?
Solved

Microsoft Access ODBC Linked Tables SQL Server Connection Error

Posted on 2014-10-02
4
Medium Priority
?
41 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 38

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 38

Accepted Solution

by:
PatHartman earned 2000 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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

762 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