Change Access Query Criteria using Excel

[mrholiday]

Dear all,

I looking for a solution to change a Access Query Criteria using a VB code in Excel, without opening the Access DB and changing the criteria manualy?


My normal Job is it:
1) I Work all the time in Excel
2) At the end of the work in Excel, I need to open Access
3) Open a query (Called "MyQuery")
4) Change one parameter the Date: Between 01/10/2008 and 30/10/2008 (This for each month)
5) Run the query

I looking to get rid of points 2) to 5) by replacing with a Command button on an Excel sheet


Sub Change_Criteria_And_Run_Query()


Dim mydbase As Object
Set mydbase = CreateObject("Access.Application")
mydbase.OpenCurrentDatabase ("C:\My doucments\DB1.mdb")

mybase."MyQuery"."Date Criteria" = Between (Worksheet("Sheet1").Range("A1").value) and (Worksheet("Sheet1").Range("A2").value)

>>>>>The above line of code I have problems to get right!

mydbase.DoCmd.RunMacro "MyQuery"

End Sub


Is this possible to do?



Thanks for any tip
Jerry

 

[thart21]

rorya,

I am trying to use the code you posted below for a similar task. I am using a form that comes up when my workbook is opened. It has a drop down for users to pick a date to filter my Access query on. Futures Mth is the alias I am using for my date field. I can get the headings to show up but am getting an "Item cannot be found in the collection..." message on this line. Any ideas on what might be happening?

.Parameters("[Futures Mth]") = " & frmPickDate.cboPickMonth.Value & "")"

Thanks,

Toni

 

[RoryA]

It sounds like your parameter name is incorrect (or you haven't actually created parameters, you've just typed them into the query grid).

 

[thart21]

I don't have any parameters specified in the Access query, I need to filter it in Excel based on user selection in the combo box, is that what you mean? If I place a parameter in my Access query, won't it come up when I try to run the query in Excel in addition to my form with the drop down? New to parameters as I'm sure is obvious, thanks for your help. Just looking for a way to get it to pull the data from my crosstab query in Access to Excel based on the date chosen in the drop down.

 

[RoryA]

The whole point of the code I posted is to pass parameters to a parameter query in Access. If that's not what you are doing, then that isn't the code for you!

 

[thart21]

Ah, that makes sense. Unfortunately my crosstab query will not allow me to use parameters in Access for some reason. I will keep searching for a solution. Thanks again.

 

[RoryA]

You could use a normal query in Access and base a pivot table in Excel directly on the query?