Excel Excel VBA to run Access Parameter Query Error 3265


Joined
Jun 30, 2011
Messages
1
Reaction score
0
Hello,

I am new to VBA and have successfully got some VBA code in Excel to open and access a parameter query in Access 2003. When I run the query without defining the parameters it promptly returns all results but when I add in the query parameters I get a Run-Time error 3265 'Item not found in collection'. I've looked closely at both the field name spellings in the VBA and the Access query and they are both the same.

Here is the code. The error occurs in step 3 at the .parameter line

Sub RunParameterQuery()
'Step 1: Declare your variables
Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset
Dim i As Integer

'Step 2: Identify the database and query
Set MyDatabase = DBEngine.OpenDatabase _
("D:\Data\Database Tools\Marketing_Database.mdb")
Set MyQueryDef = MyDatabase.QueryDefs("qry_parameter_test")

'Step 3: Define the Parameters
With MyQueryDef
.Parameters("[Gender]") = Range("D3").Value
.Parameters("[Age]") = Range("D4").Value
End With

'Step 4: Open the query
Set MyRecordset = MyQueryDef.OpenRecordset

'Step 5: Clear previous contents
Sheets("Sheet2").Select
ActiveSheet.Range("A6:K10000").ClearContents

'Step 6: Copy the recordset to Excel
ActiveSheet.Range("A7").CopyFromRecordset MyRecordset

'Step 7: Add column heading names to the spreadsheet
For i = 1 To MyRecordset.Fields.Count
ActiveSheet.Cells(6, i).Value = MyRecordset.Fields(i - 1).Name
Next i

MsgBox "Your Query has been Run"

End Sub


Thanks in advance
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top