query object

[alekm]

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

alek_mil

 

[Keith]

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

Docmd.OpenQuery "simpleSql"

Regards,
Keith.
www.keithwilby.com

 

[alekm]

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

 

[Keith]

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

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

 

[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

 

[Keith]

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?

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

 

[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)

 

[Keith]

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.

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

Keith.

 

[alekm]

Thanx

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)