Changing SQL connection information for DSN-less Access frontend

Question

I've got a mission-critical Access 2003 database that changed from a local MDB, to an MDB frontend with the backend on MS SQL Server 2005, using the Microsoft SQL Server Database Migration Assistant (SSMA) software.

Now, I need to permanently change the server that the tables are linked to from an IP address (which is changing soon) to a hostname pointing to the same server. The server itself is not changing, just the connection string.

It's a DSN-less connection, so the ODBC info is contained within the Access MDB file. If I try to refresh the table links within Access, it prompts me for a DSN (which I don't want to use).

I've done some Googling, and I've found several scraps of code to have it update itself each time the program launches. But, I'm worried that that could potentially introduce problems or delays for the users. Is that my best option, or is there some trick to permanently change the connection string stored within the MDB?

Answer 1

You can use VBA to alter the .Connect properties for your linked TableDef s.

See this sample from the Immediate window. (I used Replace() simply to split up that long line.)

? Replace(CurrentDb.TableDefs("remote_table").Connect, ";", ";" & vbCrLf)
ODBC;
DRIVER=SQL Server Native Client 10.0;
SERVER=HP64\SQLEXPRESS;
Trusted_Connection=Yes;
APP=Microsoft Office 2003;
WSID=WIN732B;
DATABASE=testbed;

So I could build a new string with a different SERVER, and assign the new string to the TableDef .Connect property.

If this is intended to be a permanent change you should only need to do it one time, not every time you open the database.

When I've done similar connection changes, it has been between different servers. So I deleted the TableDef and re-created it anew, to make sure Access didn't keep any cached meta information about that connection which would now be out of date. However, in your case, you're dealing with the same physical server, just referencing it by name instead of IP. I doubt the cached information would be a concern for you.