Reconnecting ODBC linked tables in MS Access via VBA (1 Viewer)

dodulo

Sr. Developer / Architect
Local time
Today, 13:08
Joined
Feb 7, 2008
Messages
5
Hello,

I've spent hours trying to find a solution without any luck!

*** Situation ***
I have a number of ODBC linked tables in an MS Access 2003 application. I want to be able to switch between source databases; i.e. between production and development databases, easily.

*** What works ***
Open Linked Table Manager;
Select the numerous linked tables;
Select Always Prompt For a New Location;
Click OK;
Provide the new DSN....

This works fine, but is rather manual, and does not provide for hands-free deployment. I'd like to be able to do this from code. All I need to do is change the Connect property on the tabledef, how ever, this property is read-only on existing tables! **** Microsoft!

The only method that turned up through hours of research is to drop the old table, then to connect the new table. This of course would be a nightmare, because then I would have to rebuild all of the security settings on the table, all the captions on each field, and all of the combo box lookups on some of the fields! Not gonna happen!

Anyone who can solve this riddle is a true guru!

Thank you,

Daniel Odulo
http://www.Odulo.com/Consulting/
 

jdraw

Super Moderator
Staff member
Local time
Today, 16:08
Joined
Jan 23, 2006
Messages
15,364
Hello,

I've spent hours trying to find a solution without any luck!

*** Situation ***
I have a number of ODBC linked tables in an MS Access 2003 application. I want to be able to switch between source databases; i.e. between production and development databases, easily.

*** What works ***
Open Linked Table Manager;
Select the numerous linked tables;
Select Always Prompt For a New Location;
Click OK;
Provide the new DSN....

This works fine, but is rather manual, and does not provide for hands-free deployment. I'd like to be able to do this from code. All I need to do is change the Connect property on the tabledef, how ever, this property is read-only on existing tables! **** Microsoft!

The only method that turned up through hours of research is to drop the old table, then to connect the new table. This of course would be a nightmare, because then I would have to rebuild all of the security settings on the table, all the captions on each field, and all of the combo box lookups on some of the fields! Not gonna happen!

Anyone who can solve this riddle is a true guru!

Thank you,

Daniel Odulo
http://www.Odulo.com/Consulting/

I have attached a procedure that will relink tables. It has some descriptive text showing limitations and usage.
 

Attachments

  • RelinkProc.txt
    2.1 KB · Views: 10,536

dodulo

Sr. Developer / Architect
Local time
Today, 13:08
Joined
Feb 7, 2008
Messages
5
I have attached a procedure that will relink tables. It has some descriptive text showing limitations and usage.

I wish there was a way to delete junk replies. Which part of "unusable solution" did you not understand? Please read the original post. Thank you.
 

WayneRyan

AWF VIP
Local time
Today, 20:08
Joined
Nov 19, 2002
Messages
7,122
Daniel,

At great risk of getting flamed like jdraw, I'd suggest that you:

1) Loop through the Tabledefs collection
2) If Len(tdf.Connect) > 0 Then reassign tdf.Connect and do tdf.RefreshLink with your
alternate DB.

Use the Search Facility here and look for Refreshlink, that should be enough to get you
started.

You DO NOT need to use DSNs! It can all be done with code.

Wayne
 

dodulo

Sr. Developer / Architect
Local time
Today, 13:08
Joined
Feb 7, 2008
Messages
5
Thank you Wayne for your suggestion, how ever, I have attempted this before with no success. Documentation does say that the Connect property is read-only on an existing tabledef, and, in my testing, the RefreshLink method had no effect.

Daniel

Daniel,
1) Loop through the Tabledefs collection
2) If Len(tdf.Connect) > 0 Then reassign tdf.Connect and do tdf.RefreshLink with your alternate DB.
Wayne
 

WayneRyan

AWF VIP
Local time
Today, 20:08
Joined
Nov 19, 2002
Messages
7,122
Daniel,

That's interesting.

I do this on an almost daily basis refreshing the links to my SQL server
tables; pointing to different databases/tables.

