Making Direct ODBC Calls

Another alternative to using Data Access Objects (DAO) and Microsoft Jet is to make direct calls to the ODBC API. This provides the advantages of tight control of the resources and access to extra functionality provided by ODBC, at the disadvantage of the extra coding complexity required. There is also no way to share connections between Jet and discrete ODBC calls, so you will pay the cost of more connection overhead and the inconvenience of multiple logins. The following code examples will give you an idea of what's involved:

The following code creates a connection to an ODBC data source:

iRC = SQLAllocConnect(henv, hdbc)
If iRC <> SQL_SUCCESS Then
    MsgBox "Can't allocate ODBC connection handle.", 16
    Stop
Else
    sConnect = "DSN=" & DSN & ";UID=" & USERNAME & ";PWD=" & PASSWORD & ";DATABASE=" & DBNAME
    sConnect = sConnect & ";WSID=" & HOSTNAME & ";APP=" & APPNAME
    sConnectBuffer = Space$(256)
    iRC = SQLDriverConnect(hdbc, Me.hWnd, sConnect, Len(sConnect), sConnectBuffer, Len(sConnectBuffer), ilenConnect, SQL_DRIVER_NOPROMPT)
    If iRC <> SQL_SUCCESS And iRC <> SQL_SUCCESS_WITH_INFO Then
        DescribeError hdbc, 0    ' sub to display the error
        Stop
    Else
        If iRC = SQL_SUCCESS_WITH_INFO Then DescribeError hdbc, 0
        iRC = SQLAllocStmt(hdbc, hstmt)
        If iRC <> SQL_SUCCESS Then
            MsgBox "Cannot allocate statment handle", 16
            Stop
        End If
    End If
End If
' Add the following line back to use asynchronous calls
' Attempt SQLSetStmtOption(hstmt, SQL_ASYNC_ENABLE, 1&), "Can't set Async on"

To actually execute a query, you could use code similar to:

iRC = SQLExecDirect(hstmt, sQuery, Len(sQuery))
' Add the following lines back for asynchronous execution
'    Do While iRC = SQL_STILL_EXECUTING
'        DoEvents
'        iRC = SQLExecDirect(hstmt, sQuery, Len(sQuery))
'    Loop

If iRC <> SQL_SUCCESS Then
    DescribeError hdbc, hstmt
    Exit Sub
End If

Following successful execution of the query, you could retrieve data using:

iRC = SQLNumResultCols(hstmt, iNumCols)
If iRC <> SQL_SUCCESS Then
    DescribeError hdbc, hstmt
    Exit Sub
End If
    
Do While SQLFetch(hstmt) = SQL_SUCCESS
    sStuff = ""
    For iCount = 1 To iNumCols
        Attempt SQLGetData(hstmt, iCount, 1, sBuffer, iBufferLen, lOutLen), "Call to SQLGetData Failed"
        If lOutLen = -1 Then
            sStuff = sStuff & "NULL" & Chr$(9)
        Else
            sStuff = sStuff & Left$(sBuffer, lOutLen) & Chr$(9)
        End If
    Next iCount
    Debug.Print sStuff        ' output the data retrieved
    Loop

Attempt SQLFreeStmt(hstmt, SQL_CLOSE), "FreeStmt Failed"

Final clean up of resources for the above code might look like:

If hstmt <> 0 Then Attempt SQLFreeStmt(hstmt, 0), "Unable to free statment handle"
    If hdbc <> 0 Then Attempt SQLDisconnect(hdbc), "Unable to disconnect"
    If hdbc <> 0 Then Attempt SQLFreeConnect(hdbc), "Unable to free connection handle"

Code for the Attempt subroutine is:

Sub Attempt (ResultCode As Integer, ErrorMessage As String)
If ResultCode <> SQL_SUCCESS Then
    lstMsgs.AddItem Format$(ResultCode) & " - " & ErrorMessage
    Stop
End If
End Sub

More Information

For more information on using direct ODBC calls from Microsoft Access, Microsoft Basic (or any Microsoft application that contains Basic), see the ODBC 2.0 SDK. This is available as part of the Microsoft Developers Network (MSDN) Level 2 subscription.