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

View another examples Add Own solution

Phoenix Logan 186120 points

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

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

Thank you! 2

4 (2 Votes)

Awgiedawgie 440215 points

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

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

Thank you! 0

Are there any code examples left?

Find Add Code snippet

New code examples in category VBA

VBA 2022-03-27 20:55:02 excel vba check if all substrings in list are in string
VBA 2022-03-27 20:40:39 excel vba set cell value to empty
VBA 2022-03-27 17:05:52 vba simple quote
VBA 2022-03-27 14:30:19 textbox find and replace vb
VBA 2022-03-27 09:10:21 nested if else in vb.net
VBA 2022-03-27 09:10:09 excel vba string to bits
VBA 2022-03-25 02:00:15 vba verticalalignment
VBA 2022-03-24 12:20:06 Excel web scraping
VBA 2022-03-24 10:11:00 excel vba BitToLong
VBA 2022-03-21 18:00:05 excel vba save file txt

Create a Free Account

Unlock the power of data and AI by diving into Python, ChatGPT, SQL, Power BI, and beyond.

Develop soft skills on BrainApps

Complete the IQ Test

Relative searches

vba sql query in excel table from connection vba sql query in excel table excel vba import data from access database import data from excel to access database vba query sql vba access vba read access database vba excute microsoft access database vba access database.execute query access vba vba access select from db vba access get db excel vba read access database tutorial excel vba read access database excel vba read write access database vba excel read write to access database vba connect to access database vba query access database Open query access From excel with VBA open query access vba excel open query access vba excel vba use sql excel vba query a table vba access execute sql vba access database vba access query vba select query to excel access vba query vba query excel excel vba code to run access query excel vba run acces query result exceç vba access queries in excel vba excel vba querying sql database vba query database excel vba query database excel vba query access database

excel vba query access database

Welcome Back!

Create a Free Account