I have refreshed links to Access databases, just not recently or as often.

It's just a couple of lines of code. I don't have any samples here, but
do have them in my Lab. However, no Internet in the Lab.

It really does work though (without the DSNs) and all within VBA.

I hope you're not trying to write directly to Access's system tables are you?

Wayne
 

dodulo

Sr. Developer / Architect
Local time
Today, 13:08
Joined
Feb 7, 2008
Messages
5
Amazing! F*** Microsoft!
I have solved the riddle. Here are three procedures, and how the first two don't work!

' Demonstration of how to, (and how not to,) change the ODBC connection string for a linked table in MS Access.
' By Daniel Odulo, http://www.Odulo.com/Consulting/
' 7 Feb. 2008.
Private Sub cmd_Connect1_Click()
Dim tdf As TableDef
Set tdf = CurrentDb.TableDefs("dbo_Categories")
Debug.Print tdf.Connect ' <- Run-time error '3420': Object invalid or no longer set.
End Sub

Private Sub cmd_Connect2_Click()
Debug.Print CurrentDb.TableDefs("dbo_Categories").Connect
CurrentDb.TableDefs("dbo_Categories").Connect = "ODBC;DRIVER=SQL Server;SERVER=(local);DATABASE=Northwind;Trusted_Connection=Yes"
CurrentDb.TableDefs("dbo_Categories").RefreshLink
Debug.Print CurrentDb.TableDefs("dbo_Categories").Connect
' This procedure runs without errors, but the Connect and/or RefreshLink methods have no effect.
' Both Debug.Prints show the old connection string!
End Sub

Private Sub cmd_Connect3_Click()
Dim myDB As Database, tdf As TableDef
Set myDB = CurrentDb
Set tdf = myDB.TableDefs("dbo_Categories") ' This is the way to fix the issue of the 1st procedure. Miracle!
Debug.Print tdf.Connect
tdf.Connect = "ODBC;DRIVER=SQL Server;SERVER=(local);DATABASE=Northwind;Trusted_Connection=Yes"
tdf.RefreshLink
Debug.Print tdf.Connect
' This one works like a charm!
End Sub

Thank you all for your help,

Daniel Odulo
 

WayneRyan

AWF VIP
Local time
Today, 20:08
Joined
Nov 19, 2002
Messages
7,122
Daniel,

Glad you got it working.

If it gives you any trouble later, you might try:

Dim tdf as DAO.TableDef

Wayne
 

Chromium

Registered User.
Local time
Today, 21:08
Joined
Feb 4, 2008
Messages
25
Daniel,

Glad you got it working.

If it gives you any trouble later, you might try:

Dim tdf as DAO.TableDef

Wayne

To me does not work, It says:

Private Sub cmd_Connect3_Click()
Dim myDB As Database
Dim tdf As TableDef
Set myDB = CurrentDb
Set tdf = myDB.TableDefs("dbo_Categories")
Debug.Print tdf.Connect
tdf.Connect = "ODBC;DRIVER=MySQL ODBC 3.51 Driver;UID=*********;PASSWORD=******;SERVER=217.**.***.41;PORT=3306;OPTION=0;DATABASE=*******"
tdf.RefreshLink
Debug.Print tdf.Connect
End Sub

(in bold the error line)

Run-time error 3265 "Item cannot be found in the collection corresponding to the requested name or ordinal"

What's my error?

(N.B. dsn modified for my ODBC driver)
 

Banana

split with a cherry atop.
Local time
Today, 13:08
Joined
Sep 1, 2005
Messages
6,318
This of course would be a nightmare, because then I would have to rebuild all of the security settings on the table, all the captions on each field, and all of the combo box lookups on some of the fields! Not gonna happen!

Erm, I don't put any security settings in my TableDefs collection. I can just leave the username, password, and option blank because Access doesn't actually use the Connect property if it notices it's linked to same database. Rather, when it connects to the ODBC database for first time, it caches the connection and reuses that connection whenever it needs. This 'cached connection' is not accessible as far as I can tell (Just close the database window after connecting to the ODBC database, but not the Access window, then reopen the database. You will see that there is no re-prompting for connecting to ODBC source.)

