I have a 10-year old VB6 front-end application for an Access donation management database for a non-profit I’m involved with. The original code is no longer available. There are many problems with the app and the database design. Long term, I want to move the database to PostgreSql or, perhaps, SQLite and revamp the front-end to something more flexible and more modern looking. I have decided to use Xojo.
My first step will be to replace the VB6 front-end with a Xojo front-end for the current Access database using the ODBC driver. To get some experience with Xojo and the database, I wrote an app for database lookup (by ID, name, or address) which is lacking in the VB6 app. It appears to work fine at first, but I eventually get an ODBC error that the driver cannot open any more tables:
The Xojo app is structured with a Data Module which has the connect method and several data access methods.
The driveConnect method is called by the App open event:
driveDB = new ODBCDatabase
// check if drive database file exists
Dim f1 as FolderItem
f1=GetFolderItem(Preferences.DriveLocation)
If not f1.exists then
// file not found select new location
Dim f2 As FolderItem
f2 = GetOpenFolderItem("")
If f2 <> Nil Then
// update WEFAS Drive database location
Preferences.DriveLocation = f2.NativePath
Else
//User Cancelled - exit
Quit
End If
end If
dim fileloc as String = GetFolderItem(Preferences.DriveLocation).NativePath
dim connstring as String
connstring = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};" + _
"Dbq=" + fileloc + ";"
driveDB.DataSource = connstring
if not driveDB.Connect then
// db error
MsgBox("Unable to open database. Error: " + driveDB.ErrorMessage)
Quit
end
Return True
A typical data access method (GetMasterByID) is as follows:
dim rs as RecordSet
dim sql as string = "SELECT * FROM Master WHERE Master.MasterID = ?"
dim p1 As ODBCPreparedStatement = driveDB.Prepare(sql)
p1.BindType(0, ODBCPreparedStatement.ODBC_TYPE_INTEGER)
p1.Bind(0, luID)
rs = p1.SQLSelect
if driveDB.Error then
MsgBox("Error getting Master Record. Error: " + str(driveDB.ErrorCode) + " - " + driveDB.ErrorMessage)
end
return rs
A typical method that calls one of the data access methods (some extraneous portions removed) is as follows:
dim rsMaster as RecordSet
rsMaster = data.GetMasterByID(CLong(self.txtID.Text) )
if rsMaster = nil then
MsgBox("nothing returned")
return
end
if rsMaster.EOF and rsMaster.BOF then
// nothing returned
MsgBox("No record found for ID = " + self.txtID.Text)
rsMaster.Close
return
end
// assemble name
dim line1 as string
line1 = AssembleName(rsMaster.Field("Title").StringValue, _
rsMaster.Field("FirstName").StringValue, _
rsMaster.Field("MI").StringValue , _
rsMaster.Field("LastName").StringValue)
'// assemble address
dim line2, line3 as String
AssembleAddress(line2, line3, rsMaster)
txtNameAddress.text = line1 + EndOfLine + line2 + EndOfLine + line3
txtNameAddress.SelectAll
rsMaster.Close
This is a Windows app compiled for a 32-bit executable since Access and the ODBC driver are 32-bit apps. I would appreciate any comments on the structure of the app, where I may be leaving something open, or suggestions for troubleshooting.
Thanks,
Dennis