Solved

Migration of an Access Database to a Sql server Database

Posted on 2014-10-01
7
245 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 52

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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 35

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

785 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