?
Solved

Recommendations for Upgrade Microsoft Access 2007 to SQL 2005

Posted on 2011-10-24
31
Medium Priority
?
490 Views
Last Modified: 2012-05-12
We have a MS Access 2007 database that we want to convert.  The DB has over 80 tables and a bit over 210 queries.

We would like to have a SQL 2005 database with the same structure as the MS Access.  We don't know if there is any 'Upgrade' tool to actually do a transfer like this or we just create the DB and create each of every tables & queries.

The final product is having the data in Access in SQL exactly and functional.

What considerations and method should we look at when performing a project like this?
0
Comment
Question by:rayluvs
  • 13
  • 4
  • 4
  • +6
31 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 156 total points
ID: 37019280
0
 

Author Comment

by:rayluvs
ID: 37020133
That was the word we were looking for: Migrating.

Thanx for the info, we'll proceed with the reading & testing.  However, for this question we would greatly appreciate, in EE own words, their expediences and appreciation of the process of migrating from Access to SQL.  If there hasn't been any EE who has done actual migration from Access to SQL, at least sound experienced advice in Migrating to SQL from any other DB.

Our main concern are:

- The relationships established in the Access and if all will be applied to SQL successfully (there are
  a lot of relationships)?
- How are the queries migrated to SQL? (are they migrated as View tables?)

0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 156 total points
ID: 37020519
1. Avoid using table Lookup fields, Multivalued Fields, Hyperlinks, (or any other "MS Access Only") datatypes.
As of this writing there are no direct SQL dataypes that these will convert to.

2. Avoid using the (Access Only) NZ() as this does not exist in SQL,
(you will have to use IIF-IsNull, Coalesce, ...etc, in SQL)

If you are using calculated controls, note that these are sometimes better as calculated fields in the Recordsource

Note that Boolean in MS Access is 0=False=No, -1=Yes=True
Not that it matters much, because 0=False=No, is universal.
It is just that some Access Devs will code for the -1 specifically

I'm sure other Experts will post more issues...

;-)

JeffCoachman
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:rayluvs
ID: 37020562
Thank you, Yes, that was we're talking about right now; things to avoid!!  Hope other EE post their insights.  Thanx!!
0
 
LVL 18

Assisted Solution

by:jmoss111
jmoss111 earned 312 total points
ID: 37020710
0
 

Author Comment

by:rayluvs
ID: 37020829
Just checked out the link.  Its great for our VB environment, but what does imply in the Access DN itself regarding the queries already saved? We mean that the Access DB has a lot of saved queries, does this mean that prior running the MS SQL tool, we have to literally review and modify all the 200+ queries in the Access DB?
0
 
LVL 18

Assisted Solution

by:jmoss111
jmoss111 earned 312 total points
ID: 37021056
I think that SSMA will migrate what it can and will give you an error report on what cant be migrated. Once migrated and running you might want to optimize to take advantage of SQL Server
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 37021074
By the way, what made you decide to migrate to SQL Server?
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 37021121
You can keep the queries you have in Access and run them against the SQL Server linked tables.  

However, to really take advantage of SQL Server, you will need to rewrite many of those queries as pass-through queries or as views and stored procedures on SQL Server.
0
 

Author Comment

by:rayluvs
ID: 37021137
We decided to migrate to SQL for the features provided; Access just became to slow and troubleshooting & reporting is much easier in SQL.

Regarding Access queries, when migrated what will they become in SQL? or where can we find them after the migration?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37021599
>>We decided to migrate to SQL for the features provided; Access just became to slow and troubleshooting & reporting is much easier in SQL.<<
And why do you think that porting your MS Access database ("in SQL exactly and functional") going to be any better faster?  I suspect in fact, quite the opposite.

0
 

Author Comment

by:rayluvs
ID: 37021840
Good question.  Beside being a more robust database management system:

    1. We're planning to add 20+ users to access the application; thus more load/traffic
    2. The use of triggers & store procedures
    3. Also planning to DB for driven website
    4. Finally scheduled backups and maintenance since the DB will be growing

By indicating "in SQL exactly and functional", we mean in order to modify the VB apps to the new SQL DB with few modification to DB.  

The area we are planning to expand to is in the future.  We understand we can still work with Access but we prefer to start the migration now.  
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 156 total points
ID: 37021954
You are missing the point:  If you are going to port your application as is to MS SQL Server without taking advantage of any of the features available in MS SQL Server, you will get no improvement in performance and if anything it will actually be slower.

