By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,843 Members | 1,566 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,843 IT Pros & Developers. It's quick & easy.

Checking linked tables on startup

P: 200
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.
Mar 15 '09 #1
Share this Question
Share on Google+
6 Replies

Expert 5K+
P: 8,675
I wrote this little code snippet for you. Hopefully, it should point you in the right direction. Any questions, please feel free to ask.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdTestLink_Click()
  2. On Error GoTo Err_cmdTestLink_Click
  3. Const conLINKED_TABLE As String = "tblDepartment"
  5. 'A Linked Table will have a Connect Strain whose Length is > 0
  6. If Len(CurrentDb.TableDefs(conLINKED_TABLE).Connect) > 0 Then
  7.   'OK, we know that conLINKED_TABLE is a Linked Table, but is the Link valid?
  8.   'The next line of code will generate Errors 3011 or 3024 if it isn't
  9.   CurrentDb.TableDefs(conLINKED_TABLE).RefreshLink
  10.   'If you get to this point, you have a valid, Linked Table
  11.   '...normal code processing here
  12. Else
  13.   'An Internal Table will have a Connect String Length of 0
  14.   MsgBox "[" & conLINKED_TABLE & "] is a Non-Linked Table", vbInformation, "Internal Table"
  15. End If
  17. Exit_cmdTestLink_Click:
  18.   Exit Sub
  20. Err_cmdTestLink_Click:
  21.   Select Case Err.Number
  22.     Case 3265
  23.       MsgBox "[" & conLINKED_TABLE & "] does not exist as either an Internal or Linked Table", _
  24.              vbCritical, "Table Missing"
  25.     Case 3011, 3024     'Linked Table does not exist or DB Path not valid
  26.       MsgBox "[" & conLINKED_TABLE & "] is not a valid, Linked Table", vbCritical, "Link Not Valid"
  27.     Case Else
  28.       MsgBox Err.Description & Err.Number, vbExclamation, "Error in cmdTestLink_Click()"
  29.   End Select
  30.     Resume Exit_cmdTestLink_Click
  31. End Sub
Mar 15 '09 #2

P: 200
Spot on! Thanks ADezii.

Mar 15 '09 #3

P: 200
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.
Mar 15 '09 #4

Expert 5K+
P: 8,675
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?
Mar 15 '09 #5

P: 200
Don't think so...where would I find the auxiliary code you are referring to?
Mar 15 '09 #6

Expert 5K+
P: 8,675
Not importatnt, tuxalot, just getting side-tracked.
Mar 16 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.