Solved

What's the best setup for hosting data tables remotely and a DB app locally?

Posted on 2011-02-28
8
303 Views
Last Modified: 2012-05-11
Hey all.  Looking for some advice on how to handle a situation.  I oversee collecting payroll information from about 30 field offices around the state.  We're grant funded, so we have all kinds of crazy grant requirements regarding the level of detail we have to keep on where people are charging their time.

I've built an Access 2007 database which does the job beautifully.  Now, I need to figure out how to distribute it to everyone.  I'm envisioning a system where the data tables are hosted on a web server, and the rest of the app (forms, queries, etc.) is installed on each user's computer.  I don't want them to have a separate login for the remote server, if possible.  I'd like the app to run seamlessly, as if all of the data were right there on the local computer as well.

I've played around a bit with SharePoint, but I don't think that's going to work.  It asks for a SharePoint site login every time I access a table in the database for the first time.  I work with a lot of barely computer literate people, so simplicity is a must.

Would it be better to host the tables on a SQL server, or even to redesign the whole app so that all of the forms are Access Web Forms, and host the entire thing online?  Any suggestions on how to get this thing up and running will be much appreciated.  Thanks!
0
Comment
Question by:hrdf-pipkin
8 Comments
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 20 total points
ID: 35002023
<<Would it be better to host the tables on a SQL server, or even to redesign the whole app so that all of the forms are Access Web Forms, and host the entire thing online?  Any suggestions on how to get this thing up and running will be much appreciated.  Thanks! >>

  Both of those are options.  A more "plug and play" operation is to use a terminal services server, let the app run locally on that and have everyone remote in.  Citirx on top of that makes it very flexible in regards to printing and accessing through a web browser if you don't have a VPN point to point or don't want to mess with VPN's.

  Another option is this:

 www.eqldata.com

  Don't know of anyone that's used them yet, but they've been around a few years now.  

JimD.
0
 
LVL 10

Assisted Solution

by:ALaRiva
ALaRiva earned 20 total points
ID: 35002033
You won't be able to host an Access Database with remote user locations.

For simplicity, your best bet would be to purchase a hosted sql server that you could Upsize your Access Tables/Queries to and then be able to just change your connection to the hosted SQL Server.

hth

- Anthony
0
 

Author Comment

by:hrdf-pipkin
ID: 35002674
@JDettman:
We have a TS server.  However, running this through TS would involve setting up users for all of our employees on our main office network, so that they could all log in to the TS.  We only have 30 user CALs,  and about 110 employees outside the main office.  I know that as of Server 2008, CALs are now on the honor system (I could set up 140 users with only 30 CALs), but it seems like that is just pushing it a bit too far.  I'll check out eqldata.

@ALaRiva:
I thought that might be a good option.  I'll look into hosted SQL servers, too.

Thanks.
0
 
LVL 3

Assisted Solution

by:sunezapa
sunezapa earned 40 total points
ID: 35002728
Hosted webplaces offers SQL-server or  MySQL-server, and both of these should be able to used as Backend, linked with IP ad servername, databasename and login/password, via ODBC connection to the frontend?

If the frontend is not to advanced, I would program a ASP/.Net/PHP webbased user-interface, making it easy available from "everywhere"...

The VPN/Citrix/TerminalServer solutions are also easy available, but costs some hardware (and proably software) and is maybe a bit overdoing if it is just a smaller need...
0
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

 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 20 total points
ID: 35003449
Just remember that simply migrating your backend to SQL Server is not a "magic pill", and you'll still have quite a bit of work to do if you want your datbase to work well across those remote connections. You'll want to explore the use of Views and Stored Procedures, as well as PassThrough Queries in Access.
0
 

Author Comment

by:hrdf-pipkin
ID: 35008548
I might play around a bit with designing a web interface, but the SQL Server option sounds more powerful and flexible.  Anyone know any good reference material for a first-time SQL Server user?  I currently have this, but there's not a lot in it about migrating to a SQL server.
0
 
LVL 3

Assisted Solution

by:sunezapa
sunezapa earned 40 total points
ID: 35008870
The work will not be to deal with the SQL-server, the challenges will be in  designing Access to deal with a "long-away" backend.
If you use web-interface the database and userinterface is on same intranet = fast.
If you use TerminalServer/VPN same speedy combination.
If you use access frontend and SQL-backend there will "internet-distance" delay., and will not use record-sources connected direct to the database, but get data by connections. If data is bound to the form it will be to slow, as access tends to load "all records" everytime you maybe just need a single one, incl. in searches and whatever...

The SQL-database compared to Access is no big difference, - you may need to  re-code some date-formats(!), and a few SQL functions, but you'll find help for that when you meet it, googling...
The handle of the SQL-server will be using SQL-statements, so you don't need to look into SQL-Server management.
Actually - for a simple web-application, I will go for a Access as Backend, as this is much easier to design and backup by simple FTP-upload of a local copy. SQL as backend is nice for high-performance and/or BIIG databases, but difficult to backup and manage, on a hosted web...

0
 

Author Closing Comment

by:hrdf-pipkin
ID: 35010551
Great, thanks for the tips everyone.  This will give me plenty of jumping-off points, and I imagine I'll have the chance to learn a bit along the way.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

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 this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

746 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

14 Experts available now in Live!

Get 1:1 Help Now