Query Text

[danblack_nz]

Hi,

Can I get the sql contents of a query programtically?

Thanks

 

[boblarson]

Hi,

Can I get the sql contents of a query programtically?

Thanks

Yes, you can. It does kind of depend on what you are doing - so can you be a little more specific as to what you need and where you are trying to use it?

 

[Scooterbug]

You can build a sql statement in vba, yes. From there you can do a varity of things. Display the records in a listbox, set the recordsource of a subform, etc.

What exactly do you mean by "get the sql contents"?

 

[danblack_nz]

I am trying to get the text that makes up the query. From my vb app I am connecting to my access database, I can see what queries there and can run them, but would like to be able to view the script before executing

 

[Scooterbug]

you can use the debug.print command to view variables and such in the Immediate Window if that is what you are looking for.

 

[boblarson]

you should be able to use:

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

Set db = OpenDatabase("C:\PathToDb.mdb")
Set qdf = db.QueryDefs("NameOfQueryHere")

YourFormName.YourTextBoxName = qdf.SQL

qdf.Close
db.Close

Set qdf = Nothing
Set db = Nothing

 

[danblack_nz]

I should have given more detail (as you requested initially bob, apologies)

I am using vb.net....so DAO is not supported.
I am currently connecting with OleDb.

 

[danblack_nz]

Thanks Bob.
I have achieved my goal with the use of interop (not ideal)
Here is the code if anyone is interested. I converted it to c# as it is my preference:

.
.
using Microsoft.Office.Interop.Access;
.
.
.
Microsoft.Office.Interop.Access.Dao.Database db;
Microsoft.Office.Interop.Access.Dao.QueryDef qDef;
Microsoft.Office.Interop.Access.Dao.DBEngine dbe = new Microsoft.Office.Interop.Access.Dao.DBEngine();
db = dbe.OpenDatabase(@"C:\temp\AccessTester.accdb", null, null, null);
qDef = db.QueryDefs["q2"];
string queryContents = qDef.SQL;
.
.

 

[boblarson]

Thanks Bob.
I have achieved my goal with the use of interop (not ideal)
Here is the code if anyone is interested. I converted it to c# as it is my preference:

And I did a test where I set a reference to DAO 3.6 and then used this code successfully:

   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim dbe As New dao.DBEngine
        Dim db As dao.Database
        Dim qdf As dao.QueryDef

        db = dbe.OpenDatabase("C:\Temp\Test.mdb")
        qdf = db.QueryDefs("qryTest")
        Me.TextBox1.Text = qdf.SQL
        db.Close()
        db = Nothing
        dbe = Nothing
    End Sub