CODEWITHSUNDEEP
sql-servert-sqlms-accessforeign-keys
murla
murla

Reputation: 97

Exporting data from MS Access to MS SQL with schema and table changes

I'm working on the old C++ MFC project (> 10 years old). Database application works with migrating from MS Access (2007) to MS SQL Server (2008 R2) and I faced some hurdles on the way. For exporting data I used MS SQL Management Studio ("Import" option in the menu) As it's known, there are some differences in data types between Access and MS SQL. That turned into some troubles.

  1. Columns "ID" from Access (Autonumber, not NULL, primary keys) become just usual columns in SQL Server (int, not NULL and without any autoincrement). So I got lots of mistakes while inserting new rows into the tables.

  2. Yes/No type in Access (-1/0; NULL is not allowed) becomes bit (1/0/NULL), logic of work shouldn't be broken as in the most of the places it is comparision of being not equal to 0:

    query.Select() .Buff("ID", &code) .FromS("%Table_Name%", NULL) .Where().Str("Aktiv <> 0") .Execute();

Looking for a solution I saw the advice to use SSMA (SQL Server Migration Assistant) for Access. It's much better and more intellectual as it recreated primary/foreign keys, created CHECK's, indexes. But unfortunately lots of the FOREIGN KEYs' action Update/Delete operation become not Cascade but No Action. Warning message after schema import:

FOREIGN KEY constraint "Reference77" on MS Access table %Table1% may cause circular or multiple cascade paths. The cascade option from table %Table2% to table %Table1% was set to No option in SQL Server.

And that's not a surprise application gets some errors while deleting objects, though it was all OK in Access. For testing I selected one delete operation (in application) which got errors. I watched error messages and changed No Action -> Cascade for the involved FOREIGN KEYS via SSMS (SQL Server Management Studio). After that delete operation in the application succeeded.

My questions are:

  1. Am I right I need only to change No Action -> Cascade for the FOREIGN KEYs to get the database application can work completely proper? Or there can appear another issues I don't know?
  2. How can it be realized? I would like it to be a good solution for applying it on clients' SQL Servers.

Thanks for help, I really appreciate it!

Upvotes: 2

Views: 5074

Answers (2)

murla
murla

Reputation: 97

Thanks for your answer. The solution for my problem is ... exporting data directly from Access (2010) to SQL Server. I tried:

  1. "SQL Server Import and Export Data", result - copying of only data from Access database, no any primary oк foreign keys, no transformation of autonumber to a column with IDENTITY and autoincrement.

  2. SQL Server Migration Assistant for Access, result - a lot of foreign keys lost CASCADE property for update/delete operations. But all another things are OK.

  3. Access 2010! Database Tools -> SQL Server -> ... using wizard -> all is OK with schema and data. Application works fine with the SQL Server database imported from Access.

So direct export from Access to SQL Server gave the required result.

Upvotes: 4

Alexey A.
Alexey A.

Reputation: 902

  1. Probably, but you will still need to test.

  2. For a reusable solution, I would script the database that SSMA created (checking that all the types and foreign keys are correct). Having this script you can create an empty SQL Server database on any number of servers. To populate these databases I'd use an Integration Services package. It's very easy to create by using Import wizard: going thru all the steps, but saving the package instead of running it immediately. Then you can open this package and edit it (adding data conversions or any other logic if necessary).

Upvotes: 2

Related Questions