Solved

pass through query not working correctly via VBA in Access

Posted on 2008-10-01

Microsoft Access

1 solution

931 Views

Last Modified: 2013-11-27

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_samples")
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

Comment

Question by:aebrehm

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

6 Comments

LVL 18

Expert Comment

by:jmoss111

ID: 226201672008-10-01

You should build a pass through querydef and just modify the .sql property, save and execute. While you're at it you can build the connect string also.

I'll post some code shortly.

Jim

Author Comment

by:aebrehm

ID: 226201972008-10-01

that would be great thanks!!!

LVL 18

Accepted Solution

by:jmoss111

jmoss111 earned 500 total points

ID: 226204272008-10-01

This covers the connect string, getting user name and populating the .connect property of the querydef.

Jim

'I use constants just to store basic connect string info
' if using trusted rather than mixed mode substitute Trusted_Connection=yes for PWD.
Public Const strConnect1 = "ODBC;DRIVER={SQL SERVER};SERVER=999.999.999.999\SQLEXPRESS;DATABASE=MySQLDb;UID="
Public Const strConnect2 = ";PWD=xxxxx;ADDRESS=999.999.999.999;"
'call to api th grab user name
Private Declare Function GetUserName Lib "ADVAPI32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Public gUserNm as string
 
'get user name, you'll use it in connect string
Public Function GetCurrentUserName() As String
On Error GoTo Err_GetCurrentUserName
 Dim lpBuff As String * 255
 Dim ret As Long, Username As String
   ret = GetUserName(lpBuff, 255)
   Username = Left(lpBuff, InStr(lpBuff, Chr(0)) - 1)
   GetCurrentUserName = Username & ""
   gUsrNm = GetCurrentUserName
Exit_GetCurrentUserName:
    Exit Function
Err_GetCurrentUserName:
        MsgBox Err.Description
        Resume Exit_GetCurrentUserName
End Function
 
'connect string property of pass thru querydef gets updated here
Public Sub SetSQLConnectString()
Dim db As DAO.Database
Set db = CurrentDb
db.QueryDefs("qptGetUpdateDate").Connect = strConnect1 & gUsrNm & strConnect2
End Sub

Open in new window

LVL 18

Expert Comment

by:jmoss111

ID: 226204662008-10-01

You should be able to populate the .sql property

Author Comment

by:aebrehm

ID: 226204762008-10-01

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=acfall;PWD=am066;SERVER=SBP;
how would that change the code you have entered..

thanks

LVL 18

Expert Comment

by:jmoss111

ID: 226206252008-10-01

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

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recovering from an AutoNumber Disaster

Article by: Helen

AutoNumbers should increment automatically, without duplicates. But sometimes something goes wrong, and the next AutoNumber value is a duplicate. This article shows how to recover from this problem.

Microsoft Access

Show pictures directly from URLs in Access forms and reports

Article by: Gustav

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…

Microsoft Access

Creating and Executing a SQL Pass-thru Query

Video by: TechMommy

Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

Access Desktop Databases – A Quick Tour

Video by: Jim

What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.