Solved

Microsoft Access ODBC Linked Tables SQL Server Connection Error

Posted on 2014-10-02
4
27 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
  • 2
4 Comments
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
How will I know if the user has access?  Also, there are a lot of tables.
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
Comment Utility
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 26

Expert Comment

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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 …

771 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

11 Experts available now in Live!

Get 1:1 Help Now