Change an Access Query Using VB 3

[Dauphy]

Hi: I've copied and pasted the following code from a previous forum which answers most of my question, specifically, how do you create/Change an Access Query through VB. The part I'm stuck on is the qdl.sql which contains the query itself. In the following code; how do you assign the query in Access as the qdl.sql string.

*******************************************************



Dim dbs As Database
Dim rst As Recordset
Dim qdf As QueryDef

'Create the new record set based on our new SELECT statement
Set dbs = OpenDatabase(App.Path & "\safety.mdb&quot
'Delete the old query
dbs.QueryDefs.Delete "ReportQuery"
'Recreate the query with the new criteria
Set qdf = dbs.CreateQueryDef("ReportQuery&quot
qdf.SQL = "SELECT * FROM " & TABLE & strCriteria & " ORDER BY Year, Month ;"
'Open the query recordset
Set rst = dbs.OpenRecordset("ReportQuery&quot

Of course, your "qdf.SQL" statement will be the SQL that creates the query you are looking for.
****************************************************

This last paragraph; how do I accomplish that???

Thank you.

 

[johnwm]

If you are not familiar with SQl, you can get a start by creating your query in access, then, with the query open, select View/SQL view from the menu.
This shows you the SQL statement generated by Access (which you can copy and paste to your code)
Watch out for Date formats and wildcard characters, which are different in AccessSQL and Standard SQL. VBHelp covers these topics quite well, and there have been several posts on the topic in this forum Let me know if this helps
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'There are 10 kinds of people in the world: those who understand binary, and those who don't.'

 

[GFrantsen]

I'm not sure I follow you.
If you are wondering how to get that SQL into that query, just add it as part of the CreateQueryDef command.

SQL = "SELECT * FROM " & TABLE & strCriteria & " ORDER BY Year, Month ;"
Set qdf = dbs.CreateQueryDef("ReportQuery", SQL)

Now if you just want to return the query with out a name, just leave the "ReportQuery" as "". This creates a temporary query that won't show up in the database.

If that's NOT what your wanting to know, sorry I missed the boat.

later.
greg.

 

[Dauphy]

Thanks to both of you; which sheds some light on my dilemma. Sorry, I'm fairly new to programming in VB.

I'll eloborate on my problem; I have a pre-defined query in Access; which I've created a report for in Access. Through VB, I need to update that query so that I can run the report. The
statement:

Set qdf = dbs.CreateQueryDef("Reportquery",SQL) helped as I didn't know I could add the ,SQL to tell Access that was the SQL statement to use.

My problem now is I want to update the existing query called "ReportQuery" and not CreateQueryDef; as it already exists.

Thanks again....

 

[CCLINT]

Then delete the first query, and then create the new query. [/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[GFrantsen]

CCLINT is correct.
I had a simular problem to this and the only way I could get around it was to delete the query first then recreate it. As long as you use the same name, you are ok when you run the report.
Where I ran into additional problems was that I was creating an application for multiple (30-50) users accessing the SAME ACCESS database on a network. Because any one of them could be deleting/redefining/reporting the same report at the same time, it was creating unpredictable (to say the LEAST) results. I got around this by creating a query for each user (using the userid as a prefix), copying the report in ACCESS (again using the userid as a prefix), and programatically setting the source for this new report to the newly created user-specific query (does that make sense?). I then deleted the copied report and query when the user was done with them. This worked quite well. Except, the database grew rather quickly and I had to compress it fairly often (this is an ACCESS problem related to how it deletes things - not a VB problem)

Hope this helps.
later.
greg.