ODBC Issues

I have an application that connects to a SQLServer database.  When I test everything works fine.  Forms open, data is entered, reports are made.  When I create a mde from this and I go put it on a users PC and set up the correct ODBC connection, etc.  When the users open the application they get an error on the first button they push to access a "employee" table.

Now if I go to the database window and open the table then close it the button works fine.  It's as if the connection to the tables are not "live" until we manually open them first.

Any suggestions on how I can make the tables "live" ALL the time not just when you open them manually first.
Who is Participating?
just for the record, you can see that info without going into mSysObjects--

if you open the linked table in design view; and choose Properties from the top of the QBE window, you can read the connection string for that table.

i hope that helps and ill shutup from here on out, I promise..

Bob ScriverCommented:
When you open them manually are you being prompted for a UserID and Password that you enter?  Usually that is the case.  You normally  have to create your connect string in code and include the UserID and Password parameters for the connection to be live.  By connecting manually one time you have satisfied the security requirements and then the connections work for you.

Bob Scriver

If its access 2000+ you should throw away everything you've done and remake it in an Access Data Project.

They rule.

They hide all the hassles.  The SQL Server is _a_ton_ faster with an Access Data Project. You can truly do some _crazy_ things.

Lemme know if you need help.
425 641 4354

I do travel; and i work cheap.  I have spent the past 2 years FT developing Access Data Projects.

Now I am unemployed and absoulutely _dying_ to get back to work.

PS - if you want the simple answer-- you need to edit the ODBC connection string, and add a PWD='yourpasswordhere' clause.

Id right click in windows, make new text file.  Change the extension to .UDL

Double click and this will build the connnection string for you.

PS - I assume that you are using SQL Passthrough Queires; and not Linked ODBC tables.

SPT Queries do all of the processing on the server, whereas linked ODBC goes through Jet.

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.


When you create the ODBC you have a choice for verifing ID.
Not sure which one you are making, I always use the NT authentication that way I can make a user group and add all the users that need access to the database.  You can then set up the group on SQL server and give what rights the group needs.  If you go with SQL Server authentication go with the way scriverb says.  

Syed Irtaza AliLead Software ArchitectCommented:
thats bogus aaronkempf!

I have a project worth 200 forms, 100 tables , >100 modules, >100 Reports.

I'll be mad if I throw it away.

wellsj here is a way to check whether you have a valid connection and that your connect string will have the password in it.

''Write this  code for a form named dlg_SQLServer which
''asks the user the DSN or Server, DB, UID and PWD

Function fnc_ODBC_TestConnect(ByRef strSQL As String) As Boolean
On Error GoTo Err_Control

Dim conn As New Connection
Dim strConnect
Dim f As Form
Set f = Forms!dlg_sqlserver.Form
Dim strDSN As String, strUID As String, strPWD As String, strDB As String, strSERVER As String

strDSN = Nz(f!ctrl_DSN, "")
strUID = Nz(f!ctrl_UserID, "")
strPWD = Nz(f!ctrl_Pwd, "")
strDB = Nz(f!ctrl_DB, "")
strSERVER = Nz(f!ctrl_Server, "")

'Data Source Name
If Nz(strDSN, "") <> "" Then
    'strConnect = "ODBC;"
    strConnect = strConnect & "DSN=" & strDSN & ";APP=Microsoft. Access;"
    If Nz(strSERVER, "") = "" Then
        MsgBox "Please enter the Server name or provide an existing DSN.", vbInformation, "Nomi"
        Exit Function
    End If
        strConnect = "driver={sql server};"
        strConnect = strConnect & "server=" & strSERVER & ";"
End If
    If Nz(strDB, "") = "" Then
        MsgBox "Please enter the Database name.", vbInformation, "Nomi"
        Exit Function
    End If
        strConnect = strConnect & "database=" & strDB & ";"
    If Nz(strUID, "") = "" Then
        MsgBox "Please enter the username.", vbInformation, "Nomi"
        Exit Function
    End If
        strConnect = strConnect & "uid=" & strUID & ";"
    If Nz(strPWD, "") <> "" Then
        strConnect = strConnect & "pwd=" & strPWD & ";"
    End If

Forms!dlg_sqlserver!lblConnect.Caption = "Testing Datastore connection....."
conn.Open strConnect
If Not IsMissing(strSQL) Then
    strSQL = conn.ConnectionString
End If
Forms!dlg_sqlserver!lblConnect.Caption = "Datastore connection tested successfully."
fnc_ODBC_TestConnect = True
Exit Function

    If conn.State = 0 Then
        MsgBox "Connection failed. " & vbCrLf & "Please check the information provided for the following:" & vbCrLf & _
               "Data source name," & Chr(13) & "UserId - Password," & Chr(13) & "Database.", vbExclamation, "Server Connection"
        Forms!dlg_sqlserver!lblConnect.Caption = "Datastore connection test failed."
        MsgBox err.Description, vbInformation, "Server Connection"
    End If
End Function

''This one is for linking and refreshing
''the SQLServer linked tables
''also store the UID and PWD in global variables
''such as gODBC_UID and gODBC_PWD

