Using an Access Database

I thought i would add this test code for any Microsoft Access users wanting to use Bruceys module.

SuperStrict

Framework BaH.DBODBC
' Don't mind all the debuglog statements. It took me ages to get this working properly so i have left them in for any other people
' I created a blank MS Access database on my C: drive called Glenn.mdb
' The DSN name points to this database
'                                             database                          dsn name
Local db:TDBConnection = LoadDatabase("ODBC", "Glenn", Null, Null, Null, Null, "GlennDSN")

If Not db Then
	DebugLog("Didn't work...")
	End
End If

If db.hasError() Then
	errorAndClose(db)
End If
DebugLog( "Database Loaded")

If db.isOpen() Then
	DebugLog ("Database is Opened")
	' we don't care if the drop table fails, since it might not exist yet...
	db.executeQuery("DROP TABLE person")
	DebugLog ("Table Dropped")
	Local s:String = "CREATE TABLE person (id counter PRIMARY KEY UNIQUE NOT NULL , forename varchar(30), surname varchar(30))"
	DebugLog ("s = " + s)
	db.executeQuery(s)
	DebugLog ("Tried to Execute Query")

	If db.hasError() Then
		errorAndClose(db)
	End If
	DebugLog ("No Error creating the table")

	Local names:String[][] = [ ..
	[ "Alfred", "Aho" ],   ..
	[ "Brian", "Kernighan" ], ..
	[ "Peter", "Weinberger" ] ]

	For Local i:Int = 0 Until names.length
		Local s:String = "INSERT INTO person ( forename, surname ) VALUES ('brucey','bonus')"
		DebugLog ("s = " + s)
		db.executeQuery(s)
		If db.hasError() Then
			errorAndClose(db)
		End If
	Next
	DebugLog ("First Insert SUCCEEDED")
	Local query:TDatabaseQuery = db.executeQuery("SELECT * from person")
	If db.hasError() Then
		errorAndClose(db)
	End If

	DebugLog ("Now read the records")
	While query.nextRow()
		Local record:TQueryRecord = query.rowRecord()
		DebugLog("Name = " + TDBString(record.value(1)).value + " " + TDBString(record.value(2)).value)
	Wend

' Now use the INSERT command to add some new values
	For Local i:Int = 0 Until names.length
		Local s:String = "INSERT INTO person ( forename, surname ) VALUES ('" + names[i][0] + "', '" + names[i][1] + "')"
		DebugLog ("s = " + s)
		db.executeQuery(s)
		If db.hasError() Then
			errorAndClose(db)
		End If
	Next
	DebugLog ("Second Insert SUCCEEDED")
	Local query1:TDatabaseQuery = db.executeQuery("SELECT * from person")
	If db.hasError() Then
		errorAndClose(db)
	End If

	DebugLog ("Now read the records")
	While query1.nextRow()
		Local record:TQueryRecord = query1.rowRecord()
		DebugLog("Name = " + TDBString(record.value(1)).value + " " + TDBString(record.value(2)).value)
	Wend
	
	DebugLog ("and finally CLOSE the database")
	db.close()
	
End If

Function errorAndClose(db:TDBConnection)
	DebugLog(db.error().toString())
	db.close()
	End
End Function

and here is the final output:

Building Bruceys-MS-Access-Module_Test
Compiling:Bruceys-MS-Access-Module_Test.bmx
flat assembler version 1.66
3 passes, 11118 bytes.
Linking:Bruceys-MS-Access-Module_Test.debug.exe
Executing:Bruceys-MS-Access-Module_Test.debug.exe
DebugLog:Database Loaded
DebugLog:Database is Opened
DebugLog:Table Dropped
DebugLog:s = CREATE TABLE person (id counter PRIMARY KEY UNIQUE NOT NULL , forename varchar(30), surname varchar(30))
DebugLog:Tried to Execute Query
DebugLog:No Error creating the table
DebugLog:s = INSERT INTO person ( forename, surname ) VALUES ('brucey','bonus')
DebugLog:s = INSERT INTO person ( forename, surname ) VALUES ('brucey','bonus')
DebugLog:s = INSERT INTO person ( forename, surname ) VALUES ('brucey','bonus')
DebugLog:First Insert SUCCEEDED
DebugLog:Now read the records
DebugLog:Name = brucey bonus
DebugLog:Name = brucey bonus
DebugLog:Name = brucey bonus
DebugLog:s = INSERT INTO person ( forename, surname ) VALUES ('Alfred', 'Aho')
DebugLog:s = INSERT INTO person ( forename, surname ) VALUES ('Brian', 'Kernighan')
DebugLog:s = INSERT INTO person ( forename, surname ) VALUES ('Peter', 'Weinberger')
DebugLog:Second Insert SUCCEEDED
DebugLog:Now read the records
DebugLog:Name = brucey bonus
DebugLog:Name = brucey bonus
DebugLog:Name = brucey bonus
DebugLog:Name = Alfred Aho
DebugLog:Name = Brian Kernighan
DebugLog:Name = Peter Weinberger
DebugLog:and finally CLOSE the database

Process complete


Cheers
Glenn