change ODBC linked table to an access file link

[NightZEN]

I want to programmatically change all of my table links from their existing links to varius SQL Server Databases, to a local Access Database. What is the best way to do this? I can't seem to change the tabledef.Connect value using this type of logic:

With tdfLocal
   .Connect = ";Database=" & strDBPath
   .RefreshLink
End With

I get the ODBC dialog box to "Select a Data Source". Is this because you cannot change an ODBC linked table to an "access linked table"?

Might it be easier to delete each table, then recreate it using a new link? I'm a bit lost here (obviously), and could use some help.

Thanks!

 

[PHV]

Change manually one link and then examine the value of the TableDef.Connect property in the debug window (Ctrl+G)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[NightZEN]

PHV - thanks for the reply,

That's part of my problem... When I manually attempt to change the link I am thwarted: I open the "Linked Table manager", select a table, select the "Always prompt for a new location" box, and click "OK", and then the "Select Data Source" dialog box comes up prompting me for a new ODBC datasource (the same thing that happens when I run the above code at the .RefreshLink command). I would rather not create a new dsn for this. I just want to change this link - but it appears links to other access databases, and ODBC links differ somehow, though I am not understanding how.

Any thoughts??

Thanks

 

[PHV]

Have a look here:

http://www.connectionstrings.com/

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[NightZEN]

That is indeed a sweet site.

I am still, however, stumped. Can you change a table's link from SQL Server (via ODBC), to another access database's table?

thanks