How to run sql in Microsoft Visual Basic with parameters

[mchio]

Hello,

I have a "create table as select..." query defined in a Microsoft Access 2003 that ask for a date (as string) as a parameter.

Now I need to automate a little more the execution of this query using as Subroutine by don't asking for the user to input this parameter. Instead of that I created a function that generate this value.

How can I could run this query in Visual Basic? What command can I used?

Thanks,

Mach.

 

[lameid]

You can substitute the parameter with the function in the query.

Next you probably should check out the following VBA commands...

Docmd.setwarnings
Docmd.openquery
Docmd.runSQL

You'll want to use the first command and either of the last 2 depending on how you want to manage things.

Don't forget to turn warnings on after turning them off.

 

[mchio]

Thanks lameid.

But the other problem I have is that I need to pass a parameter to this query. I have a function that evaluate the desire date in string format and I need to filter the query by this value.

Something like this:

" from Table1 where LOCDAT = DateinYYYYMMDD(WorkDate())"

Any suggestion?

Mairo.

 

[lameid]

In your example you don't have any literals or anything that looks like a parameter so, do something like that.

Personally, I would use the format function instead of writing a custom function for DateinYYYYMMDD. That way you only have one custom funtion and not 2 (workdate being the other).

from Table1 where LOCDAT = format(WorkDate(), "yyyymmdd")

 

[mchio]

Thanks Lameid.

Everything is working fine now.