An Access parameter query is a kind of interactive query that prompts you for criteria before the query is run. Parameter queries are useful when you need to ask the query different questions using different criteria each time you run it.
Now we all know you can pull data from Access into Excel using MS Query. The problem is that MS Query doesn't let you pull in Parameter queries. There may be a technical reason for this, but I like to think it's just Microsoft's way of keeping things interesting. After all, technical roadblocks are the spice of life.
If you've had enough spice, I'll show you a VBA workaround that will enable you to run an Access parameter query from Excel.
The Problem:
I've built this parameter query in Accesss. This particular query will ask the user for a Region and a Business Segment. This works fine in Access.
I've named this query MyParameterQuery.
The problem is when I try to connect to this query via MS Query in Excel, I don't see it in the list. It turns out that Parameter queries are not available in list of connectable objects.
Even if I try to get cocky, brute forcing MS Query to look at the query, I get the dreaded "Too Few Parameters" error.
The Solution:
My solution is to use a bit of VBA to pull the data based on parameters you enter directly into Excel.
The first step is to set up a spreadsheet like the one shown here. You'll notice that the needed parameters will be fed from Cells D3 and D4.
Once your spreadsheet is ready to go, switch over to VBA and enter this code into a standard module.
Note: You will need to add a reference to the Microsoft DAO XX Object Library (where XX represents the latest version you have) .
-
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 _
-
("C:\Integration\IntegrationDatabase.accdb")
-
Set MyQueryDef = MyDatabase.QueryDefs("MyParameterQuery")
-
-
'Step 3: Define the Parameters
-
With MyQueryDef
-
.Parameters("[Enter Segment]") = Range("D3").Value
-
.Parameters("[Enter Region]") = Range("D4").Value
-
End With
-
-
'Step 4: Open the query
-
Set MyRecordset = MyQueryDef.OpenRecordset
-
-
'Step 5: Clear previous contents
-
Sheets("Main").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
After the code is ready, running it will make data magically appear based on your parameters!
Who needs MS Query?
This technique allows you to build some interesting reporting solutions with relatively little effort.
RELATED STUFF
- Using Access to Combine Multiple Excel Files: Method 1
- VBE Tips I Wish I Knew 5 Years Ago
- Sending Data to Excel using Access SQL
- Using Access to Combine Multiple Excel Files: Method 2
- Passing Multiple Values to One Parameter
- Using the Switch Function in Excel
- Special Characters are a Pain in the Asterisk
- Why is Excel so Negative about Hiding Subtotals
- Why Replace the DOM Interface
- Sending Variable Access Data To An Excel Range
datapig: Thanks for the reply. It's not working though, I think I made a mistake here. This is the new code. It's returning the error "Compile error: Invalid outside procedure"
Defects_Master.Execute "qry_FPY_CustomDates", dbFailOnError
Defects_Master.Execute "qry_FPY_CustomeDates", dbFailOnError
Sub RunParameterQuery()
'Step 1: Declare your variables
Dim Defects_Master 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 Defects_Master = DBEngine.OpenDatabase _
("D:\Documents and Settings\e479196\Desktop\FPY & TPY\Defects_Master.mdb")
Set MyQueryDef = Defects_Master.QueryDefs("qry_FPY_CustomDates")
'Step 3: Define the Parameters
With MyQueryDef
.Parameters("[Enter Start Date]") = Range("C2").Value
.Parameters("[Enter End Date]") = Range("C4").Value
.Parameters("[Enter Cell Name]") = Range("C6").Value
.Parameters("[Enter Material Number]") = Range("C8").Value
End With
'Step 4: Open the query
Set MyRecordset = MyQueryDef.OpenRecordset
'Step 5: Clear previous contents
Sheets("Main").Select
ActiveSheet.Range("A14:K10000").ClearContents
'Step 6: Copy the recordset to Excel
ActiveSheet.Range("A15").CopyFromRecordset MyRecordset
'Step 7: Add column heading names to the spreadsheet
For i = 1 To MyRecordset.Fields.Count
ActiveSheet.Cells(14, i).Value = MyRecordset.Fields(i - 1).Name
Next i
MsgBox "Your Query has been Run"
End Sub
Hi all,
You do not need VBA code to perform this.
- create an ODBC Data Set using your Access 2003 DB.
- Then create a simple query without parameters inside.
- In excel 2007 do a "Data" / "From Other Sources" / "From MS Query"
- Select the ODBC data set created before
- Select the access query and make a "Return data to excel"
- Make a "Connection Properties" in excel and "Edit".
- At this stage you will see "Edit Query" in properties
- MS Query opens and now add you parameters
- Make a "return data to excle"
And you are done...
FranckF
Hi Frank, I am so desperate here. Both ideas sound great, but I can't get it to work. I've never done VBA before and I tried to do exactly what you have above but with my database and query names. You mention adding a reference to the MS DAO xx object Library. I don't know how to do that. Is that what I'm missing?
'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 _
("C:\Documents and Settings\schadlfr\My Documents\SusanT.mdb")
Set MyQueryDef = MyDatabase.QueryDefs("Query1")
I do NOT have MS Query and don't think my company will allow it but I will try. But does the machine that will be using this need to have it also or just the developer?
Sorry for the questions, but i'm new and would love to do this.
Thank you
p.s. I tried the 10/6 option but couldn't figure out the connection properties and edit part...
Thank you! I had been trying to run a LIKE query from Excel to Access and it was NOT WORKING. I tried * and % and nothing worked. This worked the first try, and it seems to be faster then by connected through ADO. Wow. Awesome. I also set the variables to nothing at the end.
Sub RunParameterQuery(BondName As String)
'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 _
(BABsDatabaseFilePath)
Set MyQueryDef = MyDatabase.QueryDefs("Bond_Name_Query")
'Step 3: Define the Parameters
With MyQueryDef
.Parameters("[Enter Bond Name]") = BondName
End With
'Step 4: Open the query
Set MyRecordset = MyQueryDef.OpenRecordset
If MyRecordset.RecordCount = 0 Then frmBondNames.FrameNameBox.Visible = False: GoTo End_With_No_Records:
MyRecordset.MoveFirst
frmBondNames.lvBondNames.ListItems.Clear
frmBondNames.lvBondNames.Sorted = False
If Not MyRecordset.BOF And Not MyRecordset.EOF Then
frmBondNames.FrameNameBox.Visible = True
'frmBondNames.FrameNameBox.Top = frmBondNames.txtCUSIP.Top
MyRecordset.MoveFirst
While Not MyRecordset.EOF
With frmBondNames.lvBondNames
Set li = .ListItems.Add(, , MyRecordset("Bond_Name").value)
li.SubItems(1) = MyRecordset("Bond_Type").value
End With
MyRecordset.MoveNext
Wend
Else
frmBondNames.FrameNameBox.Visible = False
End If
End_With_No_Records:
'MsgBox "Your Query has been Run"
Set MyDatabase = Nothing
Set MyQueryDef = Nothing
Set MyRecordset = Nothing
End Sub
you are a freaking genius!
I have been mucking with this stupid problem all day.
you rock
THANK YOU! This worked like a charm! I am so grateful for this quick and easy macro. So many other help sites and message boards had complicated solutions or told you to create a parameterless version of the query in the DB, which 1. I couldn't do, since I don't have that kind of access to the DB, and 2. didn't help me, since I needed the parameters in my query.
Thank you so much once again!
"Now we all know you can pull data from Access into Excel using MS Query. The problem is that MS Query doesn't let you pull in Parameter queries."
Just for the record, this comment is wrong. You can in fact run parameter queries using MS Query from Excel on an Access database. In fact, it's pretty cool because you can house the parameters in spreadsheet cells. I do it all the time, and it works great. The Access DB then just becomes a backend for Excel storing and retrieving data.
Brilliant. I have hated MS Query for many years. Running the query natively in Access is so much better. Thanks very much for showing how easy it is to set up VBA
Wow! Really worked like a charm! I need to get deeper into VB because this just rocks.
If you need to "add a reference to the Microsoft DAO XX Object Library", then in the VB editor, go to "Tools" and "References" and select the "Microsoft DAO X.X Object Library" that appears on your list (where X.X = the version on your system).
how do I make an ms query use a range of cells though? Can get it to refer to a cell for the parameter but wondering how to make it use the values in a range of cells
Hey Adrian Brown. I too used to integrate Excel into Access parameter queries, but now can't figure out how to do it in Office 2007. WHich version are you using and how are managing it?
Any ideas how to implement this in a secure database?
Please help! This stuff is great but I am experiencing a an error.
It runs fine returning all data in the query without the .parameter code but when I insert that code it kicks back an error Run-Time error '3265' Item not found in collection.
I set everything up as specified above and double checked all the spelling etc.
'Step 3: Define the Parameters
With MyQueryDef
.Parameters("[Acct]") = Range("k2").Value
.Parameters("[Gender]") = Range("D3").Value
.Parameters("[Age]") = Range("D4").Value
End With
I have done all the code and set the DAO in my preferences but I get a Runtime error "3343". Unrecognised database format 'S:\IRRSD_DATA|Participation\NQ\NQ Participation_Finalised.accdb'.
My code is:
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 _
("S:\IRRSD_DATA\Participation\NQ\NQ Participation_Finalised.accdb")
Set MyQueryDef = MyDatabase.QueryDefs("Report1")
'Step 3: Define the Parameters
With MyQueryDef
.Parameters("[Enter Hub Name]") = Range("C2").Value
End With
'Step 4: Open the query
Set MyRecordset = MyQueryDef.OpenRecordset
'Step 5: Clear previous contents
Sheet1.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
I have the same problem as Mark on July 13th. Has anyone found a solution to his problem?
Mark,
Lori,
It looks like you're trying to hit a network directory that has been mapped to your Windows Explorer as a relative drive. In this case, you'll have to use the full absolute network path.
For Example:
Instead of
B:\SalesOps\AccessDatabase.accdb
I would use
\\skuspldc03\data\SalesOps\AccessDatabase.accdb
Michael,
I have transcribed your code verbatim except for the name of my DB and query.
I am getting the dreaded error 3343: Unrecognized Database Format.
here are two screen shots: 1) My references and 2) the code.
Can you tell me what I need to to do to fix this problem. I am using Excel 2007 and Access 2007.
thanks
glen
references:
MS Excel 12.0 Object Library
MS DAO 3.6 Object Library
Code:
'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 _
("C:\My Documents\TRHDR1.accdb")
Set MyQueryDef = MyDatabase.QueryDefs("PARAMETERQUERY")
'Step 3: Define the Parameters
With MyQueryDef
.Parameters("[Enter Name]") = Range("C1").Value
End With
'Step 4: Open the Query
Set MyRecordSet = MyQueryDef.OpenRecordSet
'Step 5: Clear Previous contents
Sheets("Main").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
Actovesheet.Cells(6, i).Value = MyRecordSet.Fields(i - 1).Name
Next i
MsgBox "Your Query has been run"
End Sub
After having the '3343 - Unrecognized Database Format' error I changed my references in excel to "Microsoft 12.0 Access Database Engine Object Library". This appears to have cleared up the problem, even though references are declared as DAO they're apparently very compatible.
I'm having error 3265 "Item not found in collection". My Query has the following cirteria:
Field: WORK ORDER: Trim([WO_HDR.WO_NUM])
Cirteria: Like [Enter WO Number and include "*" at end of WO Number to include NCR WO]
It seems like the trim function is messing it up. any ideas how to get around it?