Geek asks and answers

Changing SQL Connection Information for the DSN-less External Access Interface

I have a critical Access 2003 database that has been changed from a local MDB to an MDB interface using a backend on MS SQL Server 2005 using Microsoft SQL Server Database Migration Assistant (SSMA) software.

Now I need to constantly change the server so that the tables are bound to with an IP address (which is changing soon) to a host name that points to the same server, the server itself does not change, just a connection string.

This is a DSN-less connection, therefore the ODBC information is contained in the Access MDB file. If I try to update the links in the table in Access, it will offer me a DSN (which I do not want to use).

I did some Googling, and I found a few snippets of code so that it is updated every time the program starts. But I am concerned that this may create problems or delays for users. Is this my best option, or is there some kind of trick for constantly changing the connection string stored in MDB?

+3
source share
2 answers

The following code has served me well for years:

Function LinkTable(DbName As String, SrcTblName As String, _
                   Optional TblName As String = "", _
                   Optional ServerName As String = DEFAULT_SERVER_NAME, _
                   Optional DbFormat As String = "ODBC") As Boolean
Dim db As dao.Database
Dim TName As String, td As TableDef

    On Error GoTo Err_LinkTable

    If Len(TblName) = 0 Then
        TName = SrcTblName
    Else
        TName = TblName
    End If

    'Do not overwrite local tables.'
    If DCount("*", "msysObjects", "Type=1 AND Name=" & Qt(TName)) > 0 Then
        MsgBox "There is already a local table named " & TName
        Exit Function
    End If

    Set db = CurrentDb
    'Drop any linked tables with this name'
    If DCount("*", "msysObjects", "Type In (4,6,8) AND Name=" & Qt(TName)) > 0 Then
        db.TableDefs.Delete TName
    End If

    With db
        Set td = .CreateTableDef(TName)
        td.Connect = BuildConnectString(DbFormat, ServerName, DbName)
        td.SourceTableName = SrcTblName
        .TableDefs.Append td
        .TableDefs.Refresh
        LinkTable = True
    End With

Exit_LinkTable:
    Exit Function
Err_LinkTable:
    'Replace following line with call to error logging function'
    MsgBox Err.Description
    Resume Exit_LinkTable
End Function



Private Function BuildConnectString(DbFormat As String, _
                                    ServerName As String, _
                                    DbName As String, _
                                    Optional SQLServerLogin As String = "", _
                                    Optional SQLServerPassword As String = "") As String
    Select Case DbFormat
    Case "NativeClient10"
        BuildConnectString = "ODBC;" & _
                             "Driver={SQL Server Native Client 10.0};" & _
                             "Server=" & ServerName & ";" & _
                             "Database=" & DbName & ";"
        If Len(SQLServerLogin) > 0 Then
            BuildConnectString = BuildConnectString & _
                                 "Uid=" & SQLServerLogin & ";" & _
                                 "Pwd=" & SQLServerPassword & ";"
        Else
            BuildConnectString = BuildConnectString & _
                                 "Trusted_Connection=Yes;"
        End If

    Case "ADO"
        If Len(ServerName) = 0 Then
            BuildConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                 "Data Source=" & DbName & ";"
        Else
            BuildConnectString = "Provider=sqloledb;" & _
                                 "Server=" & ServerName & ";" & _
                                 "Database=" & DbName & ";"
            If Len(SQLServerLogin) > 0 Then
                BuildConnectString = BuildConnectString & _
                                     "UserID=" & SQLServerLogin & ";" & _
                                     "Password=" & SQLServerPassword & ";"
            Else
                BuildConnectString = BuildConnectString & _
                                     "Integrated Security=SSPI;"
            End If
        End If
    Case "ODBC"
        BuildConnectString = "ODBC;" & _
                             "Driver={SQL Server};" & _
                             "Server=" & ServerName & ";" & _
                             "Database=" & DbName & ";"
        If Len(SQLServerLogin) > 0 Then
            BuildConnectString = BuildConnectString & _
                                 "Uid=" & SQLServerLogin & ";" & _
                                 "Pwd=" & SQLServerPassword & ";"
        Else
            BuildConnectString = BuildConnectString & _
                                 "Trusted_Connection=Yes;"
        End If
    Case "MDB"
        BuildConnectString = ";Database=" & DbName
    End Select
End Function


Function Qt(Text As Variant) As String
Const QtMark As String = """"
    If IsNull(Text) Or IsEmpty(Text) Then
        Qt = "Null"
    Else
        Qt = QtMark & Replace(Text, QtMark, """""") & QtMark
    End If
End Function
+1
source

You can use VBA to change properties .Connectfor your related TableDefs.

See this sample from the Immediate window. (I used Replace()just to split this long string.)

? 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;

SERVER TableDef .Connect.

, , , .

, . TableDef , , Access , . , IP. , .

+1

More articles:


All Articles