Change ODBC source programmatically

[rdeleon]

I have an access application that uses an ODBC data source to link to a SQL 2k database.

I have several databases that I connect to and I am getting tired of relinking the tables when I want to use a different database.

Does anyone have code that will do this?

Thanks in advance.

Rene'

 

[nealvenables]

Feel free to try this code. It re-links your SQL tables and also refreshes the connection strings embedded in passthrough queries.

I use it to switch between two connection contexts, production vs development. Note that the context is set by remming out the appropriate code block in the declarations section.

Option Compare Database
Option Explicit

' SQL Production context
' Public Const CONTEXT As String = "Production"
' Public Const SERVER_NAME As String = "YourProductionServer"
' Public Const SERVER_ALT As String = "YourDevelopmentServer"
' Public Const DB_NAME As String = "YourProductionDB"
' Public Const DB_ALT As String = "YourDevelopmentDB"

' SQL Dev context
Public Const CONTEXT As String = "Development"
Public Const SERVER_NAME As String = "YourDevelopmentServer"
Public Const SERVER_ALT As String = "YourProductionServer"
Public Const DB_NAME As String = "YourDevelopmentDB"
Public Const DB_ALT As String = "YourProductionDB"


Public Sub SetContext()

Dim adoCNN As New ADODB.Connection
Dim adoCMD As New ADODB.Command
Dim adoRST As New ADODB.Recordset
Dim daoDBS As DAO.Database
Dim daoQDF As DAO.QueryDef

Set daoDBS = CurrentDb()
adoCNN.Open "Provider='sqloledb';" & _
"Data Source='" & SERVER_NAME & "';" & _
"Initial Catalog='" & DB_NAME & "';" & _
"Integrated Security='SSPI';"

adoCMD.ActiveConnection = adoCNN
adoCMD.CommandType = adCmdStoredProc
adoCMD.CommandText = "sp_appListUserTables"

Set adoRST = adoCMD.Execute

With adoRST
Do Until .EOF
If Mid$(!name, 1, 2) <> &quot;dt&quot; _
And Mid$(!name, 1, 3) <> &quot;sys&quot; Then
On Error Resume Next
DoCmd.DeleteObject acTable, !name
On Error GoTo 0
DoCmd.TransferDatabase acLink, &quot;ODBC Database&quot;, _
&quot;ODBC;DRIVER=SQL Server;SERVER=&quot; & SERVER_NAME & &quot;;&quot; & _
&quot;UID=;DATABASE=&quot; & DB_NAME & &quot;;Trusted_Connection=Yes&quot;, _
acTable, !name, !name, , False
' Make tables truly hidden in production context
' If CONTEXT = &quot;Production&quot; Then
' daoDBS.TableDefs(!name).Attributes = dbHiddenObject
' End If
End If
.MoveNext
Loop
End With


For Each daoQDF In daoDBS.QueryDefs
If daoQDF.Connect <> &quot;&quot; Then
daoQDF.Connect = Replace(daoQDF.Connect, SERVER_ALT, SERVER_NAME, 1)
daoQDF.Connect = Replace(daoQDF.Connect, &quot;(local)&quot;, SERVER_NAME, 1)
daoQDF.Connect = Replace(daoQDF.Connect, DB_ALT, DB_NAME, 1)
End If
Next daoQDF
daoDBS.QueryDefs.Refresh

adoRST.Close
adoCNN.Close
Set adoRST = Nothing
Set adoCNN = Nothing

End Sub

The code depends on the following stored-procedure being set up in each of your SQL databases...

CREATE PROC sp_appListUserTables AS
SELECT *
FROM sysobjects
WHERE xtype = 'U'