VBA switch from local to remote database

[mikeimp]

I am using a local 2010 Access Database linked to a MySQL Database using ODBC Driver 5.1.

I want to easily switch from local linked tables on my development PC to a local network database.

The server string localhost or 127.0.0.1 works fine but when changed to 192.168.xxx.xxx then I receive the message

Run-time error '3146'
ODBC --call failed

Function relinkTables(server As String)
Dim db As DAO.database
Dim tdf As DAO.TableDef
Dim constring, strconnect, port, user, password, database As String

Set db = CurrentDb

port = "3306"
user = "myuser"
password = "mypassword"
database = "mydatabase"

strconnect = "ODBC;DRIVER={MySQl ODBC 5.1 Driver};" & _
                          "SERVER=" & server & ";" & _
                          "PORT=" & port & ";" & _
                          "DATABASE=" & database & ";" & _
                          "USER=" & user & ";" & _
                          "PASSWORD=" & password & ";" & _
                          "OPTION=3;"

    For Each tdf In CurrentDb.TableDefs
        ' check if table is a linked table
        If Len(tdf.connect) > 0 Then
               tdf.connect = strconnect
             tdf.RefreshLink
        End If
    Next
 
End Function

[NeoPa]

If you set the link up manually and then look at the connection string (.Connect), what does it say in the "SERVER=???;" part?

[TheSmileyCoder]

Just a quick comment to your code, which isn't really related to the question, but I feel its worth mentioning as its a common mis-conception.

Dim constring, strconnect, port, user, password, database As String

will declare constring, strconnect, port, user, password as VARIANT
and only database as STRING.

This is a common error as it is not quite obvious that it happens, and its an area where VB.NET syntax differs from VBA.

[mikeimp]

If you set the link up manually and then look at the connection string (.Connect), what does it say in the "SERVER=???;" part?

If I set up the link using the Access ODBC tool then I have to use a DNS connection. I do not know how to set up a link any other way. The connection prior to tdf.refresh is SERVER=192.168. XXX.XXX; I am nervous about giving out the local IP address of the webserver so have replaced the numbers with X.

[mikeimp]

Just a quick comment to your code, which isn't really related to the question, but I feel its worth mentioning as its a common mis-conception.

Dim constring, strconnect, port, user, password, database As String

will declare constring, strconnect, port, user, password as VARIANT
and only database as STRING.

This is a common error as it is not quite obvious that it happens, and its an area where VB.NET syntax differs from VBA.

Thanks for the info much appreciated

[NeoPa]

In that case I see nothing wrong with your code. It appears to be handling the situation exactly as you'd want it to. I'm afraid that also means I have no idea why it wouldn't be working.

BTW. 192.168.X.X is a Class B public address range. That means it is not routable on the internet (IE. It is perfectly safe to share publicly as no-one outside of your internal network will be able to connect to it using that address). That said, using XXX is also perfectly ok as far as the question goes. Your meaning is perfectly clear.