Function fnc_ODBC_LinkTables(strSQL As String)
On Error GoTo Err_Control
Dim f As Form

Set f = Forms!dlg_msgbox.Form

f!lbl1.Caption = "Linking the Client to Server. "
f!lbl3.Caption = "Please wait as this would take a few seconds....."

'Linking of Server Tables Start from here.....
  Dim maxTbl As Integer
  Dim avgVal As Double
  Dim tot As Double
On Error GoTo Err_Control
  'Loop through all the tables
  maxTbl = UnProcessed.Count
  avgVal = CDbl(100) / CDbl(maxTbl)
  f!lbl4.Caption = "0% completed...": f!lbl4.Visible = True
  f!axbar.Value = 0
  f!axbar.Visible = True
Dim dbLocal As Database
Dim tdLocal As TableDef
Dim x

Set dbLocal = CurrentDb

f!lbl1.Caption = "Linking the Client Database with the Datastore. "
f!lbl3.Caption = "Please wait as this would take a few seconds....."
Application.Echo False

    For Each x In UnProcessed
      If Len(dbLocal.TableDefs(x).Connect) > 0 Then
        Set tdLocal = dbLocal.TableDefs(x)
            Dim myDSN As String
            Dim myUser As String
            If InStr(1, strSQL, "DSN", vbTextCompare) Then
                myDSN = Mid(strSQL, InStr(1, strSQL, "DSN=", vbTextCompare), Len(strSQL) - InStr(1, strSQL, "DSN=", vbTextCompare))
                myDSN = Mid(strSQL, InStr(1, strSQL, "Driver=", vbTextCompare), Len(strSQL) - InStr(1, strSQL, "Driver=", vbTextCompare))
            End If
            If Nz(gODBC_UID, "") = "" Then gODBC_UID = "sa"
            tdLocal.Connect = myDSN & ";UID=" & gODBC_UID & ";Pwd=" & gODBC_PWD
            Application.Echo True
            If f!axbar.Value < 99 Then
                tot = f!axbar.Value + avgVal
                If tot > 98 Then tot = 99
                f!axbar.Value = tot
                f!lbl4.Caption = Round(f!axbar.Value, 0) & "% completed..."
                f!lbl4.Caption = "99% completed..."
                f!axbar.Value = 99
            End If
            UnProcessed.Remove (x)
            Application.Echo False
       End If
    Application.Echo True
            f!axbar.Value = 100
            f!lbl4.Caption = "100% completed."
Exit Function

        MsgBox err.Description, vbInformation, "Server Link"
        Application.Echo True
End Function


If you have any problems plz do mention.


wellsjAuthor Commented:
Scriverb, I am not prompted for a user ID or Password.  It is using the NT Auth to connect to the database.  I am using a mdw to handle the security on the forms/reports.  All the user has to do is open a table then they have access to it.  Could I set up a macro or something to open then close the tables to establish the connections?  How much slower would this be?  I don't want to do what you suggested b/c the user would have to enter the id and password.  I am trying at all cost to avoid this.
Syed Irtaza AliLead Software ArchitectCommented:
wellsj refresh your links by checking the check box below the links which says "Always prompt for new location" and then connect to the SQL DB you have with checking ON the option "Save Password" at bottom-right.

Hope this solves your issue.

wellsjAuthor Commented:
I can't find the "Always prompt for new location" check box and I have a question on the save password check box.  If I create an mde to distribute will it save my password/id so everyone that logs on will be doing so as me?  I can't imagine this is how is works but what do I know.  
if you're trying to avoid _all_ ODBC & logon problems.. the answer is simple.

throw away what you have.  keep the sql server data.

move _all_ of your data (relevent for this project) into one SQL Server database.

the Access Data Project allows for integrated security without all of the headaches of creating DSNs.

Imagine that--

You could have a database that would be infinitely fast, somewhat secure (c'mon-- do people really use Access MDB Security??), scalable-- and you wouldn't have to do _anything_ to deploy this to a new machine---

you just point the end user to the folder where you store your Access Data Projects-- and voila-- your company doesn't need to hire the extra staff to maintain DSNs.

If you are really deadset on keeping Jet alive (longer than Microsoft will) then I would reccommend dropping all DSNs and all DAO.  Use UDL Connection files; and ADO.

It would be a solution that would last you another 10 years.

Have you ever heard the phrase 'do it nice or do it twice'??

Syed Irtaza AliLead Software ArchitectCommented:
wellsj we are currently testing our product developed in Access 2000. Our QA ONLY tests on MDE.

Now before compacting and reparing the Database all I do is remove all the SQL links and then click of File->GetExternalData->Link Tables...

Now select ODBC Databases in File Types and select your DSN.

Then a Link Tables dialog opens up with the table names on the left side and btn on the right side. Just look below Deselect All btn you will see the Save Password check box. Select the tables to link and CHECK that option btn.

Now all the links will get created with the UID and PWD.
You can verify that from MSysObjects [Hidden table] in the Connect field of the links.

If you have any problems do mention.


Syed Irtaza AliLead Software ArchitectCommented:
aaronkempf thanks for the QBE tip.

wellsjAuthor Commented:
Not really what I had in mind but it did work.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.