Querying Access From Excel. Expected Query Name After Execute

Question

I'm trying to query an Access database and return values based on what is in my spreadsheet.

Based on Running an Access Query from Excel, here is what I have:

Sub testdb()

Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim rs As ADODB.Recordset

Set con = New ADODB.Connection
Set cmd = New ADODB.Command

With con
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .Open "D:\Users\*****\Documents\Database2.accdb"
End With

With cmd
    .ActiveConnection = con
    .CommandText = "SELECT qx FROM Table1 WHERE ID = [MyID]; "
    .CommandType = adCmdStoredProc

    .Parameters.Append cmd.CreateParameter("MyID", adChar, adParamInput, Size:=14)
    .Parameters("MyID") = "ANBMaleNS21216"
End With

Set rs = New ADODB.Recordset
rs.Open cmd

Do Until rs.EOF
    Debug.Print rs.Fields("ID").Value
    rs.MoveNext
Loop

rs.Close
con.Close

Set cmd = Nothing
Set rs = Nothing
Set prm = Nothing
Set con = Nothing

End Sub

When it gets to the line rs.Open cmd it errors out

"Run-time error '-2147217900 (80040e14)':
Expected query name after execute.

You have set CommandText to an SQL statement. As the error states, it expects a query name - a query object that is built in Access. The referenced link example references a query object named Query1 and that query has the SQL structure shown. — June7, Mar 13 '20 at 21:36
What do you really want to do with recordset data? - certainly more than just output to the immediate window. — June7, Mar 13 '20 at 21:45
I see. That makes sense. My VBA is a bit rusty, and I haven't done much with Access before. Ideally, I'd like to have the macro look up a list of values in my spreadsheet in my Access database and return back an associated value. Kind of like a VLOOKUP, but the table array would be stored in Access. — Max Herring, Mar 13 '20 at 21:59
What do you expect to return - a single record? Do you want to pull only a single related value? — June7, Mar 13 '20 at 22:20
`.CommandType = adCmdText` See https://stackoverflow.com/questions/10352211/vba-ado-connection-and-query-parameters/10353908 for example. Also make sure you're adding the correct type of parameter. If ID is numeric don't add a character-type parameter. — Tim Williams, Mar 13 '20 at 22:33
Why would you print ID when you already have that value? ID is not even included in recordset. If you want to pull and output qx that matches given ID, then Debug.Print qx field. Code doesn't even show looping through Excel cells to pull ID values. Better figure that part out first. — June7, Mar 13 '20 at 22:38

June7 · Accepted Answer · 2020-03-13T23:23:36.223

If all you are looking for is a single related value and don't really need a full set of data consider:

Dim acc As Access.Application, varData As Variant
Set acc = CreateObject("Access.Application")
acc.OpenCurrentDatabase ("D:\Users\*****\Documents\Database2.accdb")
'code here to start worksheet loop
varData = acc.DLookup("qx", "Table1", "ID=" & need worksheet cell reference here)
Debug.Print IIf(IsNull(varData), "Not found", varData)
'end worksheet loop here

Or maybe better would be to hit the Access file only once to pull all data into a recordset then use recordset Find method.

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Users\*****\Documents\Database2.accdb"
rs.Open "SELECT qx FROM Table1", cn, adOpenDynamic, adLockPessimistic
'code here to start worksheet loop
rs.MoveFirst
rs.Find "ID=" & need worksheet cell reference here
Debug.Print IIf(rs.EOF, "Not found", rs!qx)
'end worksheet loop here

Second approach may actually be faster procedure.

score 0 · Answer 2 · answered Mar 14 '20 at 19:48

There are so many ways to use Excel to control MS Access, and vice versa. Here is one simple way to pull data into Excel, from Access (code runs in Excel).

Sub Import()
' Declare the QueryTable object
Dim qt As QueryTable
' Set up the SQL Statement
sqlstring = "Select LastName, FirstName from Employees Where FirstName In " & Range("A1:A10").Value & ""
' Set up the connection string, reference an ODBC connection
connstring = _
"ODBC;DSN=Northwind;UID=;PWD=;Database=Northwind"
' Now implement the connection, run the Query, and add
' the results to the spreadsheet starting at row A1
With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)
.Refresh
End With
End Sub

Note: set a reference to 'Microsoft ActiveX Data Objects 2.8 Library'

Here is another, similar but different, way to import data into Excel, from Access.

Sub Select_From_Access()
    Dim cn As Object, rs As Object
    Dim intColIndex As Integer
    Dim DBFullName As String
    Dim TargetRange As Range

    DBFullName = "C:\your_path\Northwind.mdb"

    'On Error GoTo Whoa

    Application.ScreenUpdating = False

    Set TargetRange = Sheets("Select").Range("A1")

    Set cn = CreateObject("ADODB.Connection")
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBFullName & ";"

    Set rs = CreateObject("ADODB.Recordset")
    rs.Open "SELECT * FROM [OrderDetails] WHERE [OrderID] = 10248", cn, , , adCmdText

    ' Write the field names
    For intColIndex = 0 To rs.Fields.Count - 1
    TargetRange.Offset(1, intColIndex).Value = rs.Fields(intColIndex).Name
    Next

    ' Write recordset
    TargetRange.Offset(1, 0).CopyFromRecordset rs

    Application.ScreenUpdating = True
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    On Error GoTo 0
    Exit Sub

End Sub

Querying Access From Excel. Expected Query Name After Execute

2 Answers2