Solved

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

Posted on 2011-02-28
8
308 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
[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
8 Comments
 
LVL 58

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
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
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
 
LVL 85

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
When using a search centre, I'm going to show you how to configure Sharepoint's search to only return results from the current site collection. Very useful when using Office 365 with multiple site collections.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

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