Solved

Access conversion to SQL Server

Posted on 2013-01-28
15
195 Views
Last Modified: 2013-04-29
I need to convert my Access database backend to SQL Server.
This include Tables and queries.

Can someone tell me the process? Or if there is more than one way or doing it?
0
Comment
Question by:Idarac
  • 5
  • 5
  • 2
  • +3
15 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 250 total points
ID: 38827715
<somewhat simplistic, low-tech approach>
(1)  Open up SQL Server Management Studio
(2)  Right-click on the target database, select Tasks ... , Import Data
(3)  Follow the wizard to import the Access tables into SQL Server.
(4)  Make a backup of your Access app.
(5)  In Access link tables to the target SQL tables, delete local tables, rename linked SQL tables to what your local tables used to be named.
0
 
LVL 3

Assisted Solution

by:Nalinkumarbalaji
Nalinkumarbalaji earned 250 total points
ID: 38827717
Hi,

If you are exporting the data only once then follow the below one Otherwise follow second one.

1) Export the Access data into SQL by selecting the MSAccess as the Source Data. Give the Table name as the Excel Sheet Name.

2) You have to write a coding (eg: Using .NET, C#) to port the data from MSAccess to SQL by using the Stored Procedure in the SQL. For that you should create the schema first in the SQL Database and the Insert Storedprocedure ready.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 38827867
You should bear in mind that Access does not impose any constraints which might prevent you from upsizing.

So it is quite easy to name objects in Access in such a way that they cannot be upsized to SQL Server.  Recent Access versions have datatypes that cannot be upsized to SQL Server.

This is quite apart from the fact that you might have to do design changes to your front-end to make your app work with a sql server backend as there are objects such as crosstab queries which cannot be upsized, and any queries which use form references for parameters cannot be upsized.

So you should approach this as some sort of casual move to a different backend, but rather prepare for it to be a sizable project.  If it turns out to be straightforward then great.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38829418
And of course it goes without saying that if you are doing this because MS Access is "too slow"  than you will be frustrated and disappointed with the outcome, if you are not prepared to redesign and rewrite your app to take advantage of SQL Server.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 38829458
Idarac - Building on Anthony's point, if you could tell us what issues you're having with your app that is causing the question, perhaps we can provide a more direct solution.
0
 
LVL 9
ID: 38833173
We recommend SQL Server Migration Assistant (SSMA) to actually migrate the structure and data from Access to SQL Server.

After the data is migrated, the Access front-end will need attention.  I've written a PowerPoint presentation on techniques for using Access as a client-server front-end to SQL Server databases.  It's called "Best of Both Worlds" at our free J Street Downloads Page:

http://www.JStreetTech.com/downloads

It includes some thoughts on when to use SQL Server, performance and security considerations, concurrency approaches, and techniques to help everything run smoothly.

Cheers,
Armen Stein
0
 
LVL 1

Author Comment

by:Idarac
ID: 38836111
Hi Everyone and thanx for all the input. I have a client that wants to convert their Access database to SQL Server. The goal is to get rid of all things Access. So initially we will convert the database then begin converting all the forms etc to something like VB.net or C#.

I don't actually have my hands on the database yet but I am supposed to get sometime today or tomorrow. I was looking for some answer on converting so I could more reliably quote on the conversion.

So its not really the app in questions its just the need to convert.  The heartache will be converting the Access forms and reports over to something else.

So I need a bit of time to digest all that you have presented and once I get the database I will trying out the various techniques
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 65

Expert Comment

by:Jim Horn
ID: 38836141
First question I'd ask is why do they want to convert out of Access?  
Performance issues, data security, scalability, deployability, rumors about Access, they want to run away from their crappy database/code and have someone else clean it up, ...

Their reasons may or may not justify the price tag.

I think your original question of 'How to migrate from SQL to Access' has been nailed by multiple experts with multiple ways of doing it.

When you know more, feel free to post follow-up quesitons on it.
0
 
LVL 1

Author Comment

by:Idarac
ID: 38849336
The one questions I did not take into consideratikon is conversion of the queries which will be a lot of work. Is  there something that will also convert the ACcess queries.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 38852653
afaik there is no automated tool to convert Access queries to SQL Server views.

Having said that though, views, functions, and stored procedures offer way more functionality than the Access query, so it's worth having an experienced SQL developer look at your app and see what kinds of opportunities there are for optimization.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38853401
Is  there something that will also convert the ACcess queries.
If all you do is convert the exact same MS Access queries and tables to SQL Server, you will missing the boat and your client is going to blame you for the lousy performance.  Again, unless this is a trivial application, you need to rid the notion that you are going to do an "upgrade" to SQL Server and everything will be peachy.  What you are going to do is migrate the data from MS Access to a SQL Server schema and code that will take advantage of the functionality on that new platform.  That will require changes not only in the queries, but also in the structure of the TABLEs and VIEWs.  Anything else and you are going to be in for a world of pain.
0
 
LVL 1

Author Comment

by:Idarac
ID: 38855202
OK I was hoping for an easy way out with the queries. "and there are a lot of them"
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 38855372
No, in this situation there is no button that says 'Convert my Access queries to SQL queries and gain lots of performance increases.'

As multiple experts have stated, you'll need some solid SQL experience to gain the benefits of having SQL views/functions/procs as your data source, and if you don't have it you'll have to go buy it.
0
 
LVL 1

Author Comment

by:Idarac
ID: 38855454
I am pretty good at SQL Server but I guess just a bit lazy. Thank you everyone for all you input now I have to figure how to score this one....
0
 
LVL 1

Author Closing Comment

by:Idarac
ID: 39120978
Thank you both for your answers
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

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…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
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 …

760 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

23 Experts available now in Live!

Get 1:1 Help Now