Access SQL Passthrough Query

When ever  I try to run a SQL Pass Through Query in MSAccess 2007 I have to specify the DataSource each time.

Is their a way to save the ODBC Information with the Query?  So that when the query is run it automatically connects and retreives the data.


LVL 11
Who is Participating?
rowansmithConnect With a Mentor Author Commented:
Ok, you gave me the answer!  In Access 2007 you have to open the query and then select properties from the Query Menu.  This opens a dialog where you can enter these.

Right-Clicking on the Query and selecting Object Properties does not give you these (just a description box) the query has to be open.


Paul Cook-GilesAccess/VBA/SQL Database DeveloperCommented:
Yes;  in the Properties box for the query there's a property labled "ODBC Connect Str";  click in that field and you'll see the Builder button (with the three dots) appear on the right side of the properties box.  Click that, and the ODBC Data Source Admin window will open;  select the ODBC you want to use for the query.

You'll be prompted on whether or not you want to save the password.

Paul Cook-GilesConnect With a Mentor Access/VBA/SQL Database DeveloperCommented:
Oooop;  just saw the '2007' in your question.  I know the answer I gave works up through Access 2003;  I don't know about 2007.

rowansmithAuthor Commented:
Yes, that is not there in 2007.  It just lets me type a description about the query....
I have another way to do this in code and pick up the user name. See attached snippet for code.

Set your connection info in the constants

use your pass through query names to populate the db.querydefs statement in BuildSQLConnectStrings

Call the GetCurrentUserName and BuildSQLConnectStrings functions on initial form load.

Your connect strings are set up for any user


Public Const strConnect2 = ";Trusted_Connection=yes;"
Private Declare Function GetUserName Lib "ADVAPI32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
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 Function
        MsgBox Err.Description
        Resume Exit_GetCurrentUserName
End Function
Public Function BuildSQLConnectStrings()
Dim db As dao.Database
Set db = CurrentDb
DoCmd.SetWarnings False
db.QueryDefs("qptGetICUser").Connect = strConnect1 & gUsrNm & strConnect2
DoCmd.SetWarnings True
End Function

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.