Can't compact my 2GB database Invalid Argument

[Chris Larmer]

Hi,

My XP database has exceeded 2GB when running and gives the error "Invalid Argument"

I've tried deleting tables then tried to compact and repair the database but it will not do it. Any suggestions as I need it to work tomorrow.

Thanks for the help
Chris

[MMcCarthy]

Hi,

My XP database has exceeded 2GB when running and gives the error "Invalid Argument"

I've tried deleting tables then tried to compact and repair the database but it will not do it. Any suggestions as I need it to work tomorrow.

Thanks for the help
Chris

You need to compact and repair. If the database won't do this for some reason see if the ldb (locked) file is open and delete it. This assumes no other user is on the system.

If this doesn't work copy the file to a local hard drive and try to compact and repair it there. Then replace the existing file after creating a backup.

The only other thing I can suggest is that you open a new access file and import all objects, etc.

[Chris Larmer]

You need to compact and repair. If the database won't do this for some reason see if the ldb (locked) file is open and delete it. This assumes no other user is on the system.

If this doesn't work copy the file to a local hard drive and try to compact and repair it there. Then replace the existing file after creating a backup.

The only other thing I can suggest is that you open a new access file and import all objects, etc.

Thanks,
I tried that but it did not work. I'm downloading my backup copy and hoping that I can import the tables into the restored database. Do you know how I can find out the size of the various sections of the database are? In particular tables, code and queries. I'm looking at spliting the database into 2 code and queries in one and data in the other and want to see what improvement I get.
Chris

[Killer42]

Thanks,
I tried that but it did not work. I'm downloading my backup copy and hoping that I can import the tables into the restored database. Do you know how I can find out the size of the various sections of the database are? In particular tables, code and queries. I'm looking at spliting the database into 2 code and queries in one and data in the other and want to see what improvement I get.

Probably not a bad idea, but I think you'll find the other parts are negligible in comparison to the data.

[Chris Larmer]

Probably not a bad idea, but I think you'll find the other parts are negligible in comparison to the data.

Thanks for your reply. The database has extensive code used to append data each week to set tables which it first deletes all old data.

When the database is closed it compacts to 170,000kb in size even though the tables are now full.

However when it is running the database expands to just under 2GB. One of the first steps it does is a do loop where it reads a table which gives a start and end date for the flight. It then reads another table which tells it which days of the week the flight flies. It then creates a row for each day the flight flies from the start date to the end date. This process takes a long time and expands the database enormously. eg at 10% through the database was 600MB but at 30% it was 770MB. Each row has 164 fields which should require 374 bytes (if an integer is 2 bytes long), plus any database and field overheads. It is only creating 100,000 records which should occupy <400MB. By the end of this process the database is nearly 2GB.

Have you got any ideas on where the extra space has gone.

Thanks

[Killer42]

Let me see if I'm following you here. Are you saying that on opening, it create a temporary table, which is deleted when you shut down? Or what?

If it is something along these lines, this sounds as though it could be a good candidate to split and have the user create the temp stuff in their front-end database. I still don't believe the actual code would take up enough space to worry about, but obviously what the code does could require any amount.

I'm going to yell for help to one or two Access experts (VB6 is more my area of expertise). In fact, I'm surprised they haven't jumped in yet.

[NeoPa]

Two issues.

1. Temp data can take up a lot of space in your database. Unless and until you understand how Access does its stuff you will not have a reliable way to predict what space it should be taking up. Splitting the Front- and Back-Ends should certainly help here.
2. Compact & Repair will only work on a database if there is enough space free at the start to cope with the extra temporary data required to do the job. I would allow at least ten to twenty percent for this.

In post #2 you will see an alternative to Compact & Repair which I'm confident you will need to use. Importing all the objects from your old database into two new ones - One for the Back-End (Data) and one for the Front-End (Project).

I'm going to yell for help to one or two Access experts (VB6 is more my area of expertise). In fact, I'm surprised they haven't jumped in yet.

Can't get around to all the new threads Killer. Not reliably in the first couple of days anymore. Would have got here eventually ;) but thanks for the heads-up.

[MMcCarthy]

However when it is running the database expands to just under 2GB. One of the first steps it does is a do loop where it reads a table which gives a start and end date for the flight. It then reads another table which tells it which days of the week the flight flies. It then creates a row for each day the flight flies from the start date to the end date. This process takes a long time and expands the database enormously. eg at 10% through the database was 600MB but at 30% it was 770MB. Each row has 164 fields which should require 374 bytes (if an integer is 2 bytes long), plus any database and field overheads. It is only creating 100,000 records which should occupy <400MB. By the end of this process the database is nearly 2GB.

Chris,

Can you post the code for this process. It shouldn't be taking a long time.

Mary

[PraveenKadkol]

You can compact from outside, I mean open another Access instance in module type DBEngine.CompactDatabase SourceName, Destination it should work.

[NeoPa]

Are you saying this should work notwithstanding point 2 in Post #7? Or did you not read that?
Bear in mind also, that the OP has said that the size of his database is close to 2GB.

[Killer42]

...Compact & Repair will only work on a database if there is enough space free at the start to cope with the extra temporary data required to do the job. I would allow at least ten to twenty percent for this...

I would argue this point. I've had cases where I've had to move a bunch of records from one database to another, and had to keep compacting the destination one when it filled up the 2GB limit and "crashed".

[NeoPa]

I couldn't argue the point with you Killer. I'm only speaking from my own personal experience. If you have had situations where a database closely approaching 2GB has successfully compacted then it must be possible at least. I can only say that I've had a number fail on me in that situation.

[Killer42]

I couldn't argue the point with you Killer. I'm only speaking from my own personal experience. If you have had situations where a database closely approaching 2GB has successfully compacted then it must be possible at least. I can only say that I've had a number fail on me in that situation.

Likewise, of course, I can't speak for all situations. But I have compacted plenty which were not merely approaching, but had already hit the 2GB limit.

Incidentally, does anyone know whether any plans are afoot to overcome this silly limit? Or would that take business away form SQL Server or something?

[NeoPa]

There's a theory that they're not pushing the SQL line quite so hard now (at the expense of Access) but I don't know how far that goes. They never merged the teams in the end I believe, so there is always hope. I've not heard it said yet that they're planning that though :(

[atillman23]

You can compact from outside, I mean open another Access instance in module type DBEngine.CompactDatabase SourceName, Destination it should work.

Awesome...this worked perfectly for my same problem.

[NeoPa]

I'm very pleased to hear that :)

Welcome to Bytes!