Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

ODBC Issues

Posted on 2002-03-29
12
Medium Priority
?
527 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +2
12 Comments
 
LVL 3

Expert Comment

by:Bob Scriver
ID: 6905262
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
ID: 6905973
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
ID: 6906725
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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 2

Expert Comment

by:Syed Irtaza Ali
ID: 6908607
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
ID: 6910043
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
ID: 6910060
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
 

Author Comment

by:wellsj
ID: 6910116
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
ID: 6911974
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
ID: 6915154
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 400 total points
ID: 6915204
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
ID: 6915275
aaronkempf thanks for the QBE tip.

Nomi.
0
 

Author Comment

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

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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 …

596 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