[Last Call] Learn how to a build a cloud-first strategyRegister Now


Access database - desktop and "cloud"

Posted on 2014-04-02
Medium Priority
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 11

Assisted Solution

HuaMinChen earned 568 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 84

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 284 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 284 total points
ID: 39973996
Technology Partners: 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!

LVL 11

Assisted Solution

HuaMinChen earned 568 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 288 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 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 288 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 www.eqldata.com 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 85

Assisted Solution

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

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

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
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 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 …
Suggested Courses

834 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