Access Table

N

N i M d A

Hi all,

I have an access table which contains more than 1 million records, I need to
split this table into multiple table can anyone give me a tip?

Thanks in advance
 
K

Klatuu

Why is it necessary to split the table up? The number of rows in the table
being more than 1,000,000 is not unusual.
If there is some other business rule driving this, please explain.
 
N

N i M d A

Hi Dave,

actually this is the 1rst time that i'm facing a situation like this,
normally i work with small db's,so i want to devide the access table into 4
parts so it will be easyier to import & export the data. any tips or advice?
 
K

Klatuu

I think you would create more problems than you would solve.
Importing is not an issue.
Exporting may be, depending on what format you want to export to and what
versions you are using. If you Export to Excel, 2003 will only accept 65,546
rows (less one for headers), but 2007 will. I don't recall the maximum for
2007, but I think it is 1 million. The short of it is, you can export
filtered subsets without a problem.
 
J

John W. Vinson

Hi all,

I have an access table which contains more than 1 million records, I need to
split this table into multiple table can anyone give me a tip?

Thanks in advance
Click to expand...

A million records is large but managable - I know of databases with 20,000,000
rows in the largest table. Splitting JUST because of a million records is
likely to be a bad idea! Does the data consist of logical groups of records,
or were you thinking of just (say) 10 tables of 100,000 records each?

To directly answer though, I'd create a new, empty database; create however
many tables you want (hopefully based on logical distinctions, not arbitrary
ones!). Be sure to define the field types and field sizes; text fields should
be sized to no larger than the maximum size you'll need (e.g. US postal Zip-5
fields should be 5 characters, not the default 50 or the maximum 255). Use
File... Get External Data... Link to link to the existing large table.

Then run as many Append queries as needed to migrate the data. Each query
should have criteria to appropriately limit the records. Avoiding duplicates
between the tables will require careful query design!
 
D

Dip

Hi all:

I agree with Dave. The reason we prefer Access instead of Excel as a
database is that it is centralised, and it has been designed to handle a few
million rows as such. It would be very easy to make mistakes if you make this
access database into many smaller ones. On the other hand, if you select
excel, it would be more convenient if the files are not that big. The choice
is yours to select Access or Excel for this project!

Regards,
Dip
 
Top