Automatically refresh ODBC linked tables at satrtup

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have any Access db the has linked tables to an Oracle db using ODBC. If I
send the database to another user on my network, they get an ODBC failed
error. They have to refresh the links, then the Access DB works fine. Does
anyone have any code on refreshing ODBC links using VBA code in Access?
 
I had a similar problem. Here's what I use, it was from another member in
the Access ODBC Client Newsgroup:

Dim mydb As DAO.Database, myq As DAO.QueryDef
Set mydb = DBEngine.Workspaces(0).Databases(0)
Set myq = mydb.CreateQueryDef("")

connectstring = "ODBC;DSN=myDataSourceName;uid=" & Forms![main
menu]!BoxUserName & ";PWD=" & Forms![main menu]!BoxPassword &
";SERVER=myDataSourceServerName;"

sqltext = "select emp_id from Table1"
myq.returnsrecords = False
myq.Connect = connectstring
myq.sql = sqltext
myq.Execute
myq.Close
MsgBox ("linked to tables")

That's all. The weird thing is that I only have to run a query against one
table, but it forces a link to all of them. I don't know how it works, but I
like it.
 
Does the ODBC data source (DSN) exist on the new PC before the user opens the
Access database?

If not, the problem may be the absence of the DSN, and not the link itself.
I simply export the DSN from the registry of a PC that is all set up and send
it to the user as a .reg file for the user to import into the registry.

Export this registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\<DSN Name Goes Here>

Send the .reg file to the user and have him double-click to insert it into
the registry.

Kurt Monroe said:
I had a similar problem. Here's what I use, it was from another member in
the Access ODBC Client Newsgroup:

Dim mydb As DAO.Database, myq As DAO.QueryDef
Set mydb = DBEngine.Workspaces(0).Databases(0)
Set myq = mydb.CreateQueryDef("")

connectstring = "ODBC;DSN=myDataSourceName;uid=" & Forms![main
menu]!BoxUserName & ";PWD=" & Forms![main menu]!BoxPassword &
";SERVER=myDataSourceServerName;"

sqltext = "select emp_id from Table1"
myq.returnsrecords = False
myq.Connect = connectstring
myq.sql = sqltext
myq.Execute
myq.Close
MsgBox ("linked to tables")

That's all. The weird thing is that I only have to run a query against one
table, but it forces a link to all of them. I don't know how it works, but I
like it.



RonB said:
I have any Access db the has linked tables to an Oracle db using ODBC. If I
send the database to another user on my network, they get an ODBC failed
error. They have to refresh the links, then the Access DB works fine. Does
anyone have any code on refreshing ODBC links using VBA code in Access?
Click to expand...
Click to expand...
 
=?Utf-8?B?S3VydCBNb25yb2U=?= said:
I had a similar problem. Here's what I use, it was from another member in
the Access ODBC Client Newsgroup:

Dim mydb As DAO.Database, myq As DAO.QueryDef
Set mydb = DBEngine.Workspaces(0).Databases(0)
Set myq = mydb.CreateQueryDef("")

connectstring = "ODBC;DSN=myDataSourceName;uid=" & Forms![main
menu]!BoxUserName & ";PWD=" & Forms![main menu]!BoxPassword &
";SERVER=myDataSourceServerName;"

sqltext = "select emp_id from Table1"
myq.returnsrecords = False
myq.Connect = connectstring
myq.sql = sqltext
myq.Execute
myq.Close
MsgBox ("linked to tables")

That's all. The weird thing is that I only have to run a query against one
table, but it forces a link to all of them. I don't know how it works, but I
like it.
Click to expand...

8 years later and it's the simplest, most effect thing i've found to solve that problem. I say, msft and oracle, what's up with that?
 
Back
Top