ERROR [HY001] [Microsoft][ODBC Microsoft Access Driver] Cannot open any more tables

Question

hi,

i'm working on a vb.net project accessing ms access and it keeps on displaying this error message:
ERROR [HY001] [Microsoft][ODBC Microsoft Access Driver] Cannot open any more tables.

any idea on why this is happening? i've read some articles about this on the web and they say it has something to do with the jet 4.0 driver. i'm on xp sp2 and this has the laters version of the ms access odbc driver right?

when running the application, the tables of the said database are also closed in ms access.

thanks in advance,
dave.

Answer 1

Dave Patricio,

 

Based on your post, there is something wrong with Aceess database when you are using ODBC to connect to the Access database in your VB.NET application. I would like to provide you the suggestions as follows:

 

1. Since you are developing VB.NET application with ADO.NET, please consider to use .NET Framework Data Provider for OLE DB instead of ODBC. The following KB article shows you an example on the issue:

 

How to use Microsoft Visual Basic .NET to connect to a Microsoft Access database and to retrieve data

This step-by-step article describes how to use Microsoft ADO.NET to open a Microsoft Access database by using the OLE DB data provider. You use the OleDbDataAdapter class to obtain rows from the Access database and to insert the rows into a DataSet object. This article describes how to add rows, how to delete rows, and how to update rows in the Access database by using DataSet and OleDbDataAdapter.

2. "Cannot open any more tables" error message: I have no idea for the version of your Access database. I would like to provide you the following KB article on the error message:

 

ACC2000: "Microsoft Access Was Unable to Create an MDE Database" Error Message

The Microsoft Jet database engine version 4.0 allows a maximum of 2048 open TableIDs at one time. The process of saving a database as an MDE may cause this number to be exceeded if the database contains a large number of objects.

 

There is no accurate method to estimate the number of TableIDs that the Jet database engine uses during the process of saving a particular database as an MDE. Each module uses one TableID, and saving the database as an MDE involves the Visual Basic for Applications project of the database as well as the project of the MDE. As a result, if your database contains 500 forms, and each form's HasModule property is set to Yes, you may use as many as 1000 TableIDs when saving the database as an MDE.

 

Hope that can help you.

Answer 2

hi bruno,

thanks for the info. i was going over my project a few days back and decided to change the data flow as to avoid the error. i'm using access 2003 by the way. i'll surely try your suggestion and check with the old design to see what happens. thanks again.

dave