excel accessing sql database
'If the database is MS Access, the most performant way to run SQL
'queries from Excel VBA is by using DAO instead of ADO:
Function QuerySQL_DAO(sql$, dbFile$)
Const DAO = "DAO.DBEngine.120"
Set QuerySQL_DAO = CreateObject(DAO).OpenDatabase(dbFile).OpenRecordset(sql)
QuerySQL_DAO.MoveLast
QuerySQL_DAO.MoveFirst
End Function
'---------------------------------------------------------------------
MsgBox QuerySQL_DAO("SELECT * from Titanic", "C:\Ships.accdb").RecordCount
'For all other databases, use ADO:
Function QuerySQL_ADO(sql$, dbFile$)
Dim cnx
Set cnx = CreateObject("ADODB.Connection")
cnx.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbFile
Set QuerySQL_ADO = CreateObject("ADODB.Recordset")
With QuerySQL_ADO
.CursorLocation = 3 'adUseClient
.CursorType = 1 'adOpenKeyset
.Open sql, cnx
End With
End Function
'---------------------------------------------------------------------
MsgBox QuerySQL("SELECT * from Titanic", "C:\Ships.accdb").RecordCount