?
Solved

Migration of an Access Database to a Sql server Database

Posted on 2014-10-01
7
Medium Priority
?
254 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 336 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
Dale Fye earned 332 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 55

Assisted Solution

by:Joe Winograd, EE MVE 2015&2016
Joe Winograd, EE MVE 2015&2016 earned 332 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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 332 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 51

Assisted Solution

by:Gustav Brock
Gustav Brock earned 332 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 336 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

Technology Partners: 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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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 …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

762 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