• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 758
  • Last Modified:

need to copy 130,000 rows from one access db into another access db

Hi,

i need to copy a large number of rows from one Access db (31.9 MB) (db_1) into another Access db (57.6 MB) (db_2).   db_1 has 130,172 rows.   db_2 has 208,778 rows.  Both db's have the same structure.

Thanks!
0
Cam Raben
Asked:
Cam Raben
  • 7
  • 3
2 Solutions
 
pdebaetsCommented:
One way to do this is to link the table you want to copy the records from, then use the query wizard to create an append query.
0
 
Cam RabenAuthor Commented:
How do I do this?  I am a SQL novice so explicit instructions would be great.
0
 
aikimarkCommented:
or just use the external data import menu, if this is going to be a one-time import.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Cam RabenAuthor Commented:
It imported successfully.  Thanks much!
0
 
Cam RabenAuthor Commented:
ps  Now that I have both tables in my original db, how would I append the rows of the newly imported table into the table in my existing db.    (both tables are identical)
0
 
aikimarkCommented:
just create an append query
0
 
Cam RabenAuthor Commented:
I did and got a warning that "MS Access can't append all records in the append query:  MS Access set 0 fields to Null due to a type conversion failure, and it didn't add 66201 records to the table due to key violations.   ??
0
 
Cam RabenAuthor Commented:
It appears to be due to "key" violations.  Both tables have an ID field that is an autonumber primary key.  I think the failure to append 66,201 records has to do with this key but I'm not sure.  ?
0
 
Cam RabenAuthor Commented:
Ok, I got it to work but..... I had to remove my ID field (autonumbered PK) from both tables.  From what I know about relational db's this is not recommended.  Is this true, and if so, is there a workaround to retain the integrity of my PK field?   If I don't need it so be it, but I want to make sure before I go further.  

Thanks much for any advice you'd give me on this Aikimark (this is that database of NC water quality data, btw).
0
 
aikimarkCommented:
The ID field of the target database will automatically increment.  This might only be a problem if you have other tables that you join with these two tables on the ID column.  Otherwise, no problem.
0
 
Cam RabenAuthor Commented:
Excellent - thanks.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now