Automatically linking an ODBC connection (2 Viewers)

[shabbaranks]

Hi,

Im looking to automate the process of linking my Access DB with an ODBC connection to an SQL DB with VBA (unless theres an easier way to do it?) - can anyone either point me in the right direction of some sample code - that's if this is possible at all?

Thanks

 

[shabbaranks]

Taken from a Microsoft Example:

'//Name     :   CreateDSNConnection
'//Purpose  :   Create a DSN to link tables to SQL Server
'//Parameters
'//     stServer: Name of 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
Public Function CreateDSNConnection(stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String) As Boolean
    
    On Error GoTo CreateDSNConnection_Err
    Dim stConnect As String
    
    stServer = ERPSVR
    stDatabase = MAIN
    stUsername = testuser
    stPassword = "test$123"
    
    
    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "Description=myDSN" & vbCr & "SERVER=" & stServer & vbCr & "DATABASE=" & stDatabase & vbCr & "Trusted_Connection=Yes"
    Else
        stConnect = "Description=myDSN" & vbCr & "SERVER=" & stServer & vbCr & "DATABASE=" & stDatabase & vbCr
    End If
    
    DBEngine.RegisterDatabase "myDSN", "SQL Server", True, stConnect
        
    '// Add error checking.
    CreateDSNConnection = True
    Exit Function
CreateDSNConnection_Err:
    
    CreateDSNConnection = False
    MsgBox "CreateDSNConnection encountered an unexpected error: " & Err.Description
    
End Function

I have already created the linked tables, the MS example goes onto to say

When you use the startup form, add code that is similar to the following to the [B]Form_Open[/B] event. 
Private Sub Form_Open(Cancel As Integer)    If CreateDSNConnection("(local)", "pubs", "", "") Then        '// All is okay.    Else        '// Not okay.    End IfEnd Sub

But doesn't clarify what this aspect is

If CreateDSNConnection("(local)", "pubs", "", "") Then

what are local and pubs? Apart from where you get beer

 

[vbaInet]

local is the name of the SQL Server instance.
pubs is the database.

But you don't need to create a dsn. Create a dsn-less connection on the fly using a connection string, keep the connection alive and use that for whatever purpose.

If your ultimate goal is to be able to re-link your SQL server tables, then you can use something like the following code:

    Dim db          As DAO.Database
    Dim tdf         As DAO.TableDef
    Const strTable  As String = "MyTable"
    Const STR_CONN  As String = "ODBC;" & _
                                "driver={SQL Server};" & _
                                "Server=MyServer;" & _
                                "database=MyDataBase;" & _
                                "uid=MyUserId;" & _
                                "pwd=MyPassword;"
    
    ' Delete tabledef
    Set db = CurrentDb
    With db.TableDefs
        .Delete strTable
        .Refresh
    End With
    
    ' Create new tabledef with appropriate conn strings
    Set tdf = db.CreateTableDef(strTable)
    With tdf
        .Connect = STR_CONN
        .SourceTableName = strTable
    End With
    
    ' Add newly created tabledef to the TableDefs collection
    With db.TableDefs
        .Append tdf
        .Refresh
        .Item(strTable).RefreshLink
    End With
    
    Set tdf = Nothing
    Set db = Nothing

... aircode/untested because I don't have SQL Server installed on this laptop but it should work.

For a list of connection strings, have a look here:

http://www.connectionstrings.com/sql-server/

The one used in the example is the SQL Server ODBC driver.