access queries in excel vba code example

Example 1: excel vba query access database

'VBA function to query an Access database. Returns and ADO recordset:

Function QuerySQL(sql$, dbFile$)
    Dim cnx
    Set cnx = CreateObject("ADODB.connection")
    cnx.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbFile
    Set QuerySQL = CreateObject("ADODB.Recordset")
    With QuerySQL
        .CursorLocation = 3 'adUseClient
        .CursorType = 1     'adOpenKeyset
        .Open sql, cnx
    End With
End Function
    
'---------------------------------------------------------------------
    
MsgBox QuerySQL("SELECT * from Titanic", "C:\Ships.accdb").RecordCount

Example 2: vba query access database

'VBA function to query an Access database. Returns and ADO recordset:

Function QuerySQL(sql$, dbFile$)
    Dim cnx
    Set cnx = CreateObject("ADODB.connection")
    cnx.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbFile
    Set QuerySQL = CreateObject("ADODB.Recordset")
    With QuerySQL
        .CursorLocation = 3 'adUseClient
        .CursorType = 1     'adOpenKeyset
        .Open sql, cnx
    End With
End Function
    
'---------------------------------------------------------------------
    
MsgBox QuerySQL("SELECT * from Titanic", "C:\Ships.accdb").RecordCount