Solved

Migration of an Access Database to a Sql server Database

Posted on 2014-10-01
7
240 Views
Last Modified: 2014-10-02
What is the best way to move an Access Database to  a SQL Server Database?
0
Comment
Question by:metro156
7 Comments
 
LVL 22

Assisted Solution

by:Kelvin Sparks
Kelvin Sparks earned 84 total points
ID: 40355599
Use the SQL Server Migration Assistant from Microsoft. Is far more reliable than the Access built in tools.

http://www.microsoft.com/en-nz/download/details.aspx?id=42656

Kelvin
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 83 total points
ID: 40355617
For getting the data structure and data from Access to SQL Server, I would totally agree with Kelvin.

But moving your data to SQL Server is only the start.  Although you can use linked SQL Server tables in your Access application, with very few changes to your application, to really take advantage of SQL Server, you will want to change Access queries to SQL Server views or Stored Procedures and take advantage of pass-thru queries.

In my experience, the major change you will need to deal with is that most of your recordset and action query operations will need to include the dbSeeChanges value added to the Options argument.  I generally just create a TempVar like:

Tempvars!SQLOptions = dbSeeChanges + dbFailOnError

And use that in the options argument when I open a recordset or execute a query.
0
 
LVL 51

Assisted Solution

by:Joe Winograd, EE MVE
Joe Winograd, EE MVE earned 83 total points
ID: 40355643
I recommend reading Jim Horn's EE article, Migrating your Access Queries to SQL Server Transact-SQL. The article title is a bit misleading, as it also discusses converting the Access tables to SQL Server (as well as converting the Access queries To SQL Server). Regards, Joe
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 83 total points
ID: 40355669
^^^ Holy Cow, I am somebody...

To build on the above experts' excellent comments, paint for us a picture of what you mean by 'move'.   Move all tables, and link back to Access?  Any file import/exports going on?  Web access?  Encryption/security?  You get the idea..
0
 

Author Comment

by:metro156
ID: 40355702
What I am going to do is migrate the access database data to a sql server database.

Also, the access database as attached Access screens with functionality  to update the database. My client wants me to migrate  the access data to a sql server database  and create a c# windows client  to call store procedures that mimic the functionality on the access database screens.
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 83 total points
ID: 40356422
> .. and create a c# windows client  ..

That turned the picture. Then you need Visual Studio. The free Express version for desktop applications will do.
WinForms is probably what to use to "mimic the functionality on the access database screens".

One advice: Forget everything about the low-level SqlCommands and move to DataTableAdapters for "talking" to the database.

Another option is to use LightSwitch:

    http://msdn.microsoft.com/en-us/vstudio/lightswitch.aspx

You cannot directly mimic Access forms with this, but it is much faster to work with.

/gustav


/gustav
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 84 total points
ID: 40357308
Does the client realize that he can move the data to SQL Server but leave the FE as Access?  Access is a great FE for SQL Server so unless there is some specific reason to convert the FE to C++ such as he has an ardent desire to burn thousands of dollars, I wouldn't recommend it.

People who don't understand Access frequently recommend rewriting an app when converting to SQL Server but generally that isn't necessary.  Although, if the Access app was written with "Access" techniques rather than "Client/Server" techniques, you'll have to make some form modifications to ensure that each form only brings up 1 or just a few records.  The old "Access" technique of binding a form directly to a table or to an unrestricted query and using filters to get to specific records doesn't cut it once you move to SQL Server.  In fact, an app written that way will perform poorly against SQL Server compared to how it performed against Jet/ACE.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

706 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now