Access database - desktop and "cloud"

Posted on 2014-04-02
Last Modified: 2014-11-12
I've been presented with an opportunity in developing an Access database for the desktop with the possibility of having it also on the Internet.
For the desktop and land platform - no problem. However, I HAVE NEVER been approached to have the database also accessible from the Internet.
My question what are some of the gotchas I may be facing? Can this be done? Any additional software? I just don't know.
Question by:Frank Freese
  • 3
  • 2
  • 2
  • +5
LVL 10

Assisted Solution

HuaMinChen earned 142 total points
ID: 39973936
You can set up Win 2008 server together with Sql server express inside. Then such database can be available on the internet.
Take Sql server express below
LVL 83

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 71 total points
ID: 39973986
Very few web hosting companies support Access databases and those that do require indirect methods to access them so that people can't just download the entire database file.  SQL Server as suggested above is a much better method since it is a real 'server'.  However, you would have to create a front-end program that accesses the database since SQL Server does not have such a thing built in.
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 71 total points
ID: 39973996
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

LVL 10

Assisted Solution

HuaMinChen earned 142 total points
ID: 39974004
Microsoft Azure can also be your option.
LVL 75

Accepted Solution

DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 72 total points
ID: 39974151

You are going to want to consider Access 2013 and Web Apps. You can build a web UI which will automatically be connected to a real SQL Server (SQL Azure) database (not Express) managed my Microsoft.

Further, on the desktop side of A2013, via VBA code, you can link to that same SQL Server back end and build and have yourself a tool to administer the SQL back end or whatever you see fit.

There are two outstanding books that have a LOT of content on A2013 Web Apps that will get you up to speed quickly:

Microsoft Access 2013 Inside Out

Professional Access 2013 Programming

And in fact, here is a tutorial on how to link to the SQL back end as I mentioned above:

How to: Make external connections to an Access Web App

I have both books and I've tested out the linking.

LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 72 total points
ID: 39974676
The other method not mentioned is to use terminal services.   It's important to understand that Access DB's now come in two flavors: Desktop and Web Apps.

"Desktop" is what we've always worked with as "Access".   You can develop a desktop app as always, and then with either or terminal services, make that app with little modification available to internet users.

Down side with eql is cost and reliance on a 3rd party, and with TS is that it is costly to scale up to large number or users (i.e. hundreds).
Access Web Apps can be offered to a number of users easily, but there are downsides:

1. It requires a Sharepoint Enterprise license.  That's either expensive if you run Sharepoint on premise, or you need to get Office 365.

2. Web apps at this time are very limited; there is no VBA or coding language other then macros.  The types of forms and controls is limited.  There is no reporting either, so you will need to develop a desktop app to go along with it and do a "hybrid" design.

 And of course there is just scrapping Access and using something like ASP.Net and do a totally web based app.

 So what it boils down to is the number of remote users.   If somewhat low, you can do a desktop app and use Terminal services.   If your expecting more remote users, then eql data service with a desktop DB, or dive into web apps (assuming you can do what you need to in a web app).

If hundreds of users, then I would not be thinking about Access.

LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 72 total points
ID: 39974692
If you're looking to create a true web application then Access isn't really the best choice for that. As mx said, 2013 has Web Apps, and they're better than anything else Access has done web-wise, but there are still far too many limitations if you really want a web-based application and not one that simply links to a web-based server. Don't get me wrong - they're getting much better, and better able to handle more robust applications, but they've still got a ways to go.

In my opinion, if you want to create a web app, then use traditional web languages like PHP, ASP.NET, etc etc.

Author Closing Comment

by:Frank Freese
ID: 39974951
fantastic! wish I could give all 500 points. this is awesome and I do appreciate it - have a great day
LVL 36

Expert Comment

ID: 39974987
with the possibility of having it also on the Internet

For what purpose?  Who will be the users?  Do you need to allow anonymous users or will every user be known?  What functions to the remote users need?  Is Citrix/RDP an option?  Will you host the app yourself or will you host it at a service bureau.  By "Access" do you mean that you want an ACE database as your BE or you want an Access FE?

You cannot make any decision on a course of action with that fuzzy description.  People say this all the time because they think that web apps are some how cool and therefore better than client server apps because they are the latest fad.  But if all your users are on your LAN, a web app has no advantage and in my opinion has disadvantages.  One of which is cost.  The cost to develop a web app is significantly higher, usually 3 or more times more and will also take longer than developing an Access client/server app.

If you want to use Access 2013 to develop a web app, you will also need either the latest version of SharePoint running in house or you will need a subscription service.  In this case the BE will be SQL Azure, it will NOT be ACE.  Access web apps do not support "Access" databases!  Your outside users will also be limited so this is not a good option for a public facing app.  You also need to keep in mind that Access web apps are limited in functionality.  They are restricted to using macros only.  No VBA or other programming language is supported.  There are other limits to what you can do.  No reports for example - NO a web page that goes on forever is NOT a report.  So before you get into this make absolutely sure that you can do everything you need to do now and for the foreseeable future.

With an Access web app, you can create a hybrid application where you have a traditional client/server database that includes some web forms published with SharePoint and they all connect to the same SQL Azure BE.  If you go the Access web app route, this is probably the most useful option.  It will allow remote access to certain features but still retain the user-friendliness of a client/server app for most functions.  Don't plan on converting an existing application to a web app.  You must build them from scratch although in a hybrid, you can start with a converted app and add the web features.  You may need to change the schema though because SQL Azure does not support any of the new data types introduced with A2007 such as attachments, memos, and multi-value fields.  SQL Server also does not support hyperlink type fields.

My configuration choice for applications that are used primarily on a LAN but that have some remote users is Citrix.  The technology is very stable and doesn't require any changes to the Access app.  And, because of the way Citrix works, the Citrix users frequently experience better performance than the LAN users.

Author Comment

by:Frank Freese
ID: 39975099
The points you make are great! You're correct about "fuzzy". I have not interviewed the client yet and what the agency sent from the client was vague. They are referring to MS Access 2007 as the development tool and I would challenge that from the very beginning. From what the agency has said the client believes that it should not be a big deal to develop the database then post it to the Internet. Oh, if only that was true. The client also believes that little to no code is necessary. Makes you wonder who they've been talking too? Makes me wonder if I want this assignment?
Thank you
LVL 84
ID: 39975195
Makes me wonder if I want this assignment?
From what you said, your best course of action would be ... run fast, and run far.

LVL 36

Expert Comment

ID: 39975229
Or at least make it time and materials.  Don't even consider fixed price.

Citrix is frequently the best option when you have remote users.  I had one app that ran in Farmington Connecticut and had Citrix users from San Francisco to London and all were happy with performance.

Author Comment

by:Frank Freese
ID: 39975258
Time and material is the only way I'd go with this -

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

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…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

733 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