Połączenie tabel z zewnętrzną bazą via ODBC

Mam prawidłowo utworzone łącze w connektorze ODBC - nazwa usera "np. XXX" i odpowiednie hasło.
Test połączenia zawsze jest pozytywny (dopóki nie zablokują mojego IP).
Problem pojawia się, gdy w bazie Access'a wykonuję "połącz tabele". Owszem, tabele Accessa łączą się z odpowiednimi tabelami bazy zewnętrznej (Mysqli), pokazują właściwe dane, ALE po kilku połączeniach następuje blokada IP, bo w logowaniu się bazy/tabel do zewnętrznej bazy pojawia się zamiast usera wpisanego w connektorze ODBC "Admin@" i moje IP.
Widać to w logach śledzenia w connektorze ODBC.
Jak mogę "zmusić" Accessa, by przy uruchamianiu bazy logował się właściwą nazwą użytkownika = tą z connectora ODBC?

Access czasami ignoruje dane logowania z DSN. Próbowałeś użyć w ciągu połączenia czegoś takiego:

ODBC;DSN=nazwaDSN;UID=XXX;PWD=TwojeHaslo

To powinno wymusić odpowiednią nazwę użytkownika niezależnie od domyślnej konfiguracji Access'a

kiyo napisał(a):

Access czasami ignoruje dane logowania z DSN. Próbowałeś użyć w ciągu połączenia czegoś takiego:

Kopiuj

ODBC;DSN=nazwaDSN;UID=XXX;PWD=TwojeHaslo

To powinno wymusić odpowiednią nazwę użytkownika niezależnie od domyślnej konfiguracji Access'a

Może to głupie pytanie, ale GDZIE mam umieścić ten kod? Spróbowałem w ustawieniach ODBC (MariaDB ODBC 3.2), ale w jednym "wariancie" (miejsce wpisu tego kodu - oczywiście z właściwymi danymi usera, IP serwera i hasła) nie połączył w ogóle, a w drugim połączył, ale znów z błędem. Czyli albo nie wpisałem tam, gdzie trzeba, albo nie działa ...
Jakaś podpowiedź GDZIE to wpisać?

Tego łańcucha połączenia nie wstawisz do konfiguracji w MariaDB ODBC, tylko używasz go w Accessie, np. poprzez VBA lub przez "dołącz tabele" z ręcznym wpisaniem danych. W kodzie VBA w Accessie używasz tego łańcucha jako wartości TableDef.Connect:

Sub PolaczZTabelaMariaDB()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strConnect As String

    Set db = CurrentDb

    On Error Resume Next
    db.TableDefs.Delete "TwojaTabela"
    On Error GoTo 0

    ' Łańcuch połączenia ODBC
    strConnect = "ODBC;DSN=nazwaDSN;UID=XXX;PWD=TwojeHaslo"

    ' Tworzenie nowej tabeli połączonej
    Set tdf = db.CreateTableDef("TwojaTabela")
    tdf.SourceTableName = "nazwa_tabeli_na_mariadb"
    tdf.Connect = strConnect
    db.TableDefs.Append tdf

    MsgBox "Połączono z tabelą!"
End Sub

kiyo napisał(a):

Tego łańcucha połączenia nie wstawisz do konfiguracji w MariaDB ODBC, tylko używasz go w Accessie, np. poprzez VBA lub przez "dołącz tabele" z ręcznym wpisaniem danych. W kodzie VBA w Accessie używasz tego łańcucha jako wartości TableDef.Connect:

Kopiuj

Sub PolaczZTabelaMariaDB()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strConnect As String

    Set db = CurrentDb

    On Error Resume Next
    db.TableDefs.Delete "TwojaTabela"
    On Error GoTo 0

    ' Łańcuch połączenia ODBC
    strConnect = "ODBC;DSN=nazwaDSN;UID=XXX;PWD=TwojeHaslo"

    ' Tworzenie nowej tabeli połączonej
    Set tdf = db.CreateTableDef("TwojaTabela")
    tdf.SourceTableName = "nazwa_tabeli_na_mariadb"
    tdf.Connect = strConnect
    db.TableDefs.Append tdf

    MsgBox "Połączono z tabelą!"
End Sub

kiyo napisał(a):

Tego łańcucha połączenia nie wstawisz do konfiguracji w MariaDB ODBC, tylko używasz go w Accessie, np. poprzez VBA lub przez "dołącz tabele" z ręcznym wpisaniem danych. W kodzie VBA w Accessie używasz tego łańcucha jako wartości TableDef.Connect:

Kopiuj

Sub PolaczZTabelaMariaDB()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strConnect As String

    Set db = CurrentDb

    On Error Resume Next
    db.TableDefs.Delete "TwojaTabela"
    On Error GoTo 0

    ' Łańcuch połączenia ODBC
    strConnect = "ODBC;DSN=nazwaDSN;UID=XXX;PWD=TwojeHaslo"

    ' Tworzenie nowej tabeli połączonej
    Set tdf = db.CreateTableDef("TwojaTabela")
    tdf.SourceTableName = "nazwa_tabeli_na_mariadb"
    tdf.Connect = strConnect
    db.TableDefs.Append tdf

    MsgBox "Połączono z tabelą!"
