Member Avatar for sjohnfernandas

hi all i have 2 systems connected in lan.i have to access the system 1 Database from system2 using mysql odbc i give localhost in the hostname then give username and password of system2 in the corressponding places now i can get the list of databases from system2 mysql databases.the problem was i tried in the odbc connector by giving the datasource name and in the place of host name i have given system 1 ip address then in the place of user and password i have given the system1 mysql username and password but i did not get the list of databases in the place of database guide me to get

  • 2 Contributors
  • 1 Reply
  • 277 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by StephNicolaou

Dani AI

Generated

— is correct that the connection needs a MySQL account, but the usual reasons a remote DSN shows no databases are server networking, MySQL user/host restrictions, or an ODBC-driver/DSN mismatch. The behavior described (localhost returns system2 DBs; system1 IP does not) strongly points to one of those.

Check the server networking first. In MySQL’s config (my.cnf / my.ini) make sure the server is listening on the LAN and not only on localhost, and that skip-networking is not enabled:

# allow network connections (example)
bind-address = 0.0.0.0
# make sure skip-networking is absent or commented
# skip-networking

Restart MySQL after changes, then verify the port is reachable from system2:

telnet 192.168.1.100 3306
# or on Windows PowerShell:
Test-NetConnection -ComputerName 192.168.1.100 -Port 3306

Confirm MySQL user host privileges. MySQL treats user accounts as 'user'@'host' — a correct username/password that is only allowed from 'localhost' will be rejected from a remote IP. Create/grant a remote-capable account (or add the correct host entry):

CREATE USER 'odbcuser'@'192.168.1.2' IDENTIFIED BY 'StrongPwd';
GRANT SELECT ON yourdb.* TO 'odbcuser'@'192.168.1.2';
FLUSH PRIVILEGES;

Using '%' (all hosts) is convenient but less secure. Note: SHOW DATABASES (and the DSN wizard) only lists databases the account has privileges on, so the list will be empty if no privileges are granted.

ODBC-specific gotchas: match the ODBC driver bitness to the application (use C:\Windows\SysWOW64\odbcad32.exe for 32-bit DSNs on 64-bit Windows), specify the remote server IP and port (not 'localhost'), and test the DSN with the driver’s Test button. Avoid remote root access; grant only the minimal rights needed. Common failures produce either “Can’t connect” (network/firewall) or “Access denied” (privileges), which pinpoints the next step.

Member Avatar for StephNicolaou

The username and password on the connection has to be the username and password of the database. Post the code you have... And if all else fails the DAO method is a lot easier.

Dim rs As DAO.Recordset
    Dim db As DAO.Database
    Set db = CurrentDb
    Dim path As String
    
    '--------------------------------------------------------
    'BACKEND DATABASE PATH
    path = "C:\path\DB.mdb"
    '-----------------------------------------------------------
    
    'Open all tables
    Set rs = CurrentDb.OpenRecordset("SELECT Name " & _
                                    "FROM MSysObjects IN '" & path & "' " & _
                                    "WHERE Type=1 AND Flags=0")

    'Retrieve data from a table
    Me.RecordSource = "SELECT * FROM [TBL] IN '" & path & "'"
    Me.Requery
Edited by StephNicolaou because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.