Reasons for DB (Both FE and BE) to run terribly, freeze and often crash

[stildawn]

Hi All



I am trying to work with an Access database (FE & BE) that I have inherited here at work. I have wiped out all the old records etc to start afresh with just the structure remaining.

However it runs terribly, slow, freezing, unresponding etc all the time. I have compacted and repair etc but its still terrible.

Is there anything I should know to check as to why its like this?

Thanks

[ItsMe]

Perhaps opening the relationships manager and deleting all of the relationships will help.

Also, some tables should probably retain their records to support things like comboboxes, e.g. a table that lists all of the states.

[stildawn]

Yeah I have only emptied the tables that are for data only, not the support tables.

Will clearing the relationships ruin the structure of the database though? Its a large and complex database, I don't really want to spend all week decoding the code so I understand how it all works together.

[ItsMe]

It could. Try it on a copy to see if it makes a difference.

It is hard to say what the issue is.

I would probably have, from the start, imported the supporting tables to a new blank db. Then, import the other tables (structure only). Then import the other objects.


If you deleted a bunch of records, randomly, you may have confused the db. Especially if there are referential integrity rules enforced by the relationships window.

[stildawn]

Hmm

I'll try deleting all the relationships.

Is there a way to specifically check if I have confused the DB? Like check relationships for a specific table (eg the ones I cleared out)?

Heres a screenshot of the relationships view, to me it doesn't look like anything is linked? But I've never done relationships stuff before:

[ItsMe]

That is interesting. I would compare that window to the relationships window in the original DB.

There are not any relationships in that screenshot.

[stildawn]

It looks the same...?

[ItsMe]

As for the reason your db is running slow. It is most likely corruption. There are not any referential integrity rules being enforced by Access because there are not any relationships defined. Do not confuse this with JOINS in queries.


Suggest starting over using the import technique I described earlier.

Your issue may be as simple as a query object being copied over before a dependent table was imported. However, I would expect a different symptom from, "Running slow".

[stildawn]

Hmmm, I didnt copy or move anything from the original, I just made a direct copy (by copy and pasting the actual file) and then removed the records manually. All objects etc should be (as far as I can tell) exactly the same as in the original, just with no records.

The original is also slow as hell, if that makes any difference? I have a feeling that they just built it crap to begin with.

[ItsMe]

..The original is also slow as hell...

I would say that this is relevant. Suggest importing objects over to new blank DB. Then start working on how queries are structured and when they are used.

Another thing I do when developing is to KEEP data in tables for testing. Import the tables to, yet, another DB. Have this DB stored locally on your development machine. Now you have linked tables in the DB you are developing and tables imported to a second, blank DB. Use the Link Manager to update the links for the tables in the DB you are developing to the DB that only holds the tables. In other words, simulate the FE and BE locally on your development machine.

[lfpm062010]

Questions:

1) was it running slow before? or just now.
2) Did you re-link the table?

[stildawn]

I would say that this is relevant. Suggest importing objects over to new blank DB. Then start working on how queries are structured and when they are used.

Another thing I do when developing is to KEEP data in tables for testing. Import the tables to, yet, another DB. Have this DB stored locally on your development machine. Now you have linked tables in the DB you are developing and tables imported to a second, blank DB. Use the Link Manager to update the links for the tables in the DB you are developing to the DB that only holds the tables. In other words, simulate the FE and BE locally on your development machine.

I'll start this, its going to be a big job though as there are lots and lots of objects. Thanks for your help.

Questions:

1) was it running slow before? or just now.
2) Did you re-link the table?

I have only just taken it over, its been slow (both the original and my new one) since I took it on. Yes all the tables are re-linked.

[lfpm062010]

So, it has always being running slow even before you taking over.

Can you upload a sample database for analysis? Both FE and BE. There are a lot of area that could cause slowness. Queries, Network, Hardware, ... etc.

[John_G]

Hi -

Is the DB running slowly all the time, or just for certain operations (specific forms or reports for example)?

If the database is large (many records), one thing to look for is multi-table queries with a lot of criteria on non-indexed fields.

Another thing you might try if possible is to put both the BE and FE on the same PC; if there is a marked improvement, then maybe there is an issue with the network.

HTH

John