Creating Pass-Through Queries Programatically 1

[gavinjb]

I need to be able to create a query in my VBA Code and specify sepcfic conditions, due to the complexity of the query and the data been stored on a Progress Database, the best way for me to do this is using a Pass-Through Query, does anyone know how to create one in code and create it with a ODBC connection string.

The connection string is:
ODBC;DSN=LSQL;HOST=FLEETSYS;PORT=LSQL;DB=FLEET;UID=SQLUSER;PWD=hampton

Regards,


Gavin,

 

[scriverb]

I think this should work for you. This code is using DAO declarations so make sure you MS 3.6 DAO Object Library is declared if using A2000:

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb

Set qd = db.CreateQueryDef("qryNewQuery")
db.QueryDefs("qryNewQuery").Connect = "ODBC;DSN=LSQL;HOST=FLEETSYS;PORT=LSQL;DB=FLEET;UID=SQLUSER;PWD=hampton"
db.QueryDefs("qryNewQuery").SQL = "[blue]SELECT " & _
    "[red]tblYourTableName[/red].Field1, [red]tblYourTableName[/red].Field2 . . . " & _
    "FROM [red]tblYourTableName[/red] " & _
    "WHERE .. . .;[/blue]"
db.Close
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryNewQuery"
DoCmd.SetWarnings True

Now this is just an example. The blue code with red highlights is just some dummy code. This is where you place your SQL code that will run on your SQL server. By setting the query property CONNECT with the ODBC connection information the query becomes a PASS-THROUGH query. After you update the .SQL property the query is now ready to be executed. You can after this update the CONNECTION and SQL properties in a similar manner to make modifications as needed depending upon your database parameters.

Post back if you have any questions.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[gavinjb]

Thanks for the help, I have made a few changes to be able to pass the queryname and SQL string to the procedure. Thanks, Gavin,