Converting an MS Access database to SQL or .net

Posted on 2013-05-29
Medium Priority
Last Modified: 2013-07-25
We have two access databases which have been separated into a front end and a back end. The back ends are between 5mb and 15mb and the front ends are 25mb and 37mb. Some of the people in the organisation now want to be able to access the database when they are working from home and as you know with ms access this is a problem.

I now need to move these databases to some other platform but as you can see the databases are very small and they are for a very small business so we do not have a lot of resources to spend on it. We currently have a small business server 2008, the one without an SQL license and total of 7 users.

1. What would be the best route to achieve what we want to do, can SQL Express work in this type of environment?
2. Is there an easy way of doing the migration I have access and vb knowledge nothing much else but am willing to learn what ever needs to be done.
3. Can we only migrate the back end and leave the front end in access?
Question by:Carolvara
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

Expert Comment

by:Ross Turner
ID: 39204002
Well a quick and dirty way... rather than do all that would be to allow your users to rdp into the server and access it directly off the box.

Would save you oodles of time

also depending on you version you could try


Expert Comment

ID: 39204046
For sql express to accept remote connection


For migrating the data

or use this tool

why do you need to leave the front end without migrating, I think it is improve to migrate to sql

Expert Comment

by:Ross Turner
ID: 39204055
you might want to read about microsoft access upsizing wizard

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!


Author Comment

ID: 39204058
Hi RossTurner

I don't really want them to have to switch between two screens which if I understand correctly they have to do with rdp. They would have to use the server screen for the database and do their other normal work on this machine screen am I correct?

Expert Comment

by:Ross Turner
ID: 39204071
Indeed... that would be case yes

i had an access database which... i used the rdp solution with for a while.

Then i tried vpn <---- bad bad idea because in turn it slow and connections drop and allsort of issues

in the end i built a php webpage that pointed to the data that had been upscaled to an ms sql database.

i left the front end in place for internal users and external users ended up using the php variation.

would you user just be reading the data or would they also need the ability to write data back ?

anyway... it going to be a pain and well from my experieince.... lots and lots of testing to get right

Author Comment

ID: 39204073
Thank you for all the links will go through them and get back to you.

nsonbaty I thought things might be easier if I didn't move the front end also, was hoping to reduce my work load. Will try and look at doing both.
LVL 85
ID: 39204111
I agree with RDP - it's by far the simplest way to share an Access database. Converting to web-based is a great idea, but can involve some significant resources.

If you use RDP there's really no reason to move to a different data platform. SQL Server is great, but if your goal is to allow off-site users to use the database then all you really need to do is setup a Terminal Server and allow users to remotely connect.
LVL 58
ID: 39204365
<<I now need to move these databases to some other platform but as you can see the databases are very small and they are for a very small business so we do not have a lot of resources to spend on it.>>

 Besides RDP, I would also look at:


 It is a subscription service however, but with only a few users working remotely, it may be very cost effective compared to anything else.


Author Comment

ID: 39204762
Hi JDettman

I had a look at www.eqldata.com and I think its a wonderful solution but I think I would be better off buying SQL Server 2012 Standard Edition @ $898 per server and 6 cals @ $207 each. EQL charges $35 per user per month per database, which works out to $1680 for only 4 users per year for 1 database. For $400 more I can buy the SQL software and might not even need to upgrade for a long while.

Both databases will be able to work on that software.
LVL 58
ID: 39205254
<<EQL charges $35 per user per month per database>>

 Keep in mind that is concurrent users.

<<SQL Server 2012 Standard Edition>>

  Why buy SQL Server?  Your DB is so small, the express edition would work, which is free.

<<For $400 more I can buy the SQL software and might not even need to upgrade for a long while.>>

  But the piece that will cost you is modifying the app to work over an internet connection, SQL server or not.    For over the internet use without a point to point connection, moving data into SQL is just the start.  

  Fundamentally, apps that run over the internet are developed differently.  For example, in Access, you typically bind a form to a table directly.   You would not do that with a true client/server app.

 So depending on the complexity of the app and what it takes to get it working well, you could spend far more overall.

  Personally it sounds like you should go the Terminal Services Server/RDP route.  That means no changes to the app.   You just need a cheap server, Terminal Services, and some Cals.

LVL 21

Accepted Solution

Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 1500 total points
ID: 39206746

...Some of the people in the organisation now want to be able to access the database when they are working from home...

Do they have a desktop PC at the Office? If yes, they can use SBS Remote Web Workspace (RWW)  to connect to their office PC and work just like they were in the Office. No need to set up a terminal Services server.   I have lots of client that work from home at night and weekend using SBS RWW.

Author Comment

ID: 39206836

I do use SBS RWW, but we are in the process of doing away with desktop PC's so everyone will be carrying their laptops home. The guys who really need the database don't have desktop PC's anymore.

I had been hoping that switching to sql could help us use the database via vpn or something without having to switch screens but the more I read comments here the more I think I should just stick to access and terminal services.
LVL 15

Expert Comment

by:Simon Ball
ID: 39206992
Does everyone have access to the web?  As your F/E db is tiny I assume there is not much going on in the front end?

If your business is growing it might be worth moving to a web front end and cloud hosted database solution.

Thats a big leap in terms of tech, hosting and way of working, but you might end up doing it anyway if you keep getting more staff etc.
ID: 39208261
Hi Carolvara,

From everything I've read above, I'd say that your two viable approaches are:

1.  Keep everything exactly as is (Access + Access) and use remote desktop to allow your users to run the apps from anywhere.  Yes, it will be running in another desktop window, but it should coexist well with their local work as long as the Access apps don't interface with other applications like Word, Excel or Outlook.  There may be some remote desktop licensing you'll need to purchase if your SBS doesn't include it.

2.  Migrate the back-end database to SQL Server Express.  It's free and will be sufficient for you, with more horsepower than your current Access back-ends.  You don't need to buy a higher version of SQL Server.  You can migrate the tables using SQL Server Migration Assistant and reconnect the applications to the new SQL Server database.

There are few things that you might need to change in the Access front-end applications to make them work properly, and other changes to make them run faster.  We build most of our Access applications using this architecture.  I've documented our techniques in our free download called "Best of Both Worlds" at www.JStreetTech.com/downloads.

After this is all done, your users will be able to run the Access applications locally on their PC, with the apps being linked to your SQL Server database at your office.  Alternatively, you could even host the SQL Server in a data center - some of our client use this option because they don't want to maintain physical server hardware, routers and firewalls.

Hope this helps,
Armen Stein

Author Comment

ID: 39210020
Hi Armen

The second option is what I was hoping I could do, I was starting to lose hope and was going to resort  to option 1 but I think I will now give it a go, downloading SQL Server Express as I type. I had a look at you site very impressive tools, will definitely come in handy.

Thanks a lot.
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39210511
I had a look at www.eqldata.com and I think its a wonderful solution but I think I would be better off buying SQL Server 2012 Standard Edition @ $898 per server and 6 cals @ $207 each.
The part that you are seriously overlooking is your experience with SQL Server.  This is not a trivial change and depending on your level of experience you could be spending many hours implementing the changes and maintaining it going forward.

Author Closing Comment

ID: 39353886
Thank you all for your advice, after looking at all the option, I have decided to stick with RWW because I realise that at the moment I do not possess enough skills to go the SQL route. I will get training in SQL and hopefully use some of your advice at a later stage. I will have to leave a desktop or two in the office for that purpose alone.

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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 …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

650 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