Unstable Database

[Larry]

I posted this problem in the queries newsgroup, but didn't solve the problem
there and don't know where else to post this, so I'm trying here.

I think my database may have finally outgrown Access 2003, but I cannot
convince my users to move to something bigger (yet). So, at this time, I need
to try to get the database working, while I continue to try to convince the
user to bite the bullet and pay for the new upgraded application.

I'm hoping I can describe the problems I'm seeing and see if someone can
help me track down the problem (if there is something to track down anyway).

My database has about 45 tables, 118 queries, numerous forms (one with too
many controls really) and some reports and a good deal of code in modules.

Currently, I'm having problems with my queries sometimes working and
sometimes giving me error messages like "Query to complex" or something like
"mismatched number of columns between the queries". I do have a number of
union queries, but sometimes I get this second message on queries that are
not using a union query and has no underlying union query. A lot of my
queries do use other queries as underlying queries, but when I've been
getting the complex message, I have done what I can to directly reference the
table, instead of it's base query. (I typically create a base query for all
tables, then use those base queries in everything else). In this case, I'm
assuming it's hurting me so I am removing some of them where I can.

Almost all of the tables have RFI relationships, so there are a large number
of those (and yes the tables are in a back-end database).

I have tried to run the Performance Analyzer, but it locks up and crashes,
giving me the dialog to repair and reopen my database (I am sick of this
dialog box)!

I have done a compact/repair numerous times, with no luck. I have also tried
creating a new blank database and importing all the objects into the new
database. Most of the attempts lock up and crash before the objects are
imported into the new database. On the occasions it does work, the new
database is just as unstable.

I have read all the KB articles I can find on corrupted databases as well as
a number of websites referring to corrupted databases, but I'm not sure the
database is really "corrupted".

I have also done the following:
- Deleted all data in all tables
- Deleted all forms, reports, macros
- Deleted all Modules except one (that had some functions in use by queries)
- Deleted all unnecessary code in the remaining module
- Removed all references that were added in the code section
- Compacted and reparied the database (front and back end)

Still no help.

Next I combined the remaining tables and queries into one database, instead
of split, but that didn't help.

The last thing I tried was removing ALL relationships from the tables. That
seems to have solved the problem, but who wants a DB with no RFI??? So this
is really no solution.

I actually wrote some code to delete all the relationships and re-recreate
them, to see if there was a courrupted relationship, but once they are back
on, the problem returns.

So I think the problem has to do with the relationships, or maybe indexes on
the tables?

Thanks for any thoughts.

Larry

 

[Wei Lu]

Hi Larry,

Thank you for using MSDN Managed Newsgroup Support.

Based on my scope, there are maximum capacities of the Query in access.

Number of enforced relationships :

32 per table minus the number of indexes that are on the table for fields
or combinations of fields that are not involved in relationships

If you have too much relationships, you will get the "Query too complex"
error when you run the query.

To resolve this issue, you need to re-design the table relationship and try
to reduce the relationship amount.

Here is the Maximum Capacities of the Access:

Microsoft Access 2000 and 2002 Maximum Capacities
http://www.micronetservices.com/Access2kCapacities.htm

If anything unclear, please feel free to let me know.

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Larry]

Thank you for the website, that was very informative.

I wrote a bit of code, to check my counts after looking at that, to count
the number of indexes on a table, plus the number of relationships in which
the table is either the primary or foreign side of a relationship.

None of them have reached the threshold shown on the website. The top 8 are
listed here:
Table Indexes Primary Foreign Total
tblTrip 14 8 5 27
tblInvoice 16 0 7 23
refProfile 16 0 6 22
tblAirTransportation 11 1 5 17
tblVisa 9 1 4 14
tblChanges 9 0 4 13
tblOtherGround 7 1 3 11
tblInCountry 7 0 3 10


Any other suggestions as to what I should check?

 

[Wei Lu]

Hi Larry,

Thanks for the update.

I have researched in our internal knowledge base. Most of the same issue is
caused by reach the limitation of the query maximum capacities.

I suggest you to check the query that get error to see does it reach the
maximum capacities such as the Number of levels of nested queries or Number
of ANDs in a WHERE or HAVING clause .

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Larry]

I am not always having the problem on the same queries though. And it happens
on some queries that do not reference the new table I added, so they work in
the version of the database without the new table, then fail in the version
with the table (even though they don't reference it).

I am going through and trying to use tables in place of sub-queries where
possible. But so far, it's not helping.

The database is pretty small (as far as file size is concerned). If you
think you might spot something by looking at it, I'd be happy to send it to
you. I've been beating my head against this problem for 4 days now and am
about out of ideas.

 

[Wei Lu]

Hi Larry,

Thanks for the update.

Please zip your mdb file and send to me directly. I understand the
information may be sensitive to you, my direct email address is
(e-mail address removed) (Please remove the ONLINE when you send the
email.), you may send the file to me directly and I will keep secure.

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Larry]

Thank you for the email address. I haven't responded because I am still
trying to fix the problem myself by going into every query and removing all
subqueries that I can, to see if it helps.

At this point it seems to be helping some queries, but then when I go
through and try to execute every query, one right after the other, invariably
another one fails. So I continue to try to fix it myself before pushing it
off on you.

I will let you know how it goes.

 

[Larry]

I give up... another full day of testing and I still have problems.

You have mail.