Compact and Repair Problem (1 Viewer)

LadyDi

Registered User.
Local time
Today, 07:37
Joined
Mar 29, 2007
Messages
894
I have a database, that is really bloated, and I am trying to reduce the file size by changing five number fields from Decimal to Long Integer. However, it keeps telling me that it doesn't have the memory to perform this task. When I compact and repair it, it clears all the indexes I had in that table, including my primary key. I've got close to a million records in that table, because it houses service calls for the past year. Do you have any suggestions as to how I can correct this problem and keep it from happening in the future?
 

DJkarl

Registered User.
Local time
Today, 09:37
Joined
Mar 16, 2007
Messages
1,028
Once indexes are deleted you need to recreate them, it likely deleted them because you changed the datatype. Presumably Access can't index a Decimal the same way it index a Long. In the future I would create a blank table from your existing table basically a Make Table query with 1 row selected is a good place to start. Change data types in that table, append all records from 1 to another, then delete and replace your old table with the new one. When dealing with huge amounts of data this is the only way I've found to reliably change a data type when I've had the need to.
 

LadyDi

Registered User.
Local time
Today, 07:37
Joined
Mar 29, 2007
Messages
894
I didn't delete the indexes, and they weren't on the number fields I was going to change. I recreated the table, as you suggested with the new file sizes and with the indexes I wanted. Then I imported the data into the new table. After I removed the old table, I compacted and repaired again. When I went back into that table, it had cleared my indexes again. I've never had a database do this before.

Does it have something to do with the size of the table (it has over a million records)? Should this table be moved to an SQL server? The file size of the back end of the database is a little over 1GB. I don't know why it is so big. There are only five tables in it. Two of the tables have over 100,000 records and then there is this table with over 1 million records. The other two tables are much smaller, containing 30 or fewer records. All the tables originally had a primary key and there are relationships between each table. I've checked to make sure there are not unnecessary or duplicated indexes. I don't know what else to do with this database.

:banghead:
 

DJkarl

Registered User.
Local time
Today, 09:37
Joined
Mar 16, 2007
Messages
1,028
Strange behavior indeed. I know indexes add size to the database, but I can't imagine it would add enough size in this case to cause a problem. SQL server is an option, but 1 million records isn't an unreasonable amount, unless the record size is huge (lots of columns). What I would do next is try re-creating the whole back-end in a new database, relationships and all and move the data over to it, compact/repair the new database and see if it still has the issue. If you have already done this then I am out of ideas short of changing the design of the database.
 

Users who are viewing this thread

Top Bottom