Migrating data from Access 2000 to SQL Server 2000

Question

Migrating data from Access 2000 to SQL Server 2000

Curtis Donato

Newbie

Points: 1
More actions
March 28, 2006 at 2:24 pm

#171724

My apologies if this is not the correct forum to pose this scenario, but I will likely be faced with this soon, so I figured I'd ask if anyone had encountered something similar. We have probably 100 or more Access databases, all of identical table structure and relationships (local copies), which will need to be migrated into one SQL Server database. Since these have the same structures and therefore auto generated IDs then we will have duplicates and constraint violations to deal with when inserting data.
Has anyone encountered something similar? It has been a while since I've had the opportunity to use SQL Server, but we finally convinced the stakeholders that we should be using it. I have started to research what my options might be and figured that I would ask for some advice. Any would be greatly appreciated.
Thanks,
Curtis

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Darrell Adams Old Hand Points: 346 More actions · Answer 1

I had a similar problem with a budget program that was created 5 years ago and placed on different computers by different users.

I took an empty table and an insert query, linked to the same table in the other MDB and ran the query. Broke the link, linked to another and etc. etc.

Not pretty, but it worked. You would need to make sure your query links to "linked" tables.

then, when I was done, did an upsize, all is good.

Dick Schroth Right there with Babe Points: 725 More actions · Answer 2

This process could be automated with some programming. I don't think the above approach would be enough if your database involves related tables, autonumber fields, etc. And the upsizing wizard alone won't create a database that will work for you, because somehow you need to assign new unique keys and update the key fields throughout the database, and make the relationships based on the new keys. One approach would be to have a new field, identifying which of the databases the record came from, which when combined with the existing key would make the key unique. Your queries to fill the SQL Server database would fill in this field.

Dick

Mike Prascak SSC Eights! Points: 930 More actions · Answer 3

I've done a lot of Access to SQL Server migrations, and still many to go. For your situation, I would recommend the following:

create the SQL tables and structure (take the time to get it right)
create a link via odbc within Access to the SQL server
create queries and macros in Access to append the data to the SQL Server
repeat steps 2 and 3 for all existing Access apps
once data is all uploaded, take on the other usage conversions

I recommend this scenario because you are most familiar with Access.

There is no "magic wand". Just roll up your sleeves and get to it.

Mike Prascak

Charles Wannall Ten Centuries Points: 1003 More actions · Answer 4

I haven't seen any mention of whether you are trying to combine all 100 databases into a single database in SQL Server. Is that your goal?