End Sub

Dziękuję, ale ...
Skopiowałem tą procedurę, wpisałem UID="nazwa usera", hasło, nazwę serwera i ... błąd 3151. Nie można nawiązać połączenia z "nazwa serwera".
Rozszerzyłem strConnect o Database="nazwa bazy", dodałem Port=NrPortu i to samo.

strConnect = "ODBC;DSN="Nazwa_serwera";Port="NrPortu;Database="Nazwa_bazy";UID="Nazwa_usera";PWD="MojeHasło"
Błąd 3151. Nie można nawiązać połączenia
IP nie mam zablokowane (sprawdziłem), dostęp do sieci jest.
Co zrobiłem "nie tak"?
Może trzeba w tdf.SourceTableName oprócz nazwy tabeli "zewnętrznej" umieścić też nazwę "zewnętrznej bazy, w której jest ta tabela", tylko jak?
Debug Accessa pokazuje mi, że "zatrzymał się" na
db.TableDefs.Append tdf

Może trzeba w tdf.SourceTableName oprócz nazwy tabeli "zewnętrznej" umieścić też nazwę "zewnętrznej bazy, w której jest ta tabela", tylko jak?

Tutaj może być coś na rzeczy bo to bardzo częsty powód błędu 3151, może Access po prostu nie widzi tabeli jeśli w SourceTableName nie ma pełnej ścieżki w takim razie zamień:

tdf.SourceTableName = "moja_tabela"

na:

tdf.SourceTableName = "moja_baza.moja_tabela"

kiyo napisał(a):

Może trzeba w tdf.SourceTableName oprócz nazwy tabeli "zewnętrznej" umieścić też nazwę "zewnętrznej bazy, w której jest ta tabela", tylko jak?

Tutaj może być coś na rzeczy bo to bardzo częsty powód błędu 3151, może Access po prostu nie widzi tabeli jeśli w SourceTableName nie ma pełnej ścieżki w takim razie zamień:

Kopiuj

tdf.SourceTableName = "moja_tabela"

na:

Kopiuj

tdf.SourceTableName = "moja_baza.moja_tabela"

Niestety ...
tdf.SourceTableName = "moja_baza.moja_tabela"
generuje ten sam błąd = 3151 "Nie można nawiązać połączenia z "nazwa serwera" ...
Procedura nadal "wysypuje się" na:
db.TableDefs.Append tdf ...

A próbowałeś testować połączenie przez np. DBeaver'a? Jak tutaj nie pójdzie to może port/firewall go blokuje.

kiyo napisał(a):

A próbowałeś testować połączenie przez np. DBeaver'a? Jak tutaj nie pójdzie to może port/firewall go blokuje.
Wyczytałem w necie, że (jeśli dobrze zrozumiałem) DBeaver to coś analogicznego do tego, co używam aktualnie = connector ODBC MariaDb?
Piszą, że to JBDC ... Jak go zainstaluję, to będzie się pojawiać w moim "ODBC32bit"? Czy będzie dostępny tylko z wywołania w vba?
Sorki, że może trywialne pytania, ale informatykiem nie jestem i zwyczajnie sytuacja zmusiła mnie do szukania rozwiązania tych permanentnych blokad IP ...

Wyczytałem w necie, że (jeśli dobrze zrozumiałem) DBeaver to coś analogicznego do tego, co używam aktualnie = connector ODBC MariaDb?

Nie, DBeaver i connector ODBC MariaDB to dwa różne narzędzia, które służą do innych celów. DBeaver to graficzny klient baz danych, który umożliwia użytkownikowi łączenie się z bazami danych, przeglądanie danych, uruchamianie zapytań SQL, eksport/import danych, itp. Z kolei MariaDB ODBC Connector to sterownik ODBC, który ozwala aplikacjom np. Access łączyć się z bazą danych MariaDB.

Piszą, że to JBDC ... Jak go zainstaluję, to będzie się pojawiać w moim "ODBC32bit"? Czy będzie dostępny tylko z wywołania w vba?

Nie.Zainstalowanie DBeaver nie instaluje żadnego sterownika ODBC i nie powoduje pojawienia się żadnego wpisu w "ODBC32bit". DBeaver to po prostu narzędzie, którym chciałem, żebyś spróbował połączyć się do bazy.

Zainstalowałem, uruchomiłem, połączył - jak sądzę - jak trzeba. Podgląd w załączniku ...
Zmusił mnie do pobrania jakiegoś innego connectora mariaDB, niż mam w ODBC ... Pewnie to tak miało być ...
Czy "hostingodawca" wywali mi kolejne błędne logowanie - dowiem się i napiszę ...

Jeśli DBeaver działa i łączy się prawidłowo z Twoją bazą MariaDB, to oznacza, że:

1. serwer, login i hasło są poprawne
2. masz otwarty port i dostęp sieciowy
3. i że to nie jest problem po stronie serwera MariaDB

To zawęża problem do:

• konfiguracji ODBC
• sposobu, w jaki Access próbuje nawiązać połączenie (czyli connection string / DAO)

