How to Copy over 2MM records?

[MsAxes]

Hi



I have a database with 2.1 million records. The file came from an XML file and it split the data into multiple tables instead of 1. I would like to copy one of the tables to another but can't due to the 65k limit. Basically, I need to merge those two tables in the exact order their in but can't seem to find a way. I know I can export to Excel just as long as I don't click on any of the boxes but Excel won't allow me to view that many records in one file so I could merge them their.

Any ideas?

[ranman256]

you need the new Excel and newer access that allows 1.x million rows. (split the Excel file)
i would start with 2 db, 1 for each table, in case you go over the accesss size limit.

[CJ_London]

I would like to copy one of the tables to another but can't due to the 65k limit

why not use an append query? no 65k limit. Why does order matter? if it does then is that order from 1st record to 2m record? or one table to the next?

[MsAxes]

I didn't think of the Append query - the order matters because one table has EINs (no other fields) and the other has the names (no other fields) they are tied to. The order the tables are in now are how they should be tied to each other.

[MsAxes]

Just tried the append query approach and ran into a "Cannot open database ". It may not be a database that your..." I looked it up and found that error is returned when you are at the 2gig limit. Not sure if its even possible. Doesn't appear to be with the software I currently have.

[Gicu]

Before doing anything else you need to establish a link between the two tables. Open them in design view and add an autonumber field in each. When you open them back in datasheet view the new field should be populated and if both have the same number of records (which I believe they do from your post #4) they should match.
Next you need to start with an empty table that has both fields (EINs and Names) and a new ID field (the autonumber from previous step), run an append query based on EINs then run an update query joining on the autonumber fields from the combined table and the names table.

EDIT: to avoid the 2GIG size error you can create the resultant combined table in another new (empty) Access file and linked that into the file where you have the source data.
Cheers,

[CJ_London]

I don't know what an EIN is but are you saying that the only way a relationship can be defined between two tables is the order in which the records are stated? So record 1 of table 1 is related to record 1 of table 2? It might be worth looking at your XML file in more detail as I suspect there will be something in there that links the tables together. Perhaps find a suitable xml viewer?

[MsAxes]

Gicu - thanks for your suggestion. That worked for me but Ajax your suggestion was solid too, I just saw it a bit too late.

Thank you both