Split an Access table in smaller parts

[Guest]

Hello,

I have a huge Access table that I need to split in smaller parts. Does
anyone know a query or some other method to achieve this, other than manually
cutting and pasting rows of data?

Thanks for your help

 

[BruceM]

What is it about the size of the table that troubles you? What is the
structure of the current table (i.e. what information is stored there)?

 

[Guest]

I have to export that table into a text file, then load that file into a
different application. That application can't seem to take the one large
file, so I want to break my original access table into smaller ones and have
smaller text files that I can load into the other application, which is a
claims management system.

 

[tecas]

I have to export that table into a text file, then load that file into a
different application. That application can't seem to take the one large
file, so I want to break my original access table into smaller ones and have
smaller text files that I can load into the other application, which is a
claims management system.






- Show quoted text -

write mulriple queries splitting up the table into smaller parts and
then export the queries into text files for importing into your other
system.

HTH

Ted

 

[John W. Vinson]

I have to export that table into a text file, then load that file into a
different application. That application can't seem to take the one large
file, so I want to break my original access table into smaller ones and have
smaller text files that I can load into the other application, which is a
claims management system.

You can, and should, do as Tecas suggests: export from Queries, not from the
table. If you have some field in the table (such as an Autonumber) that would
let you break the table into managable chunks, just use a query with a
criterion on the field (e.g. BETWEEN 1 AND 10000, BETWEEN 10001 AND 20000 and
so on). It's just exactly as easy to export a Query as a Table; you can export
the data sorted as desired (which you can't with a table); it avoids the
overhead and labor of creating additional tables.

John W. Vinson [MVP]

 

[Guest]

Thanks for the idea, John. I found on this forum the SQL code to make the
export, and it worked well. However, the results is in a delimited file
format. What do I need to change in the expression below if I want the
results in a fixed width format?

INTO [Text;HDR=YES;DATABASE=C:\Documents and Settings\My
Documents\Work\\].QryResult.txt

I do have my export schema saved in the export wizard with the positions and
lenght of each field...is there a way to retrieve that code and add it to the
line above? and if there is a way to do all of this using only the query
design view (no SQL)? thanks for all your help and sorry for all the
questions.

Jose

 

[John W. Vinson]

I do have my export schema saved in the export wizard with the positions and
lenght of each field...is there a way to retrieve that code and add it to the
line above? and if there is a way to do all of this using only the query
design view (no SQL)? thanks for all your help and sorry for all the
questions.

See the online help for the TransferText method. Just use a standard Select
query, not a MakeTable query; and use TransferText (specifying your output
specs as one of the arguments) to transfer the data from the query into the
external text file.


John W. Vinson [MVP]