devxlogo

Table Checker

Table Checker

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

devx-admin

Share the Post: