Splitting a large table into multiple smaller tables

[Guest]

I need to split a larger table into many smaller tables and keep the larger
table so that when I update, delete and add data the smaller tables are
updated as well. I can't seem to find anyway to do this in access. I need
the smaller tables as my e-store server only allows 5,000 records to be
uploaded to my store at one time and my table consists of 175,000 plus
records. I do have to perform updates etc... often so the ability to run
queries etc... on the main table so that the smaller tables will be updated
as well is a must. Does anyone know if this is possible and if so what is
the most simple way to accomplish this? I am certainly no database guru!
Any advice would be appreciated!

Thanks!

 

[G. Vaught]

You can link tables in a different database into any other database; this
will inherently keep your database small. However, it is not wise to split
one table into two tables. This should be an all or nothing proposition.

See File | Get External Data for linking in other tables.

 

[Guest]

Hi, I am not worried about keeping my database small, I need to be able to
upload the data from this large table. I can't do that when it has more than
5000 records. I know I can split the table into many smaller tables with
make table queries, however, that does not allow me to update the main table
and have the updates carry over to the smaller tables. What I am trying to
avoid is constantly updating the main table and having each time to split it
into the smaller tables so I can upload them to my store.

 

[Larry Daugherty]

What ever the eventual solution it will not involve creating several
tables. The answer to your root question is that there is no way that
one table "knows" about another table. Second, it violates one of the
essential rules for relational databases to store data redundantly in
the database.

Are you limited to 5000 record transfers in both directions? If so,
how do you get by now?

What goes on at the e-store end of things? What is the table
structure there and what code is running there? Is it another copy of
your Access database?

HTH

 

[Al Camp]

Tina,
Of course, as you know, the solution should be addressed to the server
than can receive only 5000 records. That is really the "weak link in the
chain."
Even the "last resort" solution of breaking the main table into smaller
tables, via make table queries, and then reassembling on the other end with
append queries... is so flawed as to be unworkable.

A critical issue that other eresponders will need to know is... how often
do you do this update, and approximately how many records would need to be
added/revised at each upload? In other words, by how many records does your
main database grow between updates. If you ran daily, how many would you
expect?

Have you considered Replication? It's purpose is to allow remote users
to update daily (or when neeed)... but only the records that have been added
or changed in the main database since the last upload. With a daily upload,
would that do it for you?

Also, have you considered emailing the table? Place the main table into
it's own .mdb, zip it, and email it to your store? The receiving store
could just delete their table and import yours. I use emails all the time
to send large zipped mdbs to clients. My ISP (as do most) allow a 10MB
limit (say 9MB for practical purposes).
I just put a table with 20,000 records in an mdb, and zipped it to 750K

If none of these solutions work for you, being in business, and time
being money, you must consider solving the server problem.