Solved

Access database - desktop and "cloud"

Posted on 2014-04-02
13
412 Views
Last Modified: 2014-11-12
Folks,
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.
0
Comment
Question by:Frank Freese
  • 3
  • 2
  • 2
  • +5
13 Comments
 
LVL 10

Assisted Solution

by:HuaMinChen
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
Link
0
 
LVL 82

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

Assisted Solution

by:Rey Obrero
Rey Obrero earned 71 total points
ID: 39973996
0
 
LVL 10

Assisted Solution

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

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 72 total points
ID: 39974151
fh_freese

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.

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

Jim.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

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

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
0
 
LVL 34

Expert Comment

by:PatHartman
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.
0
 

Author Comment

by:Frank Freese
ID: 39975099
Pat,
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
Frank
0
 
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.

:)
0
 
LVL 34

Expert Comment

by:PatHartman
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.
0
 

Author Comment

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

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Monitoring systems evolution, cloud technology benefits and cloud cost calculators business utility.
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
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…

705 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

19 Experts available now in Live!

Get 1:1 Help Now