Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: Visual Basic
Expertise: Intermediate
Aug 6, 1997



Building the Right Environment to Support AI, Machine Learning and Deep Learning

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 Database
Dim Dummy As Integer
Set MyDB = OpenDatabase("testjet3db")
'Sample call
Dummy = CheckTable("NameTable", "readarecord", "local")

'More sophisticated call to trial read an attached table
If Not CheckTable("AnotherTable", _
	"readarecord", "attached") Then
	'we've got trouble in River City
End If
Function CheckTable(ByVal whichTable As String, _
	ByVal whichTest As String, _
	ByVal whichAttach As String) As Integer
Dim ErrorStage As String
Dim 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 removed
On 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
	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 Select
	If ErrorStage <> "SetTable" Then
	End If
	Set aTable = Nothing
	Exit Function
	CheckTable = False
	MsgBox Error$ & " ErrorTrap " & _
		ErrorStage & "  " & whichTable _
		& " failed in CheckTable"
	Resume CheckTableErrorExit
End Function
Function 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 setting
Dim i%
	CheckIfTableInCollection = False
	For i% = MyDB.TableDefs.Count - 1 _
		To 0 Step -1
		If TableName = Trim$(MyDB._
			TableDefs(i%).Name) Then
			CheckIfTableInCollection = True
		End If
	Next i%
End Function
Stan Mlynek
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date