Solved

ODBC Issues

Posted on 2002-03-29
12
483 Views
Last Modified: 2007-12-19
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.
0
Comment
Question by:wellsj
  • 4
  • 3
  • 3
  • +2
12 Comments
 
LVL 3

Expert Comment

by:Bob Scriver
Comment Utility
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

0
 
LVL 1

Expert Comment

by:aaronkempf
Comment Utility
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.
Aaron
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.

0
 
LVL 1

Expert Comment

by:msmouse
Comment Utility
wellsj,

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.  

Mike
0
 
LVL 2

Expert Comment

by:Syed Irtaza Ali
Comment Utility
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;"
Else
    'Server
    If Nz(strSERVER, "") = "" Then
        MsgBox "Please enter the Server name or provide an existing DSN.", vbInformation, "Nomi"
        f!ctrl_Server.SetFocus
        Exit Function
    End If
        'Driver
        strConnect = "driver={sql server};"
        strConnect = strConnect & "server=" & strSERVER & ";"
End If
   
    'Database
    If Nz(strDB, "") = "" Then
        MsgBox "Please enter the Database name.", vbInformation, "Nomi"
        f!ctrl_DB.SetFocus
        Exit Function
    End If
        strConnect = strConnect & "database=" & strDB & ";"
    'UserID
    If Nz(strUID, "") = "" Then
        MsgBox "Please enter the username.", vbInformation, "Nomi"
        f!ctrl_UserID.SetFocus
        Exit Function
    End If
        strConnect = strConnect & "uid=" & strUID & ";"
    'Password
    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
conn.Close
DoCmd.Beep
Forms!dlg_sqlserver!lblConnect.Caption = "Datastore connection tested successfully."
fnc_ODBC_TestConnect = True
Exit Function

Err_Control:
    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"
        DoCmd.Beep
        Forms!dlg_sqlserver!lblConnect.Caption = "Datastore connection test failed."
    Else
        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))
            Else
                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
            tdLocal.RefreshLink
            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..."
            Else
                f!lbl4.Caption = "99% completed..."
                f!axbar.Value = 99
            End If
            UnProcessed.Remove (x)
            Application.Echo False
       End If
    Next
    Application.Echo True
            f!axbar.Value = 100
            f!lbl4.Caption = "100% completed."
   
Exit Function

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

-----------------------------------------------------


If you have any problems plz do mention.


Nomi.



0
 

Author Comment

by:wellsj
Comment Utility
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.
0
 
LVL 2

Expert Comment

by:Syed Irtaza Ali
Comment Utility
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.

Nomi.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:wellsj
Comment Utility
nomehero,
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.  
0
 
LVL 1

Expert Comment

by:aaronkempf
Comment Utility
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'??



0
 
LVL 2

Expert Comment

by:Syed Irtaza Ali
Comment Utility
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.

Nomi.


0
 
LVL 1

Accepted Solution

by:
aaronkempf earned 200 total points
Comment Utility
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..

0
 
LVL 2

Expert Comment

by:Syed Irtaza Ali
Comment Utility
aaronkempf thanks for the QBE tip.

Nomi.
0
 

Author Comment

by:wellsj
Comment Utility
Not really what I had in mind but it did work.
0

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…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

763 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

7 Experts available now in Live!

Get 1:1 Help Now