Kilka miesięcy temu wykonałem testy na komputerze lokalnym połączenia Excela z bazą danych MySQL. Nie przeprowadziłem testu z bazą MySQL znajdującą się na serwerze zewnętrznym – ale uważam, że założenia będą podobne?

Załóżmy, że chcesz napisać aplikację która ma być dostępna dla głównego użytkownika (który będzie aktualizował tabele) oraz podmioty zewnętrzne, które mają z tych danych korzystać. Wyobraźmy sobie, że masz sieć sieć sklepów kontrahentów, które sprzedają Twój towar w detalu. Możesz w Excelu napisać aplikację do konfiguracji zamówienia. Co ważne, przy aktualizacji np. cen nie musisz wysyłać nowego pliku Excelu, po prostu główny użytkownik aktualizuje ceny w bazie danych. Dodatkowo większość użytkowników posiada Excela, więc nie trzeba instalować dodatkowego oprogramowania.

Trzeba uważać, aby zabezpieczyć kod VBA przed dostępem osób trzecich, ponieważ connectionstring do bazy danych wraz hasłem i userem jest nieszyfrowany w konsoli.

Trzeba zmienić ustawienia użytkownika w MySQL, aby mógł się on łączyć z tabelą, czy bazą zdalnie  – czyli sytuacja, gdy baza MySQL jest na serwerze, a Excela mamy u siebie na komputerze. Najlepiej jest stworzyć nowego usera z uprawnieniami, które nas interesują.

Do przetestowania – dawno temu to stosowałem.

Źródło: http://stackoverflow.com/questions/10236000/allow-all-remote-connections-mysql

1. Tworzymy bazę do testów w MySQL np. „Uzytkownicy”
2. Dodajemy tabelę „Nazwy” z polami „ID, Imie, WIek”.
3. Dodajemy usera wykonując kod w MySQL:

Create USER 'nazwausera'@'localhost' IDENTIFIED BY 'haslo';
GRANT ALL PRIVILEGES ON *.* TO 'nazwausera'@'localhost' WITH GRANT OPTION;
Create USER 'nazwausera'@'%' IDENTIFIED BY 'haslo';
GRANT ALL PRIVILEGES ON *.* TO 'nazwausera'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Możliwości ograniczenia dostępu dla usera:
*.* oznacza wszystkie bazy danych i tabele
db_name.* oznacza wszystkie tabele w db_name
db_name*tbl_name oznacza ograniczenie tylko do jednej tabeli w jednej bazie danych

Dodajemy kod do modułu VBA:

' Jeżeli poniższy kod przydał Ci się, rozważ proszę kliknięcie w jakąś reklamę.
' Utrzymanie serwera i domeny będzie tańsze ;) Dzięki.
' http://exceldb.net/

Źródło:
' http://stackoverflow.com/questions/13558921/how-can-vba-connect-to-mysql-database-in-excel#13558978

Private Sub mysqlconnection()

Dim Password As String
Dim SQLStr As String
Dim Server_Name As String
Dim User_ID As String
Dim Database_Name As String

Set rs = CreateObject("ADODB.Recordset") 'EBGen-Daily
Server_Name = "***.***.*.***"
Database_Name = "db_name"
User_ID = "nazwa_usera"
Password = "haslo"

SQLStr = "INSERT INTO user (id,imie,wiek) VALUES (9,'Kazik',45)"

Set Cn = CreateObject("ADODB.Connection") 'NEW STATEMENT
Cn.Open "Driver={MySQL ODBC 5.3 Unicode Driver};Server=" & _
        Server_Name & ";Database=" & Database_Name & _
        ";Uid=" & User_ID & ";Pwd=" & Password & ";"

rs.Open SQLStr, Cn, adOpenStatic

Set rs = Nothing
Cn.Close
Set Cn = Nothing
End Sub