Solved

Microsoft Access ODBC Linked Tables SQL Server Connection Error

Posted on 2014-10-02
4
34 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 35

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 35

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

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)

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

789 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