table too big - what can I do?

[Guest]

I have a query that is 280,000 records. When I do make table query, I get my
table, but it won't let me make changes in design view because there is not
enough disk space or memory - which I am told means the table is too big. I
need to make a table or a couple of tables without compromising my data!

 

[Guest]

1. Do a compact and repair and see if that frees up some space.

2. Failly that, create a new mdb database file and link to your tables
there. Run the query in the new database. You can then link that table back
to the orginal.

3. Actually #2 isn't correct. You should already have your database split
between the front end with all the forms, queries, and reports. The back end
should have all the tables. That should split up the size of the database.
You can create temporary tables in the FE; however, it's really best to do
that in the BE unless you are going to drop the temp table after running a
report or something.

 

[Guest]

I tried to compact and repair. What I need to do is not compromise the data.
The table has to stay in same format, just maybe split into a couple of
tables. Can I do this by splitting the database?

 

[Albert D. Kallal]

I tried to compact and repair.

You need to be able to do the above.

I would suggest that you create a new blank database.

Then, go file->get external data->import

280,000 records is actually not a that large of a file. (unless their is
imbedded pictures inside).

So, you need to be able to do a compact and repair on he data file. That
should allow your query to run.

 

[Joseph Meehan]

I have a query that is 280,000 records. When I do make table query, I
get my table, but it won't let me make changes in design view because
there is not enough disk space or memory - which I am told means the
table is too big. I need to make a table or a couple of tables
without compromising my data!

280,000 records is not all that large. The max size of an Access
database is 2 gig (1 gig for old versions). How many fields do you have in
the table? Is the table properly normalized? Do you have any photos stored
in the table? What kind of data is stored? Do you have memo fields?

 

[Guest]

there are about 26 fields in the table, and no pictures, all text! But
somehow the original person set up the table with some fields as numbers, and
some fields as memo (data type), so when we go to save as a text file, it is
messing things up. I was able to run query, and then do make-table query, and
then run, and come up with a table, but it is too big to let me do the data
type changes.

 

[Arvin Meyer [MVP]]

How big is a row of data? I have tables with millions of records that don't
exhibit this behavior. You can have a maximum of about 2000 bytes in a row
of data, and every text character eats 2 bytes. An Integer eats 2 bytes, and
a long 4 bytes. Doubles and Datefields eat 8 bytes each. Memo fields and OLE
objects are not stored in the same table. A database allows 2 GB, so if each
row were the full 2K and you had a million rows, you'd have a full database,
almost 4 times what you have, assuming no memo fields.

You have another problem. Possibly, as Jerry suggests, you have other
objects besides data in your data tables. This calls for splitting the
database (not the data) so that all objects are in one database container,
linked to the tables only in another container. If you have more than 1
user, you put the data only on the server, and the frontend mdb (all other
objects) on each workstation ... not on the server.

Before you destroy your database, make several copies of the file, and only
work on the copies.

 

[John W. Vinson]

there are about 26 fields in the table, and no pictures, all text! But
somehow the original person set up the table with some fields as numbers, and
some fields as memo (data type), so when we go to save as a text file, it is
messing things up. I was able to run query, and then do make-table query, and
then run, and come up with a table, but it is too big to let me do the data
type changes.

You certainly should not need to be ROUTINELY or repeatedly changing
datatypes or running MakeTable queries!

A way to get around this error is to create a *new*, empty table, with
the desired datatypes to all the fields; then run an Append query from
your existing table into the new one. A MakeTable query needs to keep
the ENTIRE old table *and* the entire new table in memory at the same
time; your table isn't too huge by Access standards, but it's clearly
too big to do so. The Append query works record by record so it
doesn't make such heavy demands on memory.

John W. Vinson [MVP]

 

[Joseph Meehan]

there are about 26 fields in the table, and no pictures, all text! But
somehow the original person set up the table with some fields as
numbers, and some fields as memo (data type), so when we go to save
as a text file, it is messing things up. I was able to run query, and
then do make-table query, and then run, and come up with a table, but
it is too big to let me do the data type changes.

:

Well any field that stores numeric data that may sometime be used in any
kind of calculation should be stored as a number type. That really should
not cause a problem when running a query or saving it as a text file.

26 fields is a lot. It may well be proper, but I would tend to suspect
that some improved table design (normalization) could be done here. It may
also reduce the overall size of the database. Are any of the fields
redundant, like [First test result], [Second test result], [Third test
result] etc. ?

Are the memo fields needed as memo fields? That is may they need more
than 255 characters?

 

[Guest]

Hi Stacy,

This might be an issue that requires you to increase the MaxLocksPerFile
setting. Here is a KB article that discusses this:

You may receive a "There isn't enough disk space or memory" error
message when you perform an operation on an Access table

http://support.microsoft.com/kb/286153/

As Arvin as stated, make sure you create some back-ups of your data. It is
best to store at least one back-up on a separate physical device, such as a
thumb drive, or a different hard drive (not a partitioned hard drive). You
should make frequent backups at an interval that is determined by your pain
threshold for re-entering data since your last backup was made. If you have a
hard drive failure in the future, you should be able to restore from a backup
from a different device.

Note: I do *not* recommend using re-writable CD's for backing up any
important files. The reason that they are re-writable is that a laser beam
does not burn the pits very deep (information is stored in binary format, as
a series of 1's and 0's--a pit represents one of these numbers and no pit
represents the other (I don't remember which is which). Burning information
to a write only once CD is a much better alternative for archiving data.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Stacy,

I was searching for some help with the same issue today and I came across
this thread. The solution for me was to save it in the latest format and to
keep increasing the MaxLocksPerFile entry in the registry. Kind of scary, I
had to bring it all the way up to about 238000 (tried 19000, 38000 then went
to 238000) before it worked. Now it seems to be working fine. I'm in Access
2007 and when I am done making these large transactions (or updates) I will
then save it back to a lesser version of Access and decrease the value in the
registry back to the original size of 9500. Good Luck