VBA commands for opening, displaying, saving a querydef

  • Thread starter Thread starter Andy
  • Start date Start date
A

Andy

Hello,

Using VBA, I'd like to open an existing query (created in
design view) and display the SQL Query on the screen.

Then I will use some string commands to change the where
clause, and save the result with the same query name.

Is this possible in VBA? If so what are the commands for
opening, displaying and saving the manipulated query? I
tried using Macros to manipulate the query with no success.

Thanks in Advance

Andy
 
Andy said:
Hello,

Using VBA, I'd like to open an existing query (created in
design view) and display the SQL Query on the screen.

Then I will use some string commands to change the where
clause, and save the result with the same query name.

Is this possible in VBA? If so what are the commands for
opening, displaying and saving the manipulated query? I
tried using Macros to manipulate the query with no success.
Click to expand...

Why do you want to open and display it in design view? You can just change
the SQL property of the querydef directly from your code.
 
I wouldn't do it that way

DAO QueryDefs have an SQL property, which represents the SQL Statement, for example

SELECT * FROM Customer

So, using DAO, if I wanted to change this stored query to select only the OrderID from the Orders Table, I would run this

Dim db As DAO.Databas
Dim qdf as DAO.QueryDe

Set db = CurrentD
Set qdf = db.QueryDefs("QueryNameHere"

'many different methods of doing this...here's a simplistic one

qdf.SQL = "SELECT OrderId from Orders

Of course, if you only want to change PART of the query, you could use the VBA string parsing functions (MID, LEFT, RIGHT, INSTR, etc) to cut up and change the string. All Access stored Queries are really just SQL Statements, so can be modified in this manner

Hope this helps

Barry
 
Back
Top