Call query with parameters from excel

[Vbanoob98]

Hi everyone, I have this code that calls a query from my access database. The issue is that I need to call a query with 2 parameters, how can I add this to the code?

Thanks in advance

Sub test111()

Const cstrPath As String = "C:\Data.mdb"
Const cstrQuery As String = "View_qry"
Dim dbe As Object 'DAO.DBEngine '
Dim rs As Object 'DAO.Recordset '
Dim ws As Worksheet

Application.DisplayAlerts = True 'leave alerts on during testing '
Set dbe = CreateObject("DAO.DBEngine.120")
Set rs = dbe.OpenDatabase(cstrPath).OpenRecordset(cstrQuery)

For i = 0 To rs.Fields.Count - 1
        Sheets("Sheet1").Cells(1, i + 1) = rs.Fields(i).Name
    Next i
If Not rs.EOF Then
    Set ws = ThisWorkbook.Sheets("Sheet1")
    ws.Range("A2").CopyFromRecordset rs
End If

rs.Close
Application.DisplayAlerts = True

End Sub

 

[xenou]

Here is an example from Microsoft's documents:

Parameters collection (DAO)

Office developer client VBA reference documentation

docs.microsoft.com

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Set dbs = CurrentDb

'Get the parameter query
Set qfd = dbs.QueryDefs("qryMyParameterQuery")

'Supply the parameter value
qdf.Parameters("EnterStartDate") = Date
qdf.Parameters("EnterEndDate") = Date + 7

'Open a Recordset based on the parameter query
Set rst = qdf.OpenRecordset()

 

[Vbanoob98]

Thanks, do you know how to set my database to currentdb?

 

[xenou]

as in the code sample. just set dbs = currentdb. Or as you did before I would think would still work. Or don't do it at all and just use currentdb straight up, which is what I usually do.

 

[Vbanoob98]

Yea I've been reading on currentdb, but I still dont understand how it will know which one. As I have like 3 different ones

 

[xenou]

Its the one you are running the code in. By definition there can only be one.

 

[Vbanoob98]

Ah should have specified that I was running this from excel

 

[xenou]

You don't need a currentdb. just use your dbe variable as you did in your original code (usually this is by convention dbs or db, not dbe but it doesn't matter).

 

[Vbanoob98]

I get an error for QueryDefs as Method or data member not found

 

[Vbanoob98]

Managed to make it work. But it seems I was only able to take 35k lines. Is there a way to take more?