Parameter query automation (1 Viewer)

[CS45]

Hi all, I’m new to forums so I’ll give it a shot. I have an existing parameter query that pulls data from a db. I want to create something to automatically run that query and automatically fill in the needed parameters. Eventually I will be putting all this into task scheduler to run this query over night. Any suggestions?

 

[pbaldy]

One option would have the query point to a form for the parameters. Your code can populate the form controls.

 

[Isaac]

So, you might write a VBA procedure to run the query, but how would it know what the parameter value should be? If that is already handled on your end, then you could create a vba procedure to run upon database open and then schedule the database to open from the task scheduler.

Or sometimes I have written a VBScript file to open the database and run a procedure, then close it - your task scheduler could call the VBScript.

 

[CS45]

The two parameters are the date (we run the query weekly so the it’s the date to start pulling the data from) and a username and password. I’m very unfamiliar with Access and VBA, do you have any examples of what you mean?

 

[pbaldy]

I assume it's some kind of action query (append, update, etc)? If the query points to a form:

Me.TextboxName = Date()
DoCmd.OpenQuery "QueryName"

It would get more complicated if you want a date other than the current date, but you haven't specified how the desired date relates to the current date.

 

[Isaac]

What Paul said.

 

[CS45]

The date entered is the data from which we start pulling data. For instance if I put in 6/15/20, it pulls all the data starting at 12:00am on 6/15/20 up until the current time.

 

[Isaac]

I think what we mean is when would you want to schedule the running of this query and relative to that, what date would you want to use

 

[CS45]

I see. Best example I can give is this. On 6/15 we ran the query and entered 6/8/20 so that we got all the data from the week before. I’m trying to schedule the query for 12:00am on a Monday morning and have the date be the Monday prior to get just the data from exactly one week. Does that help?

 

[pbaldy]

Me.TextboxName = Date() - 7

 

[CS45]

Can you put that in idiot language please lol I’ve been working with Access for about 2 days, VERY unfamiliar with how to do pretty much anything.

 

[Isaac]

Others are probably better than I at writing concise ways to grab that date, if you're talking about the previous Monday from 'whenever' (my solutions in those moments usually use a less-efficient vba method), so instead, I will show what you might use for the other part of it ... the VBScript solution I was suggesting.

Writing this code in Notepad and saving it as .vbs filetype and then scheduling it to run whenever.

dim objAccessApp, strDBPath

'ADJUST BELOW ***********************
strDBPath="c:\users\username\desktop\Testing 20200617.accdb"

set objAccessApp=createobject("Access.Application")
objAccessApp.OpenCurrentDatabase(strDBPath)

'ADJUST BELOW **********************
objAccessApp.Run "UpdateTable" 'Name of your vba procedure

objAccessApp.CloseCurrentDatabase

 

[CS45]

Great thank you. So I now need a procedure to automatically run the query and input the parameters, correct?

 

[pbaldy]

You may not need to input parameters, if they're predictable or fixed. The query could have:

WHERE DateField > Date() - 7

instead of

WHERE DateField > [Enter date]

 

[CS45]

Right now in the criteria section of the query it states:
>= [>= date]
Are you saying it could just be:
>= Date()-7
I could make a new identical query with that change as my boss sometimes like to run the query with a different date entered. I still don’t know how to automate the login and password because I don’t think that’s a query parameter but rather an to get into the database it pulls from because it is secure.
Before I look at that though, what would the procedure look like with that new query without the date parameter, supposing there was no login parameter?

 

[pbaldy]

Are you saying it could just be:
>= Date()-7

Yes, that's what I'm saying.

I assume Issac's method would work. I would create a db that had the necessary parts (could start as a copy of the working db). I'd create a function that performed the process I wanted and then quit. I'd call that function from a macro named autoexec, which automatically runs when the db opens. Then I'd run that db from scheduled tasks. Not saying it's better than what Issac suggested, it's just the way I've done it. Maybe I'm lazy, it's just easier for me to do all the work within Access.

 

[NauticalGent]

Others are probably better than I at writing concise ways to grab that date, if you're talking about the previous Monday from 'whenever' (my solutions in those moments usually use a less-efficient vba method), so instead, I will show what you might use for the other part of it ... the VBScript solution I was suggesting.

Writing this code in Notepad and saving it as .vbs filetype and then scheduling it to run whenever.

dim objAccessApp, strDBPath

'ADJUST BELOW ***********************
strDBPath="c:\users\username\desktop\Testing 20200617.accdb"

set objAccessApp=createobject("Access.Application")
objAccessApp.OpenCurrentDatabase(strDBPath)

'ADJUST BELOW **********************
objAccessApp.Run "UpdateTable" 'Name of your vba procedure

objAccessApp.CloseCurrentDatabase

I had no idea you could execute an Access Sub/Function from an external source/script. I didn't beleive it at first but I did a quick test and it worked as advertised. 'Purt near cool, thanks for the code!

Could be a game-changer...

 

[Isaac]

I had no idea you could execute an Access Sub/Function from an external source/script. I didn't beleive it at first but I did a quick test and it worked as advertised. 'Purt near cool, thanks for the code!

Could be a game-changer...

Very welcome! Just one more tool for the toolbelt

 

[CS45]

Where would I write the function to execute the query so that it can be run from the vbscript? Do I have to put it in a form or how does that work? And do I just put the function name into that script or is there a path that I have to include?

 

[Isaac]

You would go to the vba project in Access and insert a module. Then write a Sub containing the code to execute the query. Then refer to that Sub name in the VBScript, where indicated.

As Paul mentioned, I assume this is some kind of make table, append or update query?