469,271 Members | 1,466 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,271 developers. It's quick & easy.

Can't compact my 2GB database Invalid Argument

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
Jan 22 '07 #1
15 11059
MMcCarthy
14,534 Expert Mod 8TB
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.
Jan 22 '07 #2
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
Jan 22 '07 #3
Killer42
8,435 Expert 8TB
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.
Jan 22 '07 #4
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
Jan 22 '07 #5
Killer42
8,435 Expert 8TB
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.
Jan 22 '07 #6
NeoPa
32,171 Expert Mod 16PB
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.
Jan 22 '07 #7
MMcCarthy
14,534 Expert Mod 8TB
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
Jan 22 '07 #8
You can compact from outside, I mean open another Access instance in module type DBEngine.CompactDatabase SourceName, Destination it should work.
Jan 22 '07 #9
NeoPa
32,171 Expert Mod 16PB
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.
Jan 22 '07 #10
Killer42
8,435 Expert 8TB
...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".
Jan 22 '07 #11
NeoPa
32,171 Expert Mod 16PB
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.
Jan 23 '07 #12
Killer42
8,435 Expert 8TB
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?
Jan 23 '07 #13
NeoPa
32,171 Expert Mod 16PB
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 :(
Jan 23 '07 #14
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.
Oct 20 '08 #15
NeoPa
32,171 Expert Mod 16PB
I'm very pleased to hear that :)

Welcome to Bytes!
Oct 20 '08 #16

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by jcazmail-groups | last post: by
6 posts views Thread by owengoodhew | last post: by
6 posts views Thread by scottyman | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.