Solved

pass through query not working correctly via VBA in Access

Posted on 2008-10-01
6
886 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
0
Comment
Question by:aebrehm
  • 4
  • 2
6 Comments
 
LVL 18

Expert Comment

by:jmoss111
Comment Utility
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
0
 

Author Comment

by:aebrehm
Comment Utility
that would be great thanks!!!  
0
 
LVL 18

Accepted Solution

by:
jmoss111 earned 500 total points
Comment Utility
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

0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 18

Expert Comment

by:jmoss111
Comment Utility
You should be able to populate the .sql property
0
 

Author Comment

by:aebrehm
Comment Utility
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
0
 
LVL 18

Expert Comment

by:jmoss111
Comment Utility
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
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now