aebrehm
asked on
pass through query not working correctly via VBA in Access
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...
With rstTest
Do Until rstTest.EOF
sProdId = rstTest!ROW_ID
CurrentDb.QueryDefs.Delete "qry_samples"
Set crsePassThru = CurrentDb.CreateQueryDef(" qry_sample s")
With crsePassThru
.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
crsePassThru.Close
End With
DoCmd.OpenQuery "0100 get samples", acViewNormal, acEdit
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...
With rstTest
Do Until rstTest.EOF
sProdId = rstTest!ROW_ID
CurrentDb.QueryDefs.Delete
Set crsePassThru = CurrentDb.CreateQueryDef("
With crsePassThru
.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
crsePassThru.Close
End With
DoCmd.OpenQuery "0100 get samples", acViewNormal, acEdit
ASKER
that would be great thanks!!!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You should be able to populate the .sql property
ASKER
ok, sorry I guess I am a little confussed on how and where should I put this in my code...also I am connecting to an ORACLE db....
ODBC;DSN=Siebe_Box;UID=acf all;PWD=am 066;SERVER =SBP;
how would that change the code you have entered..
thanks
ODBC;DSN=Siebe_Box;UID=acf
how would that change the code you have entered..
thanks
I guess that SIEBEL didn't register with me either... You can modify the Constants for your info, but I think that you're going to have to provide a network library reference in the connect string also. But I really don't know.
You can call the functions, etc in a forms OnLoad event if a form opens when your app does. But since you probably aren't using Windows authentication, the GetUser probably won't help you. The connect string can also be put in the onload event. Does that help?
The Oracle DBAs at my clients won't allow me to connect at all due to some high security data. You will find connect string information at http://www.connectionstrings.com/. I know they have an Oracle section, but since I've never had a need, I've never checked it out.
I wish that I could be of more help on this
Jim
You can call the functions, etc in a forms OnLoad event if a form opens when your app does. But since you probably aren't using Windows authentication, the GetUser probably won't help you. The connect string can also be put in the onload event. Does that help?
The Oracle DBAs at my clients won't allow me to connect at all due to some high security data. You will find connect string information at http://www.connectionstrings.com/. I know they have an Oracle section, but since I've never had a need, I've never checked it out.
I wish that I could be of more help on this
Jim
I'll post some code shortly.
Jim