asked on

Access 2K / Pass Trought Query?

I am using MS Access 2000 with backend SQL Server 2000.
I created a passthrough query that basically just executes a stored procedure on the SQL Server. But each time I run the query it brings to the dialogue box to select the data source. How can I have this info saved?

jmoss111

you'll need to fill the connect string parameter in the pass through querydef

ASKER CERTIFIED SOLUTION

answer_dude

membership

This solution is only available to members.

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

Start Free Trial

jmoss111

the code below will grab the username and use that to build the connectstring

Private Declare Function GetUserName Lib "ADVAPI32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Public Const strConnect1 = "ODBC;DRIVER={SQL SERVER};SERVER=999.999.999.999\SQLEXPRESS;DATABASE=MyDb;UID="

Public Const strConnect2 = ";PWD=mypw;"

Public Function GetCurrentUserName() As String
On Error GoTo Err_GetCurrentUserName
 Dim lpBuff As String * 25
 Dim ret As Long, Username As String
   ret = GetUserName(lpBuff, 25)
   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 gUserNm AS String
Public Function GetCurrentUserName() As String
On Error GoTo Err_GetCurrentUserName
 dim db as dao.database
 set db = currentdb
 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

db.QueryDefs("qptMyPassthru").Connect = gstrConnect1 & gUsrNm & strConnect2
Exit_GetCurrentUserName:
    Exit Function

Err_GetCurrentUserName:
        MsgBox Err.Description
        Resume Exit_GetCurrentUserName
End Function

Open in new window

jmoss111

if you need help with a connect string or you're using windows authentication instead of mixed mode you can reference http://www.connectionstrings.com/