How to break up a 9000 record table into smaller ones using VBA?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Milton R in California - 21 Jan 2007 02:42 GMT

I need to break a 9000 (it might be larger later) into smaller tables
containing 700 records each. Each table would be named, say Toys, serialize,
Toys1, Toys2, etc.

Reply to this Message

Rick Brandt - 21 Jan 2007 02:53 GMT

> I need to break a 9000 (it might be larger later) into smaller tables
> containing 700 records each. Each table would be named, say Toys,
> serialize, Toys1, Toys2, etc.

What on earth for? You'd be going in the wrong direction. Multiple tables with
the same structure is almost never the right way to do a database.

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

Milton R in California - 21 Jan 2007 07:41 GMT

Rick:

Thanks for your response. I bit more clarification might help in seeing what
my goal is. I would like to be able to export to a text file a small number
of records, 900 max., for another application to process. This other
application has record limitations I can’t override.

Thanks
Milton

> > I need to break a 9000 (it might be larger later) into smaller tables
> > containing 700 records each. Each table would be named, say Toys,
> > serialize, Toys1, Toys2, etc.
>
> What on earth for? You'd be going in the wrong direction. Multiple tables with
> the same structure is almost never the right way to do a database.

Reply to this Message

Douglas J. Steele - 21 Jan 2007 11:11 GMT

To reiterate Rick's advice, do not split them into separate tables.

Instead, write some queries that return the appropriate number of rows, and
export the queries, rather than tables.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Rick:
>
[quoted text clipped - 15 lines]
>> tables with
>> the same structure is almost never the right way to do a database.

Reply to this Message

Milton R in California - 22 Jan 2007 03:28 GMT

Okay, that sounds good, and I actually tried to do this first. However, I run
into problems when the only criteria I have for creating the queries is that
the first query would return the first 900 records, which is easy to do; but
how do I make the subsequent queries return the following 900, and the next
900, etc. until all records from the table are exhausted?

> To reiterate Rick's advice, do not split them into separate tables.
>
[quoted text clipped - 20 lines]
> >> tables with
> >> the same structure is almost never the right way to do a database.

Reply to this Message

Douglas J. Steele - 22 Jan 2007 11:27 GMT

I was under the impression that you had some way of splitting your data into
smaller bits, by selecting only records with a specific value (or values).

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Okay, that sounds good, and I actually tried to do this first. However, I
> run
[quoted text clipped - 32 lines]
>> >> tables with
>> >> the same structure is almost never the right way to do a database.

Reply to this Message