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
Share the Post:
Share on facebook
Share on twitter
Share on linkedin


The Latest

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a

chrome os developer mode

How to Turn on Chrome OS Developer Mode

Google’s Chrome OS is a popular operating system that is widely used on Chromebooks and other devices. While it is designed to be simple and user-friendly, there are times when users may want to access additional features and functionality. One way to do this is by turning on Chrome OS

homes in the real estate industry

Exploring the Latest Tech Trends Impacting the Real Estate Industry

The real estate industry is changing thanks to the newest technological advancements. These new developments — from blockchain and AI to virtual reality and 3D printing — are poised to change how we buy and sell homes. Real estate brokers, buyers, sellers, wholesale real estate professionals, fix and flippers, and beyond may