Migrating from Access to SQL Server

[Dad0AEW]

A client has an Access 97 database that I am looking to migrate the data to SQL Server 2005. The Access database uses the mda/mdw security, so when I try to import the data via the import task in SQL Server or when I try the SSMA tool I get errors because of the security. Does anyone know of a way to migrate data in a SQL Server database by either bypassing the mda/mdw security, temporarily disabling security, or maybe even removing the particular database from the security. I should note that there are users who are still using the Access 97 database (the production copy), so security would still need to be in place.

Thank you.

 

[kjv1611]

Hmm.... is this a one time move, or are you going to need to keep pulling data for a time?

If you can do it at once, can you simply export the data from within the database to your SQL Server, or else to Text files, and then import into SQL from Text? So in other words, you'd be pushing from Access instead of pulling from SQL, and therefore that should get around your security constraints... I think..

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

By the way, this seems more like an Access: Other type question or maybe Tables question rather than a Queries question. Just try to be more cognizant of which forum you enter your questions. Here's a list of Access forums:

http://www.tek-tips.com/forumsearch.cfm?q=Microsoft+Access&type=Forum&sub=Search

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57