I use a housekeeping routine to check the state of tables during system initialization. The routine tests whether the table is in a collection, whether the table has data, and whether the table is local or attached. The routine helps to establish the state of the application at startup.For example, a table name that appears in the Tables collection can be either attached or local. If it is attached and the second MDB where it resides is not present, this state is only discovered when the application first attempts to access it. Usually, an error condition occurs in the middle of application logic. I prefer to test for this condition at the beginning as opposed to being surprised later on. I perform a trial read on the attached table:
Public MyDB As DatabaseDim Dummy As IntegerSet MyDB = OpenDatabase("testjet3db")'Sample callDummy = CheckTable("NameTable", "readarecord", "local")'More sophisticated call to trial read an attached tableIf Not CheckTable("AnotherTable", _ "readarecord", "attached") Then 'we've got trouble in River CityEnd IfFunction CheckTable(ByVal whichTable As String, _ ByVal whichTest As String, _ ByVal whichAttach As String) As IntegerDim ErrorStage As StringDim aTable As Recordset' This is a comprehensive table checker' which tests for:' .is the table in the collection' .is it attached or local as the case may be' NOTE: have found subtle differences in ' how DAO Find FindNext' commands behave wrt attached and ' local tables' message boxes are useful in ' debugging, can be removedOn Error GoTo CheckTableError 'Set Default return condition CheckTable = True ErrorStage = " Test for table in collection " If Not CheckIfTableInCollection(whichTable) Then CheckTable = False MsgBox ErrorStage & whichTable _ & " failed in CheckTable" Exit Function End If ErrorStage = "SetTable" Set aTable = MyDB.OpenRecordset(whichTable) ErrorStage = "Test read a record " If whichTest = "readarecord" Then aTable.MoveFirst End If ErrorStage = "Test attachment status " Select Case whichAttach Case "attached" If InStr(MyDB.TableDefs(whichTable).Connect, _ "DATABASE=") = 0 Then CheckTable = False MsgBox ErrorStage & whichTable & _ " failed " & whichAttach & _ " in CheckTable" End If Case "local" If MyDB.TableDefs_ (whichTable).Connect <> "" Then CheckTable = False MsgBox ErrorStage & whichTable & _ " failed " & whichAttach & _ " in CheckTable" End If Case "dontcare", "" End SelectCheckTableErrorExit: If ErrorStage <> "SetTable" Then aTable.Close End If Set aTable = Nothing Exit FunctionCheckTableError: CheckTable = False MsgBox Error$ & " ErrorTrap " & _ ErrorStage & " " & whichTable _ & " failed in CheckTable" Resume CheckTableErrorExitEnd FunctionFunction CheckIfTableInCollection_ (ByVal TableName As String) _ As Integer' This function checks that the tables ' collection has a table' it looks for tables by name and ' returns true/false settingDim i% CheckIfTableInCollection = False For i% = MyDB.TableDefs.Count - 1 _ To 0 Step -1 DoEvents If TableName = Trim$(MyDB._ TableDefs(i%).Name) Then CheckIfTableInCollection = True End If Next i%End Function