ODBC Connection to SQL Server from Access 2007 (1 Viewer)

Noodle-Head · Feb 6, 2009

Hi,

I have an Access 2007 application (which I inherhited) which uses linked tables and pass-through queries to a sql server 2005 database.

I want to create a copy of the application which will link to a test database (which resides on the same Sql Server)

So I recreated the linked tables so that they are pointing to the test database and then updated the odbc information in the pass through queries.

All this works but I'm wondering if there was a simpler solution available - as it was pretty tedious as the number of pass through queries is over 30. Worst still I was worried I had missed one of the queries and spent even more time triple checking the odbc data.

Thanks

NH.

Thomko · Feb 10, 2009

Hello Noodle-Head,

you can change OBDC links with VBA. Here some code

Code:

Sub ReplaceInConnectionStrings(sOld As String, sNew As String)
   Dim db As DAO.Database
   Dim td As DAO.TableDef
   dim qd as DAO.QueryDef

   Set db = CurrentDb
   
   For Each td In db.TableDefs
      If td.Connect <> "" Then
         td.Connect = Replace(td.Connect, sOld, sNew)
         td.RefreshLink
      End If
   Next td

   For Each qd In db.QueryDefs
      If qd.Connect <> "" Then
         qd.Connect = Replace(qd.Connect, sOld, sNew)
         qd.RefreshLink
      End If
   Next qd
End Sub

Calling this function with something like

ReplaceInConnectionStrings("DATABASE=<ProdDB>", "DATABASE=<TestDB>")

should change all linked tables and pass-through queries.

HTH
Thomas

SBCUser666 · Sep 4, 2009

Thomko,

This works great. But

1. Is there a way to specifiy the table whose link I want to change without looping through a list of tables?

2. Is there a way to supply the password permanently? I don't want to have to enter it every time I use the Access app?

Don

ODBC Connection to SQL Server from Access 2007 (1 Viewer)

Noodle-Head

New member

Thomko

Registered User.

SBCUser666

Registered User.

Similar threads

Users who are viewing this thread