asked on

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.

Thanks

-Rowan

Paul Cook-Giles

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

SOLUTION

Paul Cook-Giles

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rowansmith

ASKER

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

ASKER CERTIFIED SOLUTION

rowansmith

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jmoss111

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

Regards,

Jim

Public Const strConnect1 = "ODBC;DRIVER={SQL SERVER};SERVER=myserver\SQLEXPRESS;DATABASE=APSTAGING;UID="
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_GetCurrentUserName:
    Exit Function
 
Err_GetCurrentUserName:
        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