Access SQL Passthrough Query

Posted on 2009-02-17
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
    LVL 4

    Expert Comment

    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.

    LVL 4

    Assisted Solution

    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

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

    Accepted Solution

    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

    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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    730 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

    15 Experts available now in Live!

    Get 1:1 Help Now