Of course, I imagine that if the ODBC source had different permissions for different tables, this will force the Access to use the Connect property and may or may not prompt for credentials, depending on how you've set it up.

Finally, a suggested reading material: Evils of lookup fields.
 

Rx_

Nothing In Moderation
Local time
Today, 14:08
Joined
Oct 22, 2009
Messages
2,803
And now for something completly different - John Clease

Updating my secured Access 2003 application to link from SQL Server 2005 to SQL server 2008.
B.T.W. Be sure to update to the new ODBC that shows Native Client 10.

Would not mind manually linking my 240 linked tables to the 4 different servers manually. But, once I choose to link over 5 tables (Link Table Manager, Always Prompt for New Location (checked), it pops up the ODBC tabbed dialogue and makes me choose the data source File Data Source Tab one at a painful time.
If I could click the File Data Source Tab once and choose the source, it would be so much better.
Does anyone know why this works for about 5 table link selections at a time, but not for 40?

Does anyone know what causes this to force the one at a time?

Oh, and the connection string is different for Native Client 10 than for the code above.
I will post it if I figure it out, however donations are acceptable.
 
Last edited:

dodulo

Sr. Developer / Architect
Local time
Today, 13:08
Joined
Feb 7, 2008
Messages
5
Erm, I don't put any security settings in my TableDefs collection. I can just leave the username, password, and option blank because Access doesn't actually use the Connect property if it notices it's linked to same database. Rather, when it connects to the ODBC database for first time, it caches the connection and reuses that connection whenever it needs. This 'cached connection' is not accessible as far as I can tell (Just close the database window after connecting to the ODBC database, but not the Access window, then reopen the database. You will see that there is no re-prompting for connecting to ODBC source.)

The security settings are not for connecting to the DB Server, they are the MS Access local security settings. They are to prevent an unauthorised user who was able to ope the application from being able to access tables, modify data, delete data, etc. When access connects to the DB, the permissions are rather general, affecting all tables. This allows you to further control what the APPLICATION users are able to do, (if they gain access to the linked tables.)
 

Rx_

Nothing In Moderation
Local time
Today, 14:08
Joined
Oct 22, 2009
Messages
2,803
Public Sub LinkToODBC4SQL2008() ' SQL Server 8
'Created an ODBC DSN using SQL Native Client 10 named MyData8
' Install SQL Native Client 10 on each workstation
' Note: my database has 250 tables connected to many different databases. other people may want to loop with a for each
' My script will connect each table one at at time by name
' example of one of the names

Dim myDB As Database, tdf As TableDef
Set myDB = CurrentDb
Set tdf = myDB.TableDefs("tvFuelCharges") ' required table name
Debug.Print tdf.Connect
tdf.Connect = "ODBC;DRIVER={SQL Server Native Client 10.0};DSN=MyData8;SERVER=TestSimon;DATABASE=NFMData;Trusted_C onnection=Yes;APP=Microsoft Office 2003"

'Old style string shown below commented out
'tdf.Connect = "ODBC;DRIVER=SQL Server;SERVER=(local);DATABASE=Northwind;Trusted_C onnection=Yes"
tdf.RefreshLink

Debug.Print tdf.Connect
' This one works like a charm!
End Sub

The debug window shows:
call LinktoOdbc4sql2008
ODBC;DRIVER=SQL Server;SERVER=SQLCLUSTER;UID=MyUserID;PWD=MyPassword;APP=Microsoft Office 2003;WSID=NFMC023;DATABASE=MyData;Network=DBMSSOCN;Trusted_Connection=Yes
ODBC;DRIVER={SQL Server Native Client 10.0};DSN=MyData8;SERVER=TestServer;DATABASE=MyData;Trusted_C onnection=Yes;APP=Microsoft Office 2003
 

Users who are viewing this thread

Top Bottom