Connection String

[cradaa]

I have inherited a few Access databases that talk to a SQL database in the
backend.


I need to redirect them to the new SQL server, but when I search the Access
database, I cannot find where it builds the connection string.
When I hover over the linked tables, it shows a DSN-LESS connection, but
again, I don't know where to change it.
FYI, there is a system.mdw associated with the Access Db's

Any help will be appreciated.
Thanks.

[June7]

Can use LinkedTableManager to change table links. Right click on any table (Access 2007 or 2010, don't remember if this works in 2003) to get the Manager.

Or VBA code can change table links. I do this so can set links with UNC (Universal Naming Convention) pathing.

Clarify what you mean by DSN-less, show example of link.

[NoellaG]

Hi,

could it be you're working with an .adp project? In this case you can find the connection option somewhere in the File menu (it's been a while since I used 2003, so I don't remember where exactly).
If you're working with ODBC you can use the linked table manager. If there's no DSN available on yoour system you can create a DSN file using the ODBC data manager you'll find in the administrative tools of the control panel (see attached picture).

greetings
NG

[cradaa]

When I look in the project code, there is not code for setting up the actual connection strings.
If I right click the table that is linked it shows something similar to this...
ODBC;DRIVER=SQL Server;SERVER=sqldev;DATABASE=Inventory;Trusted_Co nnection=YesBut I don't know where that is coming from. This type of connection does not use the typical Data Source ODBC connection you would setup on the the local server.

[NoellaG]

Hi,

sounds as a perfect normal ODBC connection to a SQL server. To redirect them to the new SQL server:

• open the Linked Table Manager
• select the tables that have to be relinked
• Check "Always prompt for new location"
• In the "Select Data source, choose the DSN (File Data Source or Machine Data Source) that contains the new link info. If there doesn't exist a DSN for the new SQL server, you can create one using the administrative tools of the control panel (see my previous answer)

That's all, you don't need any code to relink the tables.

succes
NG