Jako następny krok zrób porównanie:

1. Sprawdź dokładnie nazwę hosta, port, user i nazwę bazy, których używa DBeaver:
   • W DBeaver kliknij prawym przyciskiem na połączeniu → Edit Connection
   • Przejdź do zakładki Connection Settings
   • Zanotuj: host, port, database, user
2. Na ich postawie zbuduj connection string w VBA:

Sub PolaczZMariaDB()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim connStr As String

    connStr = "ODBC;DRIVER={MariaDB ODBC 3.2 Driver};" & _
              "SERVER=db.serwer.pl;" & _
              "PORT=3306;" & _
              "DATABASE=moja_baza;" & _
              "UID=xxx;" & _
              "PWD=twoje_haslo;" & _
              "OPTION=3;"

    Set db = CurrentDb()

    Set tdf = db.CreateTableDef("moja_tabela_linkowana")
    tdf.SourceTableName = "nazwa_tabeli"
    tdf.Connect = connStr

    db.TableDefs.Append tdf

    MsgBox "Sukces!"

End Sub

Jeszcze jedno sprostowanie bo doczytałem tdf.SourceTableName nie może zawierać nazwy bazy, Access tego nie akceptuje

kiyo napisał(a):

Jeśli DBeaver działa i łączy się prawidłowo z Twoją bazą MariaDB, to oznacza, że:

1. serwer, login i hasło są poprawne
2. masz otwarty port i dostęp sieciowy
3. i że to nie jest problem po stronie serwera MariaDB

To zawęża problem do:

• konfiguracji ODBC
• sposobu, w jaki Access próbuje nawiązać połączenie (czyli connection string / DAO)

Jako następny krok zrób porównanie:

1. Sprawdź dokładnie nazwę hosta, port, user i nazwę bazy, których używa DBeaver:
   • W DBeaver kliknij prawym przyciskiem na połączeniu → Edit Connection
   • Przejdź do zakładki Connection Settings
   • Zanotuj: host, port, database, user
2. Na ich postawie zbuduj connection string w VBA:

Kopiuj

Sub PolaczZMariaDB()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim connStr As String

    connStr = "ODBC;DRIVER={MariaDB ODBC 3.2 Driver};" & _
              "SERVER=db.serwer.pl;" & _
              "PORT=3306;" & _
              "DATABASE=moja_baza;" & _
              "UID=xxx;" & _
              "PWD=twoje_haslo;" & _
              "OPTION=3;"

    Set db = CurrentDb()

    Set tdf = db.CreateTableDef("moja_tabela_linkowana")
    tdf.SourceTableName = "nazwa_tabeli"
    tdf.Connect = connStr

    db.TableDefs.Append tdf

    MsgBox "Sukces!"

End Sub

Jeszcze jedno sprostowanie bo doczytałem tdf.SourceTableName nie może zawierać nazwy bazy, Access tego nie akceptuje

Tak, jak napisałem - działa, łączy, pokazuje co trzeba, NIE pojawia się jako błędne logowanie "Admin@" ...
Ale pojawił się inny problem, który nie wiem jak pokonać: Po circa minucie bezczynności następuje zerwanie połączenia:
"Nieudane wywołanie ODBC
[ma-3.2.3]Connection not open(#0)"
Ponowna próba utworzenia połączenia powoduje wywalenie się Accessa (Access "się naprawia" tworząc backup) po czym znów może się połączyć i łączy. Aż do kolejnej bezczynności ...
Nie wiem, czy to wina connectora MariaDb. Zaznaczyłem w nim "client is interactive" oraz "Enable automatic recoccect", ale to nic nie zmieniło ...

Ten czas bezczynności, po którym następuje rozłączenie jest dziwnie różny. Od circa minuty do kilkunastu minut. Pojawia się:
[ma-3.2.5][10.6.21-MariaDb-log]Server has gone away (#2006)
po czym - przy próbie łączenia - access się "wywala", tworzy Backup, "naprawia się" i znów można połączyć ... Co go "wywala" nie mam pojęcia ...

Wystarczyło pogooglować. MariaDB ODBC 3.2.x to jeszcze seria eksperymentalna i niestety pełna błędów, szczególnie przy współpracy z Accessem. Z tego co piszą najbardziej znane problemy to:

1. Niestałe zrywanie połączeń (Server has gone away),
2. Crash Accessa przy reconnect,
3. Nieprawidłowe działanie opcji Enable automatic reconnect,
4. Problemy z długimi połączeniami i SELECT po bezczynności,
5. Memory leak lub uszkodzenie pamięci w Access.

Najlepszym rozwiązaniem jest odinstalowanie ODBC 3.2.x i zainstalowanie stabilnej wersji 3.1.17.

Miałem problem z połączeniem do zdalnej bazy przez ODBC, ponieważ serwer docelowy nie miał publicznego IP. Zamiast konfigurować tunel VPN, skonfigurowałem dostęp przez lokalny port forward na ruterze i ustawiłem stałą lokalną trasę. Teraz połączenie działa stabilnie przez sieć lokalną.