Building a query in code

[Kenln]

I have building quieries in code such as:

Dim strMySql

    strMySql = "Select blah, blah from whereever;"
    DoCmd.RunSQL strMySql

But, now I would like to create a pass-through query. I am doing this because I need to link a pass-through table on a server to a backend table in order to do an update/set.

Does anyone know how I might do this?

Thank you,

 

[ashishprem]

Try out something like this

Dim dbs As Database
Dim rs As Recordset
Dim qdf As QueryDef
Dim productName As String
Dim strSql As String

Set dbs = CurrentDb()

productName = "ADSL"

strSql = "SELECT [Information].* From [Information] WHERE [Information].Product = " & Chr(34) & productName & Chr(34) & ";"

Set rs = dbs.OpenRecordset(strSql, dbOpenSnapshot)

With dbs
Set qdf = .CreateQueryDef("tmpProductInfo", strSql)
DoCmd.OpenQuery "tmpProductInfo"
.QueryDefs.Delete "tmpProductInfo"
End With
dbs.Close
qdf.Close

 

[Kenln]

Just for reference...
I found it, I should have done more research. Sorry...

http://support.microsoft.com/kb/q124391/

This works almost perfect. The only problem I have found so far is that I cannot tell when/if the record(s) is/are locked.

At this point I will have to run the query, then another to test the data.

But other than that, this seems to work fine for update queries.