query object

A

alekm

Hi,
how can I reach a query that exists in my access db, eg "simpleSql", from
code.
Thanx

alek_mil
 
A

alekm

Ok, that's the way to execute query but how can I reach it's content, eg
Select * from .....
thanx
alek_mil
 
K

Keith

alekm said:
Ok, that's the way to execute query but how can I reach it's content, eg
Select * from .....
thanx
alek_mil
Click to expand...
Ah, now you didn't ask that ;-)

Something like:

Dim db as DAO.Database, rs as DAO.Recordset, strSQL as String
Set db = Currentdb
strSQL = "Select * from qryMyQuery"
Set rs = db.OpenRecordset(strSQL)
With rs
'Do stuff
End With

This assumes DAO library. It's also good practice to close the db and rs
objects and set them = Nothing when you've finished.

HTH - Keith.
www.keithwilby.com
 
A

alekm

I did not put question right I guess.
There is a query 'X' in my database. How can I get it's sql from code?
Furthermore, how can I change it's sql from code?

Thanx
alek_mil
 
K

Keith

alekm said:
I did not put question right I guess.
There is a query 'X' in my database. How can I get it's sql from code?
Furthermore, how can I change it's sql from code?
Click to expand...

I'm not aware of a method to do that but (at the risk of telling you
something you already know) you can execute SQL queries from within code -
so you could write Query X in a code window and manipulate it from there.

Regards,
Keith.
www.keithwilby.com
 
D

Douglas J Steele

Dim qdfCurr As DAO.QueryDef
Dim strSQL As String

Set qdfCurr = CurrentDb().QueryDefs("X")
strSQL = qdfCurr.SQL

will give you the SQL associated with a query.

Dim qdfCurr As DAO.QueryDef
Dim strSQL As String

strSQL = "SELECT Field1...."

Set qdfCurr = CurrentDb().QueryDefs("X")
qdfCurr.SQL = strSQL

will let you change it.

(NOTE: This assumes you have a reference set to DAO. If you're using Access
2000 or 2002, you may have to add that reference)
 
K

Keith

Douglas J Steele said:
Dim qdfCurr As DAO.QueryDef
Dim strSQL As String

Set qdfCurr = CurrentDb().QueryDefs("X")
strSQL = qdfCurr.SQL

will give you the SQL associated with a query.

Dim qdfCurr As DAO.QueryDef
Dim strSQL As String

strSQL = "SELECT Field1...."

Set qdfCurr = CurrentDb().QueryDefs("X")
qdfCurr.SQL = strSQL

will let you change it.
Click to expand...

Blimey, there's always something new to learn, thanks for educating me :eek:)

Keith.
 
A

alekm

Thanx

Douglas J Steele said:
Dim qdfCurr As DAO.QueryDef
Dim strSQL As String

Set qdfCurr = CurrentDb().QueryDefs("X")
strSQL = qdfCurr.SQL

will give you the SQL associated with a query.

Dim qdfCurr As DAO.QueryDef
Dim strSQL As String

strSQL = "SELECT Field1...."

Set qdfCurr = CurrentDb().QueryDefs("X")
qdfCurr.SQL = strSQL

will let you change it.

(NOTE: This assumes you have a reference set to DAO. If you're using Access
2000 or 2002, you may have to add that reference)
Click to expand...
 
Top