Changing SQL DB via ODBC Connection in MS Access

Question

Is there a global way of updating a MS Access DB that connects to SQL Database?  

I refreshed the network saved DSN file to point to the new SQL Database (on another server) but it appears that I have to update each table thru Linked Table Manager, as the refreshed DSN file changes are not automatically applying.  Very time consuming.  I want to retain the queries, forms and reports I already have.

If I update the tables, does this also refresh queries, forms, reports, etc.

Answer 1

I know exactly what you are talking about.  I have written some vba script to update all the SQL Linked Tables in a database.  See below:

This code should be used in a separate database than your other applications.

Before running the script you need to create a table:

DoCmd.RunSQL "CREATE TABLE tblPending ([Source Name] VARCHAR(255),[Linked Name] VARCHAR(255),[Database Name] VARCHAR(255))"

Place the code below into a new Module:

Option Compare Database
Option Explicit

Private db As DAO.Database
Private b As Byte
Private bolError As Boolean

Private rs As DAO.Recordset
Private tbl As DAO.TableDef

Public Sub RelinkSQLTablesTo(strAccessDatabasePath As String, Optional strInstance As String = "MyInstance", Optional strDatabase As String = "MyDatabase")

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE FROM tblPending"
DoCmd.SetWarnings True

Set rs = CurrentDb.OpenRecordset("tblPending")

b = 0
Set db = DBEngine.OpenDatabase(strAccessDatabasePath)
For Each tbl In db.TableDefs
    If tbl.Connect Like "*SQL Server*" Then
        rs.AddNew
        rs![source name] = Replace(tbl.SourceTableName, "dbo.", "")
        rs![database name] = DatabaseName(tbl.Connect)
        rs![linked name] = tbl.Name
        rs.Update
    End If
Next tbl

rs.MoveFirst
Do Until rs.EOF
    LinkSQLTable strInstance, rs![source name], strDatabase, rs![linked name], Left$(FileName(db.Name), Len(FileName(db.Name)) - 4)
    rs.MoveNext
Loop
db.Close
Set db = Nothing
MsgBox b & " tables affected", vbInformation
DoCmd.Quit acQuitPrompt
End Sub

Private Function DatabaseName(strConnection As String) As String
strConnection = Mid(strConnection, InStr(1, strConnection, "DATABASE=") + 9, Len(strConnection))
DatabaseName = Left(strConnection, InStr(1, strConnection, ";") - 1)
End Function

Private Function FileName(strFileName As String) As String
Dim lnNuvPos As Integer
Dim lnPos As Integer

lnNuvPos = 1
Do While lnNuvPos <> 0
lnPos = lnNuvPos + 1
lnNuvPos = InStr(lnPos, strFileName, "\")
Loop

FileName = Mid$(strFileName, lnPos, Len(strFileName))

End Function

Private Sub LinkSQLTable(strSource As String, strServerTable As String, strSQLDatabase As String, Optional strLinkedTableName As String, Optional strApplicationName As String)

If strLinkedTableName = "" Then
    strLinkedTableName = strServerTable
End If

On Error GoTo ErrorChecks

db.Execute "DROP TABLE [" & strLinkedTableName & "]"

bolError = False
Dim tblLinked As DAO.TableDef
Set tblLinked = db.CreateTableDef(strLinkedTableName)

tblLinked.Connect = "ODBC;DRIVER=SQL Server;" & _
                    "SERVER=" & strSource & ";" & _
                    "APP=" & strApplicationName & ";" & _
                    "TRUSTED_CONNECTION=YES;" & _
                    "PERSIST SECURITY INFO=NO;" & _
                    "DATABASE=" & strSQLDatabase

tblLinked.SourceTableName = strServerTable
tblLinked.Attributes = dbAttachSavePWD
db.TableDefs.Append tblLinked
Set tblLinked = Nothing
b = b + 1

Exit Sub
ErrorChecks:
If Err.Number = 3011 Or Err.Number = 3376 Then 'table does not exist
    If Err.Source = "MSAccess" Or Err.Source = "DAO.Database" Then
        Resume Next 'this will stop access from trying to delete a table that doesn't exist
    Else
        If bolError = True Then
            MsgBox "This table does not exist on SQL Server", vbInformation
            Exit Sub
        End If
    End If
Else
    MsgBox Err.Number & vbNewLine & Err.Description
    Exit Sub
End If

End Sub

An example to run: place into the Immediate window then press return:

RelinkSQLTablesTo "C:\MyAccessDatabase.mdb","MYSERVER\MYINSTANCE","AdventureWorks"<br/>

Hope this helps


Rob