Pass VBA to a SQL query

[soupisgood84]

I'm sorry if someone has already answered this question, but it's starting to get late and my brain is fried from searching.

I am looking for some guidance no how to pass VBA code into either a pre-existing query or create a new query for that instance. I have toyed with the ADODB.Recordset a little, but from all that I have seen thats not quite what I am looking for.
I just need something that I can have either predefined WHERE clauses or create my own via a form and some background code.

As always, any help is greatly appreciated. Thanks

 

[zapzip]

You need to construct an SQL statement from the VBA code and then copy-paste it into Access Query design grid- SQL.

Make sure to follow SQL syntax adding the ; for instance.
The VBA code will have a DoCmd.RunSQL followed by a variable or the actual SQL statements.

Hope this gets you started

Change. Remember. Enjoy. Do it often. Dotcom

 

[ProgramError]

You may want to take a look at the following thread
thread702-1411132
Where I construct an SQL statement in VBA

Please note: you can only run action queries not select queries using docmd.runSQL

as for predefined WHERE clauses, how about a

Dim CLAUSE as integer
Dim strWHERE as String
CLAUSE = 1
select case CLAUSE
Case 1
strWHERE = "WHERE this = that"
Case 2
strWHERE = "WHERE this = the other"
else case
strWHERE = ""
end select



Ian Mayor (UK)
Program Error
Always make your words sweet and nice. Because you never know when you may have to eat them.

 

[djayam]

Hi mate,

I have just done this for my own db yesterday..!!

Create a variable for you basic query SQL and another one for the WHERE clause:

Dim vSQL As String
Dim vWHEREClause As String

vSQL = "<your code>"
vWHEREClause = " <your code>" 'NOTE THE LEADING SPACE - THIS IS NECESSARY

CurrentDb.QueryDefs("<your query name>").SQL = vSQL & vWHEREClause

This will change the SQL of your query for next time you run it.

vWHEREClause could just be "" if you didn't need one.

Is that what you were after?

 

[soupisgood84]

djayam....
Perfect! Exactly what I was looking for! Thanks a bunch.

 

[rib742]

I have some pass through SQL queries that I want to re-construct in VBA so I can pass some form based parameters. I can make some adjustments and use DoCmd.RunSQL but there are some that I really need to use the pass throughs. Is there a way to reconstruct pass throughs that I'm not seeing?

 

[PHV]

Have a look at the SQL property of the DAO.QueryDef object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886