How to read/edit properties of existing query through VBA

  • Thread starter Thread starter Arvi Laanemets
  • Start date Start date
A

Arvi Laanemets

Hi

How can I through VBA read or change properties (p.e. CommandText) for an
existing query? I looked through the Object Browser, and tried anything what
looked like it'll have some tie to queries in Watch Window, but somehow I
didn't hit it. The best I did get were query names
{CurrentData.AllQueries(i)}.

At moment I'm trying this with Access2003 database, but I'll need it with
Access2000 too.
 
I tested the code below with an action query. I haven't tested with a select
query, you *might* need to replace 'ADOX.Procedure' with 'ADOX.View' and
'cat.Procedures' with 'cat.Views' for a select query.

Public Sub TestSub()

'DAO version - requires reference to Microsoft DAO 3.6 Object Library.
Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("qryCreateAbsence")
Debug.Print qdf.SQL

'ADO version - requires references to Microsoft ActiveX Data Objects 2.x
Library and
'Microsoft ADO Ext 2.x for DDL and Security object library.
Dim cat As ADOX.Catalog
Dim pr As ADOX.Procedure
Dim cmm As ADODB.Command

Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
Set pr = cat.Procedures("qryCreateAbsence")
Set cmm = cat.Procedures("qryCreateAbsence").Command
Debug.Print cmm.CommandText

End Sub
 
Hi

Thanks very much

It looks very like the code (ADO), I used to create my queries. So it means
there is no direct access p.e. in Watch window to queries, as I can't access
the database object without some code running. OK, I'll try your suggestion
then. I think I create a function for it - like the one I got from Microsoft
KB for creating queries, but modified after your example.

Arvi Laanemets
 
Back
Top