Checking linked tables on startup

[tuxalot]

I've tested many ways to accomplish this, but none of the code I've found works quite right. I am using the code written by Dev Ashish but it replicates some of the functionality I already have. And, if you change the path to the back end manually and launch the db, Dev's code throws an error.

I think this request is fairly straightforward for someone experienced. I'm sure the code exists for this somewhere but I can't find it. Using Access 2007.

I have a split db setup and also another table that exists as a linked table. I've created forms (Form1, Form2) that allow an end user to change paths to these linked db's and this bit works fine. I have a startup form ready to accept code to check the following two items:

One of my linked back-end tables is called TblDepartment. I would like to check if this linked table exists and is accessible during startup. No need to iterate through all the tables, so I arbitrarily picked TblDepartment. If not-accessible, then open Form#1.

Next, the other stand alone linked mdb (not part of the BE db), has the same field names as one of my local tables and joins my main db via a union query. Again, I would like to check to see that this db is linked properly on startup. If not-accessible, then open Form#2.

As a side note, my db which I inherited from another developer has mixed references to DAO and ADODB. Would it be beneficial to change all code to one or the other?

Thanks for the look.

[ADezii]

@tuxalot
I wrote this little code snippet for you. Hopefully, it should point you in the right direction. Any questions, please feel free to ask.

Private Sub cmdTestLink_Click()
On Error GoTo Err_cmdTestLink_Click
Const conLINKED_TABLE As String = "tblDepartment"
 
'A Linked Table will have a Connect Strain whose Length is > 0
If Len(CurrentDb.TableDefs(conLINKED_TABLE).Connect) > 0 Then
  'OK, we know that conLINKED_TABLE is a Linked Table, but is the Link valid?
  'The next line of code will generate Errors 3011 or 3024 if it isn't
  CurrentDb.TableDefs(conLINKED_TABLE).RefreshLink
  'If you get to this point, you have a valid, Linked Table
  '...normal code processing here
Else
  'An Internal Table will have a Connect String Length of 0
  MsgBox "[" & conLINKED_TABLE & "] is a Non-Linked Table", vbInformation, "Internal Table"
End If
 
Exit_cmdTestLink_Click:
  Exit Sub
 
Err_cmdTestLink_Click:
  Select Case Err.Number
    Case 3265
      MsgBox "[" & conLINKED_TABLE & "] does not exist as either an Internal or Linked Table", _
             vbCritical, "Table Missing"
    Case 3011, 3024     'Linked Table does not exist or DB Path not valid
      MsgBox "[" & conLINKED_TABLE & "] is not a valid, Linked Table", vbCritical, "Link Not Valid"
    Case Else
      MsgBox Err.Description & Err.Number, vbExclamation, "Error in cmdTestLink_Click()"
  End Select
    Resume Exit_cmdTestLink_Click
End Sub

[tuxalot]

Spot on! Thanks ADezii.

Tux

[tuxalot]

Actually if you manually change the path to the linked db and open the app, it throws a 3044 error. I added this to Err_cmdTestLink_Click and it works nicely.

[ADezii]

@tuxalot
Error Number 3044 is 'Application-defined or object-defined error' which hardly seems applicable in this case. Not that it really matters, but is there also some auxiliary code that you have not mentioned?

[tuxalot]

Don't think so...where would I find the auxiliary code you are referring to?

[ADezii]

@tuxalot
Not importatnt, tuxalot, just getting side-tracked.