Solved

Converting an MS Access database to SQL or .net

Posted on 2013-05-29
17
248 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
  • 6
  • 3
  • 2
  • +6
17 Comments
 
LVL 7

Expert Comment

by:Ross Turner
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:Carolvara
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:Carolvara
Comment Utility
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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Expert Comment

by:Armen Stein - Microsoft Access MVP since 2006
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now