CODEWITHSUNDEEP
sqlms-accessvbams-access-2010
Lorissa Becker
Lorissa Becker

Reputation: 41

Moving Thousands of Records Between 1 Access Database and Another

I have a client that has an old Access 2000 database that they want to upgrade. They had someone build them a new database in Access 2010, and want me to move all of their data over. For the most part it's simple, just copy+paste between the two databases. The problem lies with a 'Jobs' table.

The Jobs table in the old database is set up completely differently from the Jobs table in the new database. The new one has fancy dropdown fields, mandatory fields that you have to put something in before moving on to the next record, and none of the columns are in the same order. I would just copy paste all the stuff by hand, like I did with another table that had fields in a different order, but... there are 34,600 records in the old database.

The client wants to keep all of the old records, and refuses to switch to a different database (like something actually designed to have 40 tables and 20 users). I'm looking for a way to move the old records over without having to manually copy+paste all thirty four thousand of them. The tables are in completely different databases, so I don't know if there is a SQL query that can do what I need.

Upvotes: 1

Views: 399

Answers (2)

ASH
ASH

Reputation: 20302

How about External Data > Access > navigate to your specific DB > Browse > Open > Import Tables, Queries, etc > OK

That should do it for you.

Upvotes: 0

Andre
Andre

Reputation: 27634

Link (or import) the old Jobs table into the new database, so you can access both tables from one database.

Create an Append query from the old table, to append into the new table.

There you can assign each column individually (a goes into x, b goes into y, etc.)

If the new table has mandatory columns where the old table may have NULL values, use e.g. Nz(old.Foo, "*undefined*") to set a default value.

Upvotes: 2

Related Questions