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