Use VBA to create and modify a query 3

[auraele]

In Access 2000 is there any way to create a query and/or modify an existing query? I have a form that builds an sql statement for a report and I would like to be able to save the sql statement as a query so that it can be used as a source for a mail merge in Word.

 

[jebry]

Hi!

I am assuming you are working with a database object already, which I will call dbs (original huh!)

To create a query you would do the following:

dbs.CreateQueryDef(queryname, sqlString)

This is straight from the help files so if you want, just look up QueryDefs!

hth
Jeff Bridgham

 

[auraele]

Thanks. But I don't know how to modify an existing query. Is there a way in VBA?

 

[jebry]

Hi!

Sorry, I missed that part. Check your help files for SQL Property. You can use this property to get the SQL for a query or to change it.

hth
Jeff Bridgham

 

[auraele]

Thanks again for the help.

Two problems. One is that I get an error trying to create the query using the syntax above. It says that it expected an equal sign.

The second problem is that when I click on createquerydef in help, nothing happens. Also, I cannot find help for sql properties. I believe that there is something wrong with my help files. I originally had Access 97 and upgraded to Access 2000. I have reinstalled help a few times but to no avail. Any ideas?

 

[JoeMiller]

This code will modify an existing query in a db, change the query name to match, and the stSQL is a string that contains the full SQL statement that should define that query:

[tt]
Dim stSQL As String
Dim loqd As QueryDef

Set loqd = CurrentDb.QueryDefs("MyQueryName&quot
stSQL = "SELECT * FROM Blah"

loqd.SQL = stSQL
loqd.Close
[/tt]

HTH Joe Miller
joe.miller@flotech.net

 

[Sameal]

As for your problem with thinking it needs an = sign...This is because of the way you wrote out the function. In my opinion it is good habit to get into to NOT use the () for a function unless you are setting the return value into a variable. If you are just running a sub or function and don't expect a return value, then instead of using () around your arguments, just list them after the method call seperated by commas. For this particular instance you are going to want to set the return value into an object accordingly.

dim rsObj as recordset
rsObj = dbs.CreateQueryDef(queryname, sqlString)

See if that helps you at all.