CODEWITHSUNDEEP
sqlms-access
JKK
JKK

Reputation: 446

Access Linked Table Management

Got a simple question here to a problem that's grown over time. I have 50+ access databases each linked to a SQL 2000 server. I need to switch them over to SQL 2008, but I don't want to just switch them, I'd like to have them point to a single source where I can adjust it later if the tables move from one server to another so I only have to make the change in one single configuration file rather than open each and every database which is a headache.

I tried to create a single access database then link to that but you can only link to other local tables in access databases, not to linked tables.

Any ideas on solving this problem so I can create a configuration file or single pass through somehow?

Upvotes: 0

Views: 178

Answers (2)

david
david

Reputation: 2638

Since Access will cache a certain amount of information (the details depend on which version you use), you may find that you have to vist each and every database to update the link information after you modify the DSN.

Upvotes: 0

Fionnuala
Fionnuala

Reputation: 91306

You can update links via code (notes http://wiki.lessthandot.com/index.php/Link_All_SQL_Server_Tables) and you can also use DSN or FileDSN, which would contain:

[ODBC]
DRIVER=SQL Server
UID=User
AutoTranslate=No
DATABASE=Test
WSID=ComputerName
APP=Microsoft Office 2010
Trusted_Connection=Yes
SERVER=ComputerName\Instance
Description=test2

FileDSN works well with pass-through queries as well, so you can just edit the contents. However, you would need to copy to a central location.

Upvotes: 1

Related Questions