Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rating: Thread Rating: 3 votes, 5.00 average. Display Modes
Old 02-06-2008, 08:08 PM   #1
dodulo
Sr. Developer / Architect
 
Join Date: Feb 2008
Location: San Jose, CA - USA
Posts: 5
Thanks: 0
Thanked 1 Time in 1 Post
dodulo is an unknown quantity at this point
Send a message via Yahoo to dodulo Send a message via Skype™ to dodulo
Reconnecting ODBC linked tables in MS Access via VBA

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/

dodulo is offline   Reply With Quote
Old 02-06-2008, 08:52 PM   #2
jdraw
AWF VIP
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 6,886
Thanks: 18
Thanked 930 Times in 912 Posts
jdraw has a spectacular aura about jdraw has a spectacular aura about jdraw has a spectacular aura about
Quote:
Originally Posted by dodulo View Post
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.
Attached Files
File Type: txt RelinkProc.txt (2.1 KB, 7683 views)
jdraw is offline   Reply With Quote
Old 02-06-2008, 09:17 PM   #3
dodulo
Sr. Developer / Architect
 
Join Date: Feb 2008
Location: San Jose, CA - USA
Posts: 5
Thanks: 0
Thanked 1 Time in 1 Post
dodulo is an unknown quantity at this point
Send a message via Yahoo to dodulo Send a message via Skype™ to dodulo
Quote:
Originally Posted by jdraw View Post
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.

dodulo is offline   Reply With Quote
Old 02-07-2008, 12:57 PM   #4
WayneRyan
AWF VIP
 
Join Date: Nov 2002
Location: Camarillo, CA
Posts: 7,023
Thanks: 2
Thanked 38 Times in 37 Posts
WayneRyan has a spectacular aura about WayneRyan has a spectacular aura about WayneRyan has a spectacular aura about
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
WayneRyan is offline   Reply With Quote
Old 02-07-2008, 01:16 PM   #5
dodulo
Sr. Developer / Architect
 
Join Date: Feb 2008
Location: San Jose, CA - USA
Posts: 5
Thanks: 0
Thanked 1 Time in 1 Post
dodulo is an unknown quantity at this point
Send a message via Yahoo to dodulo Send a message via Skype™ to dodulo
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

Quote:
Originally Posted by WayneRyan View Post
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
dodulo is offline   Reply With Quote
Old 02-07-2008, 01:37 PM   #6
WayneRyan
AWF VIP
 
Join Date: Nov 2002
Location: Camarillo, CA
Posts: 7,023
Thanks: 2
Thanked 38 Times in 37 Posts
WayneRyan has a spectacular aura about WayneRyan has a spectacular aura about WayneRyan has a spectacular aura about
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
WayneRyan is offline   Reply With Quote
Old 02-07-2008, 03:26 PM   #7
dodulo
Sr. Developer / Architect
 
Join Date: Feb 2008
Location: San Jose, CA - USA
Posts: 5
Thanks: 0
Thanked 1 Time in 1 Post
dodulo is an unknown quantity at this point
Send a message via Yahoo to dodulo Send a message via Skype™ to dodulo
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_C onnection=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_C onnection=Yes"
tdf.RefreshLink
Debug.Print tdf.Connect
' This one works like a charm!
End Sub

Thank you all for your help,

Daniel Odulo

dodulo is offline   Reply With Quote
The Following User Says Thank You to dodulo For This Useful Post:
Poet (06-01-2012)
Old 02-07-2008, 07:19 PM   #8
WayneRyan
AWF VIP
 
Join Date: Nov 2002
Location: Camarillo, CA
Posts: 7,023
Thanks: 2
Thanked 38 Times in 37 Posts
WayneRyan has a spectacular aura about WayneRyan has a spectacular aura about WayneRyan has a spectacular aura about
Daniel,

Glad you got it working.

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

Dim tdf as DAO.TableDef

Wayne
WayneRyan is offline   Reply With Quote
Old 05-31-2008, 05:12 AM   #9
Chromium
Registered User
 
Join Date: Feb 2008
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Chromium is on a distinguished road
Quote:
Originally Posted by WayneRyan View Post
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:

Quote:
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)
Chromium is offline   Reply With Quote
Old 05-31-2008, 06:59 AM   #10
Banana
split with a cherry atop.
 
Join Date: Sep 2005
Posts: 6,319
Thanks: 0
Thanked 70 Times in 61 Posts
Banana is a glorious beacon of light Banana is a glorious beacon of light Banana is a glorious beacon of light Banana is a glorious beacon of light Banana is a glorious beacon of light Banana is a glorious beacon of light
Quote:
Originally Posted by dodulo View Post
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.
__________________
If relation-valued attributes and arbitrarily complex types are wrong, then I don't wanna to be right!
Founder of 'Blame the Developers First' crowd.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Banana is offline   Reply With Quote
Old 10-27-2009, 12:44 PM   #11
Rx_
Newly Registered User
 
Rx_'s Avatar
 
Join Date: Oct 2009
Location: Denver, Colorado
Posts: 1,872
Thanks: 312
Thanked 186 Times in 178 Posts
Rx_ will become famous soon enough Rx_ will become famous soon enough
Re: Reconnecting ODBC linked tables in MS Access via VBA

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 by Rx_; 10-27-2009 at 12:52 PM. Reason: name of tab
Rx_ is offline   Reply With Quote
Old 10-27-2009, 01:13 PM   #12
dodulo
Sr. Developer / Architect
 
Join Date: Feb 2008
Location: San Jose, CA - USA
Posts: 5
Thanks: 0
Thanked 1 Time in 1 Post
dodulo is an unknown quantity at this point
Send a message via Yahoo to dodulo Send a message via Skype™ to dodulo
Re: Reconnecting ODBC linked tables in MS Access via VBA

Quote:
Originally Posted by Banana View Post
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.)
dodulo is offline   Reply With Quote
Old 10-27-2009, 01:16 PM   #13
Scooterbug
Registered User
 
Join Date: Mar 2009
Posts: 853
Thanks: 0
Thanked 1 Time in 1 Post
Scooterbug will become famous soon enough Scooterbug will become famous soon enough
Re: Reconnecting ODBC linked tables in MS Access via VBA

Quote:
Originally Posted by Rx_ View Post
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.
Connection Strings
Scooterbug is offline   Reply With Quote
Old 10-27-2009, 02:59 PM   #14
Rx_
Newly Registered User
 
Rx_'s Avatar
 
Join Date: Oct 2009
Location: Denver, Colorado
Posts: 1,872
Thanks: 312
Thanked 186 Times in 178 Posts
Rx_ will become famous soon enough Rx_ will become famous soon enough
Talking Re: Reconnecting ODBC linked tables in MS Access via VBA

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=NFMDat a;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=MyPasswo rd;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=MyDat a;Trusted_C onnection=Yes;APP=Microsoft Office 2003

Rx_ is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Linked ODBC tables Barguast General 2 11-20-2007 02:08 AM
Must I have ODBC table Linked for VBA code to work? Air_Cooled_Nut Modules & VBA 6 07-10-2007 01:14 PM
Linked SQL Server Tables in Access 2003 PScottH Tables 1 04-16-2007 08:40 AM
Access tables in VBA in a report sjassal Reports 2 07-13-2005 12:42 PM
Access linked tables davidi General 6 06-16-2003 02:22 PM




All times are GMT -8. The time now is 10:02 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
(c) copyright 2010 Access World