pass through query not working correctly via VBA in Access
Posted on 2008-10-01
I am trying to set a passthrough query to run via VBA the code grabs the product ID and puts that in the SQL for the passthrough it them is suppose to run a append query that runs off of the pass though to append the records into a table in access. If I set this up in access it runs fine, but when I try to run it via the VBA code I get an error when I get to the append query "0100 get samples"
The error says Cannot find file c:\Documents and settings\amcfall\My Documents\siebel.mbd.
Something happens to the passthrough when the code exectues, the SQL looks ok but the connection part doesn't, I first have to reset the query type to passthrough and then reset the ODBC connection information in the property sheet, so I assume what is happening is that the code is not correct to set up the passthrough in the VBA...
Do Until rstTest.EOF
sProdId = rstTest!ROW_ID
Set crsePassThru = CurrentDb.CreateQueryDef("qry_samples")
.ReturnsRecords = True
sSQL = "select sa.pr_prdint_id, sa.target_per_id, sa.target_ou_id, co.email_addr" _
& " from SIEBEL.s_evt_act sa," _
& " SIEBEL.s_contact co" _
& " where sa.target_per_id = co.row_id" _
& " and pr_prdint_id = '" & sProdId & "'"
.SQL = sSQL
DoCmd.OpenQuery "0100 get samples", acViewNormal, acEdit