DSNless Connection in VBA to SQL Server Tables

dsnless-connection-in-vba-to-sql-server-tables

When we released updated access database which were linked to an sql source the deployment requires re-connection to the client environment ; We would need to ensure the client PC had the DSN setup and tested, to simplify the process we call this DSN-less function. The program tests the connection when opening and advises the user if a re-connection is required.

I used the Microsoft code and a listing of tables in the database which were SQL tables and passed the parameters to the function.

DSNless Connection Function
Function refreshConnection(tmpServer As String, tmpDatabase As String, tmpUser As String, tmpPassword As String, tmpFiles As String)

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("Select * from tblLinkedSQLSOURCE where location=" & Chr(34) & tmpFiles & Chr(34), dbOpenDynaset, dbSeeChanges)
If rst.RecordCount > 0 Then
    rst.MoveFirst
    Do While Not rst.EOF
        
        Form_frmConnect.lblStatus.Caption = Left("Connecting " & rst!Table & " at " & Now() & vbCrLf & Form_frmConnect.lblStatus.Caption & "", 1000) & ".."
        DoEvents
        Call AttachDSNLessTable(rst!Table, rst!File, tmpServer, tmpDatabase, tmpUser, tmpPassword)
        rst.MoveNext
    Loop
Else
    MsgBox "No files in that list?"
End If
Set rst = Nothing


End Function
DSNless

This is the function – which was based on this MS Solution

 '//Name : AttachDSNLessTable
'//Purpose : Create a linked table to SQL Server without using a DSN
'//Parameters
'// stLocalTableName: Name of the table that you are creating in the current database
'// stRemoteTableName: Name of the table that you are linking to on the SQL Server database
'// stServer: Name of the SQL Server that you are linking to
'// stDatabase: Name of the SQL Server database that you are linking to
'// stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'// stPassword: SQL Server user password
Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)
On Error GoTo AttachDSNLessTable_Err
Dim td As TableDef
Dim stConnect As String

For Each td In CurrentDb.TableDefs
If td.Name = stLocalTableName Then
CurrentDb.TableDefs.Delete stLocalTableName
End If
Next

If Len(stUsername) = 0 Then
    '//Use trusted authentication if stUsername is not supplied.
    stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
Else
'//WARNING: This will save the username and the password with the linked table information.
    stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
End If
Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
CurrentDb.TableDefs.Append td
AttachDSNLessTable = True
Exit Function

AttachDSNLessTable_Err:

AttachDSNLessTable = False
MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description
If MsgBox("Close Database", vbYesNo) = vbYes Then
    DoCmd.Quit
End If
End Function

Links to other access code samples

,

Leave a Reply

Your email address will not be published. Required fields are marked *