Learn how to a build a cloud-first strategyRegister Now


Migrating from MS Access to SQL server

Posted on 2008-11-09
Medium Priority
Last Modified: 2012-05-05
I need some coaching in how to get started with SQL.

I would like to migrate an Ms Access database to SQL for hosting on the web.
The application currently works with the Ms Access backend but I've been told that it will be sturdier if the tables are converted to SQL.
I don't know where to start.
I have looked on the web and know that this is an expensive product, but there seem to be a variety of versions from Express which is free but has limitation; I can't tell from what I have read whether express will work for my application.
I'm looking for someone who will have an exchange of postings so that I can figure out what I need to get and where to go from there.
Question by:dhapp
  • 2
  • 2

Expert Comment

ID: 22917006
The SQL server is naturally a good replacement to access, it's easy to import the access database into the SQL server using DTS or SSIS and it is naturally much more scalable when large amounts of requests and data is involved.

There is one problem though, you are talking about SQL Express, which comes with none of the above mentioned import/export functionality, making the job of migrating potentially alot more difficult.

You should look at this article: http://blogs.msdn.com/euanga/archive/2006/07/20/672272.aspx

Author Comment

ID: 22917216
I looked at the article and what I think that I got from it is that the data conversion is possible.
What I have to weight is the difficulty that you mention versus the price of SQL standard version (approx $1400).  Why isn't there something more reasonably priced?
I'm not sure if this is relevant:  the app that I have built will be hosted on the web and may get a a large number of hits in the first few minutes that it is available (perhaps 100 hits).
If I were to get SQL Express and could import the data would the resulting backend support the stress of the many hits?

Accepted Solution

stesom earned 1500 total points
ID: 22918016
SQL Express is great, I use it all the time for small business applications and I've also used it occasionally as a web backend. Basically, it performs as well as the standard edition up until a certain point, when the sql express limitations kick in. These being:

1. Max 4 GB individual database size.
2. It will only utilize 1 GB of memory.
3. It will only utilize 1 processor.

For any website/app I'd say SQL Express will be more than capable, and in any case you will be much better off than with Access as the backend. Wether the above limitations will prove a bottleneck depends on how dataintensive your webapplication is, but it is very easy to upgrade SQL Express to standard or enterprise edition later on if necessary.

To answer your last question, regarding migrating your access database without the import tools of SQL server standard edition: Again it depends on how complex your access database is, but I'm sure I'd save the money and code the conversion myself. From what I've read, Access might even be able to do it for you (bottom of the previous article I linked to).

Author Comment

ID: 22918191
So I have downloaded SQL Express and am trying to install it.
I found the SQL Server Installation Center and clicked on the System Configuration Checker with shows a dialogue box to Browse For Folder
I can't figure out which folder to choose.  I have tried many different folders and continue to get the Error box that indicates "C:\WhateverFolderIsSelected is not a valid installation folder."
Can you help?  (see attached graphics)


Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

810 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