excel define database connection code example

Example: 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