?
Solved

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

Posted on 2011-02-28
8
Medium Priority
?
309 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 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:

 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 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.

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 IT Pros

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

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.
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 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...

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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.
Suggested Courses

766 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