Method to query access database from excel Visual Basic for Applications VBA

Brad_BK 1 Reputation point
2022-02-02T20:42:47.52+00:00

Thanks for any help.

I have been using the below method to query my Access database from Excel VBA for over a decade. Today it started throwing an unspecified error. I tried all the usual suspects (reboot, reinstall, switch PC, access files from local machine instead of network, checking reference libraries) but I can't seem to find an issue. I'm running Windows 10 and Office 2016.

Is there another method to do the same thing? Thanks very much.

It's breaking on the line: con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & AccessFile

Public Function getRSOLD(sSQL As String) As Variant
Dim con As ADODB.Connection, rs As ADODB.Recordset
Dim AccessFile As String
Dim Rw As Long, Col As Long, c As Long
Dim MyField, Location As Range

'specify path to db
AccessFile = dbPath 'string variable with file location

On Error Resume Next
'Create the ADODB connection object.
Set con = CreateObject("ADODB.connection")
'Check if the object was created.
If Err.Number <> 0 Then
    MsgBox "Connection was not created!", vbCritical, "Connection Error"
    Exit Function
End If
On Error GoTo 0

'Open the connection.
con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & AccessFile

On Error Resume Next
'Create the ADODB recordset object.
Set rs = CreateObject("ADODB.Recordset")
'Check if the object was created.
If Err.Number <> 0 Then
    'Error! Release the objects and exit.
    Set rs = Nothing
    Set con = Nothing
    'Display an error message to the user.
    MsgBox "Recordset was not created!", vbCritical, "Recordset Error"
    Exit Function
End If
On Error GoTo 0

'Set thee cursor location.
rs.CursorLocation = 3 'adUseClient on early  binding
rs.CursorType = 1 'adOpenKeyset on early  binding

'On Error Resume Next
'Open the recordset.
rs.Open sSQL, con

Set getRSOLD = rs

Set Location = Nothing
Set con = Nothing
Set rs = Nothing

End Function

Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,459 questions
Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
817 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Doug Robbins - MVP 716 Reputation points
    2022-02-03T03:02:08.83+00:00

    This is snippet of code used in my Merge Tools Add-in to load an array (MergeData) with the records from an Access Database (dsName). 

            Set appAccess = CreateObject("Access.Application")
        Call appAccess.OpenCurrentDatabase(dsName)
        Set rs = appAccess.CurrentProject.Connection.Execute(sSQL)
        With rs
            .Movelast
            .moveFirst
            MergeData = .GetRows(.RecordCount)
        End With
        appAccess.Quit
        Set appAccess = Nothing
        Set rs = Nothing