Solved

Migration of an Access Database to a Sql server Database

Posted on 2014-10-01
7
250 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 48

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 54

Assisted Solution

by:Joe Winograd, EE MVE 2015&2016
Joe Winograd, EE MVE 2015&2016 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 66

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 50

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 38

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Viewers will learn how the fundamental information of how to create a table.

695 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