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

Posted on 2011-02-28
Medium Priority
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!
Question by:hrdf-pipkin
LVL 59

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 80 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:


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

LVL 10

Assisted Solution

ALaRiva earned 80 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.


- Anthony

Author Comment

ID: 35002674
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.

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

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.


Assisted Solution

sunezapa earned 160 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...
LVL 86

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 80 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.

Author Comment

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.

Assisted Solution

sunezapa earned 160 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...


Author Closing Comment

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.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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 …

624 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