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

Converting an MS Access database to SQL or .net

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?
1 Solution
Ross TurnerManagement Information Support AnalystCommented:
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

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
Ross TurnerManagement Information Support AnalystCommented:
you might want to read about microsoft access upsizing wizard

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

CarolvaraAuthor Commented:
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?
Ross TurnerManagement Information Support AnalystCommented:
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
CarolvaraAuthor Commented:
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.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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.

CarolvaraAuthor Commented:
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.
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:

...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.
CarolvaraAuthor Commented:

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.
Simon BallCommented:
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.
Armen Stein - Microsoft Access MVP since 2006PresidentCommented:
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
CarolvaraAuthor Commented:
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.
Anthony PerkinsCommented:
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.
CarolvaraAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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