Solved

Microsoft Access ODBC Linked Tables SQL Server Connection Error

Posted on 2014-10-02
4
37 Views
Last Modified: 2016-06-26
I have a database where I link tables using ODBC.  Some of the users do not have access to the tables and some do. The ones that do not have access do not use the functions that require these tables.  Out of the blue they are getting a SQL Server connection error whenever they open a form or sometimes executes a macro/vba code and these objects do not use the tables.  Is there a way that I can test whether or not the user has access to tables and if not, bypass the error?
Quote-Template---SQL-Server-Error.jpg
0
Comment
Question by:Marilync1266
[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
  • 2
4 Comments
 
LVL 37

Expert Comment

by:PatHartman
ID: 40358203
Something is referencing the tables.

If you have optional tables, you have to start with them unlinked so delete the links as the last step when the app closes.  Then in the initial form, determine if the user has access to those tables and link them.
0
 

Author Comment

by:Marilync1266
ID: 40358213
How will I know if the user has access?  Also, there are a lot of tables.
0
 
LVL 37

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40358231
I don't know.  It is your application.  Why do some users have access to the tables but others do not?  It sounded like it was intentional.  If a particular user doesn't have access to the database at all, the DBA can help you solve the problem.  If the access comes and goes, it is a network problem and you'll need your desktop support people.  It is possible for you to test the link to a table at startup and if the table isn't found, trap the error and offer to relink but that technique is generally used to find BE's that have moved.  In your case, you won't be able to do anything except trap the error if access to the BE has been interrupted.

I use a startup form that checks the link to one table.  If it is found, the login form opens normally.  If it is not found, the relink form opens.  You'll need to do something else but I don't know what.  At a minimum, I would log the user/computer and date and time of the attempt so you can get a handle on the problem.

Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)

   On Error GoTo Form_Open_Error
    
    If DCount("*", "tblClients") > 0 Then
        DoCmd.OpenForm "frmLogin"
    End If
    

Form_Open_Exit:
    DoCmd.Close acForm, Me.name
   Exit Sub

Form_Open_Error:
    Select Case Err.Number
        Case 3024, 3044, 3043
            MsgBox "Tables have moved.  Please relink.", vbOKOnly
            DoCmd.OpenForm "frmReLinkJetOrACETables", , , , , , Me.name
            Resume Form_Open_Exit
        Case Else
            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_Open of VBA Document Form_ChrckLinks"
    End Select
End Sub

Open in new window

0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 41674832
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Accept: PatHartman (https:#a40358231)

If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

MacroShadow
Experts-Exchange Cleanup Volunteer
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

737 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