Run Pass through Query with VBA?

[b1rans]

Hello Everyone,

I am trying to run a pass through query from access VBA.

Private Sub Command25_Click()
Dim dbs As dao.Database
Dim qdf As dao.QueryDef
Dim strsql As String
Const c_strSQL As String = "Call updateihdinfo({p1}, {p2})"

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("query1")
strsql = Replace(c_strSQL, "{p1}", [Forms]![IHDform]![Combo5])
strsql = Replace(strsql, "{p2}", [Forms]![IHDform]![Text15])
qdf.SQL = strsql
Set qdf = Nothing
Set dbs = Nothing

DoCmd.OpenQuery "query1", acViewNormal, acEdit

End Sub


Query1 is my Sql Pass through query. Call Updateihdinfo(p1, p2)



I am able to run "query1" successfully by itself without any errors.
but when I try and run the VBA code above I get "Run-time error '3270': Property not found.

If I change "acviewnormal" to "acviewdesign" the error goes away but the query opens in design view and does not run.

Any Help would be greatly appreciated.

Thanks

 

[mdlueck]

Try closing your QueryDef object to see if it is not getting saved since it is not being closed.

Set qdf = dbs.QueryDefs("query1")
strsql = Replace(c_strSQL, "{p1}", [Forms]![IHDform]![Combo5])
strsql = Replace(strsql, "{p2}", [Forms]![IHDform]![Text15])
qdf.SQL = strsql
[B][COLOR=Blue]qdf.Close[/COLOR][/B]
Set qdf = Nothing

I use DAO.QueryDef objects. Here is sample code of how I am using them:

Example of DAO.QueryDef objects downloading records from a SQL BE DB via Pass-Through query and populating a FE temp table with them
http://www.access-programmers.co.uk/forums/showthread.php?p=1119605#post1119605