How many open databases?

[wh00t]

I have currently been given an excessivley large database to optimise.

It frequently encounters 'can not open any more databases' errors, most of which I have changed around, but there are places which I suspect may run into these issues later on.

Is there any way that I can find out how many 'databases' are currently open so that I can put it in certain places of the code to check?

 

[HiTechCoach]

Is this a single file database or is it split into multiple database files?

Make a Backup!

Have you compacted the database file(s) lately? Make a backup first!


What is the fiel size of the datebase(s)?

 

[wh00t]

here's the detail, and yes I know it should be SQL, that work is currently in progress.

Split db FE/BE

both compacted, FE is 120Mb, BE is 150Mb.

The db has 486 tables, 2160 queries, 386 forms, 221 reports, 87 modules.

Access 2003

 

[Rabbie]

here's the detail, and yes I know it should be SQL, that work is currently in progress.

Split db FE/BE

both compacted, FE is 120Mb, BE is 150Mb.

The db has 486 tables, 2160 queries, 386 forms, 221 reports, 87 modules.

Access 2003

Your sizes don't seem to be excessively large. After all Access allows a DB to be 2GB max so you have a fair bit to go.

Does each user have their own copy of the FE on their own computers? If not they should.

 

[wh00t]

yes each user has their own FE.

all I really need to know is how I can tell how many databases are open at any time, from what I understand if you exceed 55 you receive the 'can not open any more databases' error.

 

[DCrake]

If your front end databases have linked tables then how many mdb's are there?

Can't seem to grasp the logic of the error as you open the FE each back end is referenced and I imagine becomes a collection of opened databases. Unless you are dynamically opening other mdb's that are directly linked to the FE I can see how this value could increase. Or is it that I am missing something?

I only ask because I have never received this error before.

David

 

[wh00t]

the error that appears is a little misleading, it does not refer to physical mdb files, here's the easiest way I found someone explain it

error 3048 arises from the fact that Access
allocates table IDs for each table used in a query and there's a maximum of
table IDs that Access can handle simultaneously. Also, more table IDs are
allocated in a split database than in a single-file database. Error 3048 is
triggered whenever there are no more available table IDs.

there are so many things which take up these table id's, tables, queries (table id's for each table referenced in the query), dlookups and other d* stuff, combo/list box sources, code recordsets, form recordsources etc.

Linked tables use 2 table id's instead of 1, so imagine a large form with many combo boxes and list boxes and a number of subforms based on complex union queries tied to linked tables, the limit of these 'open databases' is easy to reach.

 

[wh00t]

think I should explain why I want to count these open databases.

The application runs with no errors, but occasionally when users do things slightly differently it may cause the too many database errors.

The reason I can't just trap the error is due to the error occuring running reports, if a report encounters error 3048, it does not show the error like it would anywhere else, the report will simply close and the code would stop running (one of the special access functions :/).

So I need to record the number of open databases so that if it reaches the limit I can then run another procedure to ensure that the sudden break in the code does not cause the user problems.

 

[DCrake]

Does it store the id's in a MSys table? if so, can you do a count on that.

 

[wh00t]

unfortunatley not, watched the objects in the MSys tables but no values updated or changed. Have also tried DBEngine(0).Databases.Count but that does not return the table id database count.

 

[Pat Hartman]

Are you opening tables/recordsets in code? If so, make sure you close the recordset after using it. Does the user have multiple forms open at once? Try modifying the app to close forms as it opens each new form.

You are working with a huge number of tables and I strongly suspect a seriously unnormalized schema.

 

[DCrake]

Another thought is split the front end into individual modules. Not aware of the nature of the main app but I have wrote very large manufacturiing systems where there were stores, Inspection, Molding, Production, etc sub systems. And a reporting element that encompassed all the management side of thing but did not contain all the unneccessary forms, modules etc.

Also think about splitting modules down into their component parts, only have code in a module that is pertanent to the module. Each time access opens a module it commits the whole code to memory.

David

 

[wh00t]

thanks for all the ideas.

yes the schema is unnormalized, all recordsets are closed and modules are very specific to the component parts.

I am currently working through the database and replacing all d* functions with code that will use a recordset to get the value and then close the recordset to avoid these functions keeping hold of table id's which I think will be the short term solution.

Long term plans are to completley redesign the database, this will take many months to do as it is a very complex database.