Validating Linked tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have about 50 excel spreadsheets that are linked to my database. These
spreadsheets are monthly reports. They may or may not be there on a monthly
basis. Using VB, how can I check whether my link is valid or not? I want to
leave the link and queries attached to it for those months the reports are
there. I have tried using "null' and "empty" but I get the error that Access
cannot find the table. Or how would I 'trap' for the error so it would simply
skip it and move on to the next report?
 
Currentdb().TableDefs("strLinkedTable").Connect will return the name of the
file that the linked table is linked to.
Something like the following for an excel file
Excel 5.0;HDR=NO;IMEX=2;DATABASE=C:\Documents and Settings\spencer\My
Documents\John Spencer projects list.xls

Use the connect string property to get the connect string and then parse
that to get the path and use dir to check for the existence of the file

UNTESTED AIRCODE follows:

Public Function fIsItThere(strLinkedTableName) as Boolean
Dim StrPath as string

StrPath = CurrentDB().TableDefs(strLinkedTableName).Connect
StrPath = Mid(StrPath,Instr(1,StrPath,"DATABASE=")+8)
fIsItThere = Len( Dir(StrPath)) > 0

End Function


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Thanks to both of you for your help, but I forgot to tell you more. My tables
are actually linked to ONE spreadsheet with multiple tabs. Each tab is a
report. On a monthly basis, how would I check to see if my linked tables are
"linked" to the appropriate tab within the spreadsheet?
 
I have no idea how to do this. I do know that you can use
currentdb().TableDefs("MemberRoster").SourceTableName
to get the name of the sheet (tab) that is expected. However, I don't know
how that can be used to see if the sheet in the XL file actually exists.
I'm sure I could write some VBA that would do this, but I don't have the
time to do so.

Perhaps someone else has code they have already written that will do so.

One option would be to use error handling code and trap the error if the
sheet was not available

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top