By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,660 Members | 1,945 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,660 IT Pros & Developers. It's quick & easy.

VBA switch from local to remote database

P: 3
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 works fine but when changed to then I receive the message

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

Expand|Select|Wrap|Line Numbers
  1. Function relinkTables(server As String)
  2. Dim db As DAO.database
  3. Dim tdf As DAO.TableDef
  4. Dim constring, strconnect, port, user, password, database As String
  6. Set db = CurrentDb
  8. port = "3306"
  9. user = "myuser"
  10. password = "mypassword"
  11. database = "mydatabase"
  13. strconnect = "ODBC;DRIVER={MySQl ODBC 5.1 Driver};" & _
  14.                           "SERVER=" & server & ";" & _
  15.                           "PORT=" & port & ";" & _
  16.                           "DATABASE=" & database & ";" & _
  17.                           "USER=" & user & ";" & _
  18.                           "PASSWORD=" & password & ";" & _
  19.                           "OPTION=3;"
  21.     For Each tdf In CurrentDb.TableDefs
  22.         ' check if table is a linked table
  23.         If Len(tdf.connect) > 0 Then
  24.                tdf.connect = strconnect
  25.              tdf.RefreshLink
  26.         End If
  27.     Next
  29. End Function
Apr 24 '12 #1
Share this Question
Share on Google+
5 Replies

Expert Mod 15k+
P: 31,487
If you set the link up manually and then look at the connection string (.Connect), what does it say in the "SERVER=???;" part?
Apr 25 '12 #2

Expert Mod 100+
P: 2,321
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.

Expand|Select|Wrap|Line Numbers
  1. 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.
Apr 25 '12 #3

P: 3
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.
Apr 25 '12 #4

P: 3
Thanks for the info much appreciated
Apr 25 '12 #5

Expert Mod 15k+
P: 31,487
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.
Apr 25 '12 #6

Post your reply

Sign in to post your reply or Sign up for a free account.