• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

Access conversion to SQL Server

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
Idarac
Asked:
Idarac
  • 5
  • 5
  • 2
  • +3
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<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
 
NalinkumarbalajiCommented:
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
 
peter57rCommented:
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
Independent Software Vendors: 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!

 
Anthony PerkinsCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Armen Stein - Microsoft Access MVP since 2006PresidentCommented:
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
 
IdaracAuthor Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
IdaracAuthor Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Anthony PerkinsCommented:
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
 
IdaracAuthor Commented:
OK I was hoping for an easy way out with the queries. "and there are a lot of them"
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
IdaracAuthor Commented:
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
 
IdaracAuthor Commented:
Thank you both for your answers
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 5
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now