Link to home
Start Free TrialLog in
Avatar of etech0
etech0Flag for United States of America

asked on

Something is wrong with my Access database...


Something is wrong with my Access database, and I can't figure out what it is.

It's been freezing, crashing, and behaving oddly.

I decided to recreate the front end. I created a blank database, and started importing objects.

After importing about half of the tables from the old front end (most of which are linked tables to the back end), the new front end file is freezing as well.

Does that mean that I have problems in my tables?

I'd appreciate any help in troubleshooting this.

Avatar of Norm Dickinson
Norm Dickinson

You should do a backup of your data files and then run the database maintenance routines to check the integrity of the databases. Also try a blank database with the same structures and just a few sample records. Most likely it is a problem with the data rather than the tables.
Avatar of etech0


What are "the database maintenance routines"?

Regarding your second suggestion, it's a huge database with many tables. Is there an easy way to recreate the structures without the data?
The easiest thing to do for database maintenance is a compact and repair routine. In Access 2010 that is found under the Database Tools tab.

Using a backup copy of the database, obviously not live data - go into each table in datasheet view and click the little square in the upper left corner to select all records and delete them.
Avatar of etech0


I'll give it a try and let you know...
Avatar of Jeffrey Coachman
Sounds like a size issue...

How big is this database?
If it is nearing the 2GB limit, then things like this can happen.
Other causes:
You have a lot of Images, Forms, or Reports.
You are creating a lot of temp objects
You are using inefficient datatypes (Using LongInterger when Byte might work as well)
You are using a lot of recordsets and not closing them and setting them to Nothing

Do you have all your Service packs installed for Office *and* Windows?
Avatar of etech0


I compacted and repaired the back end.

Now, in my newly created front end (which still only contains tables), it still hangs a bit when I try to open any table. Sometimes it even says Not Responding, but then it goes away.

I can try deleting data from a backup of the database, but how on earth should I know which tables to do it to?  I have about 150 tables...

Regarding size:
The back end is about 300 mg, the front end about 80.

Of course I have a lot of forms and reports - it's a large database.

What do you mean by temp objects?

I don't think I use longinteger anywhere in my database.

How do you close recordsets?

I run Windows Update about once a week. Can I assume that I have all service packs?

So you are saying that running the compact and repair process basically solved the problem at hand - namely, crashing, freezing and behaving oddly - but that now you are still seeing a slight delay?

That is probably a great thread for an entirely new discussion, posted as a new question to get people who are better at that focused on it. My own specialty is getting things running again more than optimization. Perhaps a defrag on the drive with the front end, or adding more RAM or testing it with a faster processor or SSD drive would be helpful - it may just be a hardware limitation.

I would recommend closing this question, awarding points as you see fit, and persuing the question with another thread. Points are cheap after all, and we really don't get much out of them other than free access to the site for the first couple questions we answer each month. Glad we could help though!

And yes, you are likely running the latest service packs, as long as your Windows Update settings look for Windows and other Microsoft products, but you can always search for the latest updates.
Avatar of etech0


Actually, not.

The crashing, freezing, and behaving oddly is still going strong, in my real front end. In the new front end (that just contains tables), I am just seeing a slight delay. I'm not sure if compacting and repairing improved it at all - if so it's a slight difference.

I don't think this is a hardware issue, as I'm using a pretty new computer with 4gb of RAM and a 3.1gh i5 processor. But who knows?
<What do you mean by temp objects?>
if you don't know, then you are probaly not using them...

<I don't think I use longinteger anywhere in my database>
I meant inefficiet datatypes anywhere inyour database, not just link Integer.
For example, Long iteger is 4 bytes of storage size, Byte datatype is 1 Byte.
So if you have an "age" field for  humasn, thenm Byte would be a better choice
...but I don't think thisisi the issue either...

<How do you close recordsets?>
At the end of your code put:
set rst=nothing

But let's be clear:
You create a blank newdb and "only" import the tables,and it still displays this error?
If so then you may want to try importing 10 tables at a time and testing along the way.
(to see where the "problem" tables might be)

Finally is this db erroring on only this one machine?
Avatar of etech0


I'll update my code to close all recordsets - I didn't know about that.

in the new blank db, there's a slight hang when opening tables. Nothing like the VERY slow and freezy behavior in my real front end. I'm not sure if the problem is tables, or forms, or something else.

I'll try importing a few tables at a time, and see if the hanging behavior only applies in some cases.

The slowness and crashiness is on all machines, but it's worse on some than on others.
Avatar of etech0


Updating my code to close all recordsets does not seem to have fixed the problem.

I created another new database, and imported some tables. At some point, it crashed. I deleted all the tables in this new database, and it's still freezing. Does that mean that the database is corrupt?
Quite possible based on the above. Do you have older archives of the same database from when it used to run ok, or did it ever? You may be able to restore an older copy and import more recent records - a few at a time might be a good idea - to figure out where the problem record is. My theory is that if nothing else changed to cause an issue, it is always the data. One bad record can corrupt an entire database. Power glitches, installations and even anti-virus applications can cause issues. Can you run in safe mode with nothing else running?
Avatar of etech0


I have backups of my database from a while back. The problem is that we've been experiencing slow behavior for even longer. And, this is such a huge database with so many tables, that it would not even be possible to figure out what data was changed when.

Is there a way to search for bad data? Is it possible that forms or other things are corrupt?

The funny thing is that the new database I made is also freezing. It contained a few of my linked tables, but froze on one when importing. Does that mean that that table is corrupt? (It hangs on many tables, but that was the first one it froze on. When I closed and reopened, though, that table in question was there, along with many that come later in the list. So it looks like it was importing, it just looked frozen. Does that make sense?)
If you are using part of the database that has been giving you trouble, it makes sense. However, if you have just created a brand new database that has nothing from the old database, and it is also freezing up, you have a system or network issue, not a database issue.
Avatar of etech0


This new database has linked tables to the same back end as my regular database.
Avatar of etech0


Is there a way to search for bad data?
The compact and repair process we went over earlier should help with detecting some problems but I am not aware of an easy way to detect corruption.
Avatar of etech0


I've compacted and repaired (or however you say it) many times, but it has not resolved anything.

Do you have any suggestions how I can diagnose and or cure my database?
Avatar of Norm Dickinson
Norm Dickinson

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of etech0


Will repost. Thanks for your help!