If you want to migrate your application to MS SQL Server you need to do it right: Re-analyze and re-write your app to take advantage of MS SQL Server functionality. Anything else and you are wasting your time.
0
 

Author Comment

by:rayluvs
ID: 37022041
You are correct we have to re-analyze and re-write our app to take advantage of MS SQL Server functionality; we do want to migrate MS Access to MS SQL the right way.  The information provided here by EE is essential in this process.  At this stage we are primary analyzing the DB phase.

We would like some input in some areas that we are still in the dark:

    - The table relationships established in the Access, in the migration, will it be applied to SQL
      accordingly (there are a lot of relationships)?
    - How are the queries migrated to SQL? (are they migrated as View tables?)
    - Access DB has a lot of saved queries, does this mean that prior running the MS SQL tool,
      we have to literally review and modify all the 200+ queries in the Access DB?
    - Also regarding Access queries, when migrated where can we find them in MS SQL after
       the migration?

Thanx
0
 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 612 total points
ID: 37022465
Might be worth a read of the guide here.  Obviously if you have SQL Server experience already, it's less relevant.

-Yes
-Depends
-Depends
-Depends

The last three can all be answered simultaneously.  As mentioned under the "Other Data Objects
", heading... "Queries" in Access are an umbrella for a range of functionalities.
They are both simple selects, parameterised or action.  These, naturally, have to be implemented differently on the server.

Consequently, even if you let the SSMA convert your queries - it can only do so to a limited extent.
There will very possibly be errors relating to the use of Access or VBA functions.  Parameter and action queries would have to become procedures (or UDFs in the former case).
So where they exist on the server would be a mixture of as Views or nowhere.
If you use a 3rd party tool such as MUST upsizer then you'll get more comprehensive results.  But still - your previous queries will be divided between Views and Procedures.
You'd need to work differently with the results of that, unless you're moving to an ADP (which can consume procedures more natively - i.e. without code.
You've some work ahead of you - there always is.

Cheers.
0
 

Author Comment

by:rayluvs
ID: 37023392
understood.

We are not frequent with Access, that is a lower reason for the migration; we're a bit more in SQL.

So when you say "simple selects, parameterised or action", that means that in Access, Queries are divided by "selects, parameterised or action" and by this, we have to review the queries prior running the Migration tool to determine how it will be migrated as Views or Store Procedure?

0
 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 612 total points
ID: 37023519
>> We are not frequent with Access, that is a lower reason for the migration; we're a bit more in SQL
I'd still suggest a read of the FAQ.  Just to see where it's coming from.  You can skip sections you're familair with (for example simple explanations about SQL Server.)

As for the location/extent of the query conversions... It depends on the migration tool.
As has already been said, you could migrate only the tables and leave all queries in the front end.  And then, progressively, convert those (manually most likely) to server objects (of the appropriate type) and making appropriate use of them from Access.
For example, if you had an action query you wanted to represent as a stored procedure (say to add a new record to a table) then you would not only need to create the procedure on the server, but also implement a method of calling it.  (This would invariably involve some code in the front end - either to adapt a passthrough query's definition and execute it or via some ADO code.)

There's every chance that, if you had such a query, you'd have had existing code to execute it.  Perhaps even returning a newly created autonumber value from that procedure.  That's the sort of thing the stored proc could do for you now - so you'd alter your code at that point to return that value from there.

If you allow the migration assistant to convert what it can then you'll get a head start - or a tool like MUST would go much further and create Views where appropriate and procedures for the rest (where possible).
0
 

Author Comment

by:rayluvs
ID: 37023551
Thanx you just finish shedding more light on this topic and more intrigues (since we're new to this).

We were planning to use the recommended link for the SQL Migration Tool, but you mention MUST.  Our experience has always been that non-MS Tool are better or more thorough than MS Tools.  Can this be said for MUST?
0
 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 612 total points
ID: 37023605
I don't think it's fair to say that MUST will be more comprehensive just because it's a non-MS tool.  It's been created by MS aficionados (an Access MVP among them).  It's just a dedicated tool which represents a lot of time and effort and so is sold at a cost, as opposed to the free tools provided by MS.
But yes, it is much more thorough - as you'd expect, given that it's a licensed piece of software.
They have demos on their website if you want to check it out. (I've not used it personally... just sayin')
0
 
LVL 1

Assisted Solution

by:Coolsoftware
Coolsoftware earned 304 total points
ID: 37023650
No automatic tool will produce results as good as a rewrite/hand migration.  

MSSQL is Client/Server.  Access is File-Based Client-Side only.  If you just push the existing Access DB to the SQL server using migration tools only you will get (Mostly) the advantages and disadvantages of Both Systems.

Certainly start with a migration tool.  But finish by going over both ends by hand and converting to stored procedures, views, Transactional code etc.

Prepared Stored Procedures with parameters will perform far better than ad-hoc SQL ever will.

Although the migration tool will provide for you, I prefer to enforce data integrity/Relationships (Like cascading deletes) by using hand coded triggers.


0
 

Author Comment

by:rayluvs
ID: 37024242
Yes thank you, our main concern is exactly that, relationships, store procedures, triggers, views, etc.

What do you mean by "cascading deletes"?
0
 
LVL 1

Assisted Solution

by:Coolsoftware
Coolsoftware earned 304 total points
ID: 37025304
Cascading deletes:  for example, when a customer record gets deleted, all their existing related orders go also.  That way, you end up with no "Hanging" or "Orphan" records.

Also It can really pay to have a look at the Traffic between SQL Server and Access when using linked tables.  There are tools in SQL management apps for spying.

Good luck!





0
 
LVL 22

Assisted Solution

by:dportas
dportas earned 304 total points
ID: 37025398
A database design made for a Jet/ACE database isn't necessarily suitable for SQL Server. A (small and incomplete) list of major differences that may catch you out:

Nullable unique indexes / constraints with nulls work differently in SQL Server
Nullable foreign key constraints work differently in SQL Server
Different limitations apply to cascading foreign key constraints
Triggers that modify data are usually best avoided in SQL Server (use regular stored procedures instead)
Data types are different
Tables are unordered so you can't reliably implement functions that depend on an unspecified ordering of data in tables (e.g., DLOOKUP, FIRST, LAST). Eliminate those functions and add extra attributes / keys to your tables if you need to.

The migration wizards won't pick up all these things. You should review the database design and redesign where necessary.
0
 
LVL 1

Expert Comment

by:Coolsoftware
ID: 37025445
Hey - Good answer.  
May I ask what's the issue with triggers changing data?

0
 

Author Comment

by:rayluvs
ID: 37027198
Agree with Coolsoftware, why avoid triggers that modify data?
0
 
LVL 22

Assisted Solution

by:dportas
dportas earned 304 total points
ID: 37030051
Triggers are seldom necessary because they don't do much that can't be done using a regular (non-triggered) stored procedure. They are a form of tight-coupling. They conceal logic and performance/blocking issues, make code harder to debug and can become very complex to maintain and administer over time (e.g. it is hard to disable a trigger selectively for some processes when requirements change).

A reasonable use of a trigger is to implement constraint-style business logic (not modifying data) that can't otherwise be implemented in a CHECK constraint. If you really feel you must use a trigger to modify data then put the trigger on a view, not a base table.
0
 

Author Comment

by:rayluvs
ID: 37030307
Understood.

Based on our need in migrating Access to SQL, should this be a concern?  I mean, are there Triggers to be migrated from Access?

Please advice.
0
 
LVL 1

Expert Comment

by:Coolsoftware
ID: 37031069
Access has no triggers, Ramante.

Really good answer dportas.  Taught me stuff.  Thank you.

0
 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 612 total points
ID: 37031107
It's unlikely you're using the data macros approximation to triggers in ACE introduced in 2010 (you'd be aware of it if you were).
So the chances are that there is nothing to migrate (not that they would at all) - but, to be fair, it was you who first mentioned trigggers in post #37021840. ;-)

Triggers operating on table data accessed via a linked ODBC table can, under certain circumstances, cause issues.  (For example Jet/ACE has no Scope_Identity function.  But, as mentioned earlier, you would use a dedicated stored procedure to retrieve that.)
0
 

Author Comment

by:rayluvs
ID: 37034636
Thanx all!  

We have began the process of evaluating all codes, structure, incorporating all of the recommendations placed in the questions.  We have a lot of work ahead :)
0
 

Author Closing Comment

by:rayluvs
ID: 37034661
Thank you all!!!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question