MSAccess SQL Pass through Promts for ODBC Connection

Posted on 2007-08-03
Medium Priority
Last Modified: 2013-11-05
My SQL pass though query promts for the ODBC Connection each time it runs.  How do I stop that?
Question by:KimberleyY
  • 5
  • 3
  • 2

Expert Comment

ID: 19625124
It sounds like the reference to the DSN has been lost, try re-creating the query from scratch
LVL 18

Expert Comment

ID: 19625244
What does the connection string in the query look like?

Author Comment

ID: 19625361
jmoss111 -
There is no connection string in the query - from your question I take it I need one!  I am developing this at our office and taking it to a client.  Is there a way to refer to the ODBC Connection by name or do I need to buikdl the whole string?

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.


Author Comment

ID: 19625373
PS I dont really thinl I am a guru - I just want to get through all those STUPID prompts and get to asking my question.

Assisted Solution

Toast_Boy earned 200 total points
ID: 19625386
Go into the query properties and click on the elipsis on the ODBC Connect Str
LVL 18

Expert Comment

ID: 19625518
That type of behaviour occurs when you don't have the ODBC Connect Str populated in the Query Properties. Unless you enter values the default value is ODBC;

If you enter a connect string then you won't be prompted for connection info. What the ODBC Connect Str is looking for is something like :

ODBC;DRIVER={SQL SERVER};SERVER=999.999.999.999\SQLEXPRESS;DATABASE=EmailGlobal;UID=myUserName;PWD=xxxxx;ADDRESS=999.999.999.999:1433;

Where 999.999.999.000 = IP address; you can use the server name instead. The :1433 is the port to use.
LVL 18

Expert Comment

ID: 19625532
Open the query in design view and right click on the query title bar and click prioperties to view the properties window.
LVL 18

Expert Comment

ID: 19625685
What I exampled above was for mixed mode authentication, for windows authentication just substitute the PWD=xxxxx; with Trusted_Connection=Yes;

Either way you will have to build the connect string in code and grab the user name from Windows. Using the DSN-less connection you code it once or somebody has to create it on all users workstations.
LVL 18

Accepted Solution

jmoss111 earned 1800 total points
ID: 19626158
To grab the username and build the connect string in code:


Public Const strConnect2 = ";PWD=nassc;ADDRESS=999.999.999.999:1433;"

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 * 25
 Dim ret As Long, Username As String
   ret = GetUserName(lpBuff, 25)
   Username = Left(lpBuff, InStr(lpBuff, Chr(0)) - 1)
   GetCurrentUserName = Username & ""
    Exit Function

        MsgBox Err.Description
        Resume Exit_GetCurrentUserName
End Function

Public Sub BuildSQLConnectStrings()
Dim db As DAO.Database
Set db = CurrentDb
DoCmd.SetWarnings False
db.QueryDefs("qptGetAPAgingStatistics").Connect = strConnect1 & GetCurrentUserName() & strConnect2
DoCmd.SetWarnings True

End Sub

Author Comment

ID: 19626582
Thanks Toast Boy and jmoss111.

jmoss111 - thanks for the tip and code  on gettting the user name. Into the string That would have been areal gotcha!

Excellent Job

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

862 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