Connect to SQL Server Table in VBA

[kwvh]

Is it possible to connect to tables in SQL Server from a Microsoft access 2010 front end using VBA only?

Code: Select all

Dim db As DAO.Database
Dim rst As DAO.Recordset

Dim strSQL As String
Dim strServerName, strUserName, strUserPwd, strDatabase As String
Dim strConnectString As String

   On Error GoTo Form_Load_Error


Set db = CurrentDb
strSQL = "SELECT sServer, sDatabase, sUserID, sUserPwd " & _
        "FROM tlkpInfo;"

Set rst = db.OpenRecordset("tlkpInfo", dbOpenSnapshot)
    If rst.EOF = True Then
        MsgBox ""
    Else
        With rst
            strServerName = !sServer
            strUserName = !sUserID
            strUserPwd = !sUserPwd
            strDatabase = !sDatabase
            strConnectString = "Provider=SQLOLEDB; Password= " & strUserPwd & ";Persist Security Info=True;User ID= " & strUserName & ";Initial Catalog= " & strDatabase & ";Data Source= " & strServerName
        End With
    End If


Dim cat As New ADOX.Catalog
cat.ActiveConnection = strConnectString

Using the above code, I am connecting to a Microsoft SQL Server database, and it seems to work. Now I need to connect to a specific table in the database and add a record to it. Is it possible to connect to a specific table via VBA and not have the table as a linked table in the front end?

Thanks for your ideas and help.

[HansV]

You use an ADOX Catalog if you want to inspect or modify the design of a table. If you want to view, edit, add or delete records, you don't need an ADOX Catalog but an ADODB Connection and an ADODB Recordset. Code would look like this:

Code: Select all

    Dim cnn As New ADODB.Connection
    Dim rst2 As New ADODB.Recordset
    cnn.Open ConnectionString:=strConnectString
    rst2.Open Source:="NameOfTheTable", ActiveConnection:=cnn, _
          CursorType:=adOpenKeyset, LockType:=adLockOptimistic, _
          Options:=adCmdTableDirect
    rst2.AddNew
        ...
        ...
    rst2.Update
    rst2.Close
    Set rst2 = Nothing
    cnn.Close
    Set cnn = Nothing

[kwvh]

Hans,

THANKS! worked very well. Using the sample you provided I was also able to open a connection and set up to pass variables to a passthrough query for submitting to a stored procedure. However, in calling the stored procedure from a passthrough query I am unable to pass the connection string to the passthrough query.

Code: Select all

    Dim cnn As New ADODB.Connection
    Dim rst2 As New ADODB.Recordset
    cnn.Open ConnectionString:=strConnectString
    rst2.Open Source:="MyTable", ActiveConnection:=cnn, _
          CursorType:=adOpenKeyset, LockType:=adLockOptimistic, _
          Options:=adCmdTableDirect

If logAdd = True Then

' Initiate i at 1 to skip the primary key maintained by SQL Server
        For i = 1 To rst2.Fields.Count - 2 ' use 2 to eliminate the GID field
            FldName = rst2.Fields(i).Name
                If i = rst2.Fields.Count - 2 Then
                    strBuildString = strBuildString & "@" & FldName & " = '" & Nz(Me.Controls.Item(FldName)) & "' "
                Else
                    strBuildString = strBuildString & "@" & FldName & " = '" & Nz(Me.Controls.Item(FldName)) & "', "
                End If
        Next i

        strSQL = "EXEC spAddRecord  " & strBuildString
        CurrentDb.QueryDefs(strMyPassThrough).SQL = strSQL
        
      
        DoCmd.OpenQuery strMyPassThrough
        
    rst2.Close
    Set rst2 = Nothing
    cnn.Close
    Set cnn = Nothing
    
End If

ClearScreen

The only method I know to use to call a strored procedure is through a passthrough query. The problem for me with that approach in this instance is that the passthrough already has the connection string embedded and I don't know how to change it. Is there a way to pass the connection string to the passthrough query, or is it possible to Execute the strSQL statement directly from VBA?

Thanks in advance for all your efforts and helping me continue to learn even at this late stage of life.

[HansV]

Sorry, I don't know anything about that - hopefully someone else can help you.

[kwvh]

hans,

Thanks. Via trial and error I was able to add "cnn.Execute strSQL" and viola'. It worked.

Code: Select all

If logAdd = True Then
    
' Initiate i at 1 to skip the primary key maintained by SQL Server
        For i = 1 To rst2.Fields.Count - 2 ' use 1 to eliminate the GID field
            FldName = rst2.Fields(i).Name
                If i = rst2.Fields.Count - 2 Then
                    strBuildString = strBuildString & "@" & FldName & " = '" & Nz(Me.Controls.Item(FldName)) & "' "
                Else
                    strBuildString = strBuildString & "@" & FldName & " = '" & Nz(Me.Controls.Item(FldName)) & "', "
                End If
        Next i
        strSQL = "EXEC spAddRecord  " & strBuildString
        CurrentDb.QueryDefs(strMyPassThrough).SQL = strSQL


        ' This did it!
        cnn.Execute strSQL
        


        rst2.Close
        Set rst2 = Nothing
        cnn.Close
        Set cnn = Nothing
    
End If

ClearScreen

[HansV]

Thanks for sharing the solution!