Solved

Converting an MS Access database to SQL or .net

Posted on 2013-05-29
17
286 Views
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?
0
Comment
Question by:Carolvara
[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
17 Comments
 
LVL 7

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

http://office.microsoft.com/en-gb/access-help/build-an-access-database-to-share-on-the-web-HA010356866.aspx
0
 
LVL 6

Expert Comment

by:nsonbaty
ID: 39204046
For sql express to accept remote connection

http://blogs.msdn.com/b/sqlexpress/archive/2005/05/05/415084.aspx

For migrating the data

http://forums.asp.net/t/935914.aspx/1
or use this tool
http://www.spectralcore.com/fullconvert/adwords?source=acc2sql&kw=Access%20to%20sql%20express&adsrc=google-cpc-search&reset=1&gclid=CISNtuqGu7cCFUfKtAodKk8Ahg

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

Expert Comment

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

http://office.microsoft.com/en-gb/access-help/use-the-upsizing-wizard-HP005273009.aspx
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:Carolvara
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?
0
 
LVL 7

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
0
 

Author Comment

by:Carolvara
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.
0
 
LVL 84
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.
0
 
LVL 57
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:

www.eqldata.com

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

Jim.
0
 

Author Comment

by:Carolvara
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.
0
 
LVL 57
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.

Jim.
0
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 500 total points
ID: 39206746
Carolvara,

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

Author Comment

by:Carolvara
ID: 39206836
TheHiTechCoach

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.
0
 
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.
0
 
LVL 9
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
0
 

Author Comment

by:Carolvara
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.
0
 
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.
0
 

Author Closing Comment

by:Carolvara
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.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

739 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