Access SQL Passthrough Query

Posted on 2009-02-17
Medium Priority
Last Modified: 2013-11-28
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.


Question by:rowansmith
  • 2
  • 2

Expert Comment

by:Paul Cook-Giles
ID: 23665617
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.


Assisted Solution

by:Paul Cook-Giles
Paul Cook-Giles earned 2000 total points
ID: 23665621
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.

LVL 11

Author Comment

ID: 23665790
Yes, that is not there in 2007.  It just lets me type a description about the query....
LVL 11

Accepted Solution

rowansmith earned 0 total points
ID: 23665807
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.


LVL 18

Expert Comment

ID: 23667361
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


Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

807 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