Solved

Access conversion to SQL Server

Posted on 2013-01-28
15
235 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
[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
  • 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
Industry Leaders: 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!

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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

Suggested Solutions

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.
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
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.

734 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