Solved

Migration of an Access Database to a Sql server Database

Posted on 2014-10-01
7
248 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 53

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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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 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 36

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: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

733 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