ODBC Error - Cannot open any more tables

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

I don’t know how ODBCPreparedStatements work internally and if there is a limit in Access / the Access ODBC driver, but one idea I have would be that the Prepared Statements pile up server-side and are reaching a per-session limit. You should check for errors after db.Prepare, as well. If that is the problem, you could work around by caching the ps-objects in Xojo.
Related cases for PostgreSQL: <https://xojo.com/issue/41633> and <https://xojo.com/issue/37805>

Thanks Tobias,

I made the prepared statements static variables as follows:

Static init As Boolean = False
Static p1 As ODBCPreparedStatement
if Not init Then
  dim sql as string = "SELECT * FROM Master WHERE Master.MasterID = ?"
  p1  = driveDB.Prepare(sql)
  p1.BindType(0, ODBCPreparedStatement.ODBC_TYPE_INTEGER)
  init = True
End

dim rs as RecordSet
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

After some preliminary testing, the problem seems to be resolved.

Thanks again,
Dennis