Solved

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

Posted on 2011-02-28
8
304 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

In this article I will describe the Backup & Restore 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.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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…

911 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

20 Experts available now in Live!

Get 1:1 Help Now