Change the SQL in an existing MS Access query

[Graywolf]

If this question has been answered before, just point me to the correct post
and I'll go look it up. I've already done a search and couldn't find what I'm
looking for.

Here's the background:
I'm running a MS Word Mailmerge and the data fields in the mailmerge do not
change, but I need to change which records get pulled from the underlying MS
Access table. I've tried to do this from the MS Word side of this problem and
have posted questions in their forum. I have come to the conclusion that it's
either impossible or way over my head. So now I'm here.
Here's the question:
If I can change the underlying MS Access query I think I will be able to
solve my problem. I would be doing this from VB6. I know this would probably
involve a querydef object. Can anyone give me a sample code snippet that
would allow me to change the SQL statements in an existing MS Access 2003
query?

Thanks in advance for any help.

 

[Douglas J. Steele]

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

strSQL = "SELECT Field1, Field2, Field3 FROM Table"

Set db = OpenDatabase("F:\Folder\File.mdb")
Set qdf = db.QueryDefs("NameOfQuery")
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
db.Close
Set db = Nothing

 

[Ken Snell [MVP]]

Generically:

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("NameOfTheQuery")
qdf.SQL = "NewSQLStatement"
Set qdf = Nothing
Set dbs = Nothing

 

[Graywolf]

Thanks to both Ken and Doug for your prompt replies.
A couple more questions if you don't mind.
Can this only be done in DAO? If yes, I will follow your code examples.

Is there a way to do this in ADO, and if so, how?

Thanks again.

 

[Bob Barrows]

Thanks to both Ken and Doug for your prompt replies.
A couple more questions if you don't mind.
Can this only be done in DAO? If yes, I will follow your code
examples.

Is there a way to do this in ADO, and if so, how?

Yes, you would have to use the JetSQL DDL statements DROP PROCEDURE and
CREATE PROCEDURE. See the online help in Access (look for JetSQL
Reference in the table of contents).

Alternatively, you can use ADOX - you can find examples via google.

 

[Bob Barrows]

Thanks to both Ken and Doug for your prompt replies.
A couple more questions if you don't mind.
Can this only be done in DAO? If yes, I will follow your code
examples.

Is there a way to do this in ADO, and if so, how?

Correction to my prior message:
Depending on nature of the saved query, you will either drop and create
a procedure, or drop and create a view.
Use DROP VIEW / CREATE VIEW if the query selects records without using
parameters.
Use DROP PROCEDURE / CREATE PROCEDURE if the query
a) is an action query (insert, update or delete)
or
b) contains parameters

 

[Piet Linden]

Yes, you would have to use the JetSQL DDL statements DROP PROCEDURE and
CREATE PROCEDURE. See the online help in Access (look for JetSQL
Reference in the table of contents).

Alternatively, you can use ADOX - you can find examples via google.

to follow on what Bob said... Allen Browne has examples of doing this
kind of thing with both ADOX and DAO on his website.

http://www.allenbrowne.com/tips.html

the ADO and DAO links are near the bottom...

 

[Douglas J. Steele]

While, as others have pointed out, yes it can be done with ADO or ADOX, why
would you want to?

DAO was written specifically for Access, and is the preferred method.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

 

[Graywolf]

Thanks to all of you gentlemen for your advice, code samples, links, etc. I
believe I have enough to overcome my problem.