October 18, 2016 at 2:32 pm
The unpleasant task of upgrading an MS Access 10 database to SQL Server 2008 R2 fell on my shoulders.
The Access back end had ref integrity enforced with Cascading. After the upgrade, I recreated the database diagram using the SQl tables in MS Access. This was partially successful. The tables and links were recreated but the check boxes to enforce RI and Cascade in the database diagram were grayed out. These were selected in the Access database before I did the SQl Server upgrade.
So my question is: Do I have to implement the RI in the SQL Server environment or do I need to tweak something in MS Access?
Any help is much appreciated.
And yes I am not in the Access fan club.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
October 18, 2016 at 3:35 pm
What tool did you use to migrate the Access back-end to SQL Server? Access has an upsizing wizard that in recent versions will preserve referential integrity. The second option is to us the SQL Server Migration Assistant, and the third is to do the process manually. The Access Upsizing Wizard attempts to create triggers to implement the RI and I believe that includes the cascading deletes (which I'm not a fan of). I believe the SSMA process has some tools to at least deal with some of the issues, but I've not worked with it enough to know how well it succeeds. Anecdotal evidence from others indicates it generally works better than the Access tool.
For what it is worth, here are links to a couple of useful sites:
Move Access data to a SQL Server DB...
10+ tips for upsizing an Access DB...
Hope this helps some.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
October 18, 2016 at 3:49 pm
I used the upsizing wizard. it did not preserve the RI. Thanks for asking.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
October 19, 2016 at 1:39 am
I think you have to set up the the RI in the SQL Server environment, not via your Access FE.....I may be wrong though
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 19, 2016 at 7:49 am
J Livingston SQL (10/19/2016)
I think you have to set up the the RI in the SQL Server environment, not via your Access FE.....I may be wrong though
You are correct - you cannot setup RI in the Access FE - I presume the intent is to connect to the new SQL Server backend using ODBC so that you can continue to use the queries, forms and reports in the Access FE. But if you want to implement RI in that arrangement, it must be done in SQL Server.
fizzleme (10/18/2016)
I used the upsizing wizard. it did not preserve the RI. Thanks for asking
It would be helpful to know the version of Access that you used, as there have been significant improvements from the versions back in 2000/2002/2003. Also what version of SQL Server are you using?
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
October 19, 2016 at 8:39 am
WendellB (10/19/2016)
J Livingston SQL (10/19/2016)
I think you have to set up the the RI in the SQL Server environment, not via your Access FE.....I may be wrong thoughYou are correct - you cannot setup RI in the Access FE - I presume the intent is to connect to the new SQL Server backend using ODBC so that you can continue to use the queries, forms and reports in the Access FE. But if you want to implement RI in that arrangement, it must be done in SQL Server.
fizzleme (10/18/2016)
I used the upsizing wizard. it did not preserve the RI. Thanks for askingIt would be helpful to know the version of Access that you used, as there have been significant improvements from the versions back in 2000/2002/2003. Also what version of SQL Server are you using?
upgrading an MS Access 10 database to SQL Server 2008 R2
assume that is 2010?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 19, 2016 at 10:28 am
yes ms access 2010 and sql server 2008 r2.
When using the upsizing wizard I chose the RI option. Perhaps the triggers would have been a better choice?
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
October 19, 2016 at 10:57 am
Apologies, I should have gone back and reread your original post before asking about versions. I should finish my morning joe before replying to posts!
Unfortunately the Upsizing Wizard was removed in 2013, and as far as I know, has not reappeared in 2016. I think the logic was that the SSMA did a better job of migrating databases to SQL Server. My experience in using the SSMA is limited, as we normally designed the back-end in SQL Server to start with, but you might just give it a try and see what you get.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
October 20, 2016 at 3:44 pm
I used the SSMA back in 2008 to convert an Access 2003 db to SQL Server, it worked quite well.
The only issues were that, typical of an Access app, many of the table & column names used reserved words like Date, Description, Function etc., as well as names with spaces, punctuation and the like. Luckily there are utilities like Find and Replace (http://www.rickworld.com/products.html) under $40 USD that can help.
Don't forget that you need to add primary keys to tables that you want to edit thru the Access UI, in addition to any tables that will participate in RI.
October 21, 2016 at 10:20 am
I downloaded and ran the Migration assistant. It did a nice job moving the tables. Afterwards, I logged into SQl Server Mgmt Studio and activated the database diagram. Some of the Ref Integrity successfully migrated and by adding the missing tables and establishing the RI links, I completed that part of the process.
However, the access queries did not fare so well. Over 60 errors were generated.
Still in retrospect, the migration assistant is much better than the upsizing tool.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
October 21, 2016 at 12:22 pm
Unfortunately, Access queries use a different syntax, and in most cases other than action queries, are used as the basis for forms and reports. So you may be able to focus on just the update, append and delete queries - and they will generally need to be rewritten anyhow. If you are going to continue using an Access front-end, then using ODBC to link to the SQL Server tables, you may well not need to change the select queries. Bill Mitchell's comments are very pertinent too.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply