Solved

Microsoft Access ODBC Linked Tables SQL Server Connection Error

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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 …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

724 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