Publishing access database on web, how and which way is the best?

Hello,

I have small program in access database which contains about 100 cca tables, querys, forms, macros in total. Customer which both program from me, wants me to enable him to access this program not just from work, it wants to access it from home or when he is on business trip from hotel etc.

I am little confused how to achieve this and what are my options? I assume I have to set by database somewhere on server and so my customer could access it any time? Do I have to upsize access database to SQL express or similar?

Also, one more important question: is it possible to "convert" access database into HTML code or similar so it could be accessed via web browser without need to install microsoft access and with freedom that it can be opened from any type of PC OS (Linux, Mac etc)?

Any help is appreciated and many thanks in advance!

Adi

There is no conversion path from where you are now to the web.

1. If you want to convert to an Access 2013 Web application, you will need to RECREATE EVERYTHING except tables (those can be upsized - whoopty do). Forms are completely different and have different events. Reports are not possible at all. And VBA is not supported so your only option is macros which will probably not support all your existing functionality. And on top of that you need to purchase and install and maintain SharePoint or have it hosted for you at a service bureau.

2. If you want to go to HTML or other "native" web environment, again, you must start from scratch but this time you will need to learn a whole new language and development environment.

3. The simplest solution for an existing application is Citrix or Terminal Services. That will allow you to run the Access app on your server at work from anywhere. Since everything runs on the server and only keystrokes are sent to the server and screen pictures are sent back from the server, this is quite speedy. If you don't have a lot of users to support and you have a computer you can dedicate to the task, Terminal zervices is probably the cheapest solution. You set up the Boss' computer to allow remote connections and leave it powered up all the time (although, he can and should be logged off). Then he starts the remote session from his laptop. Some versions of Windows come with the Remote Desktop Client included and for others you need to purchase it. I think it runs around $125 per seat. Access doesn't need to be installed on the laptop because you are not actually running on the laptop. You are running the app on your server back at the office. Terminal services is only sending keystrokes and getting back pictures of the desktop in a browser window.

4. The final solution is to upsize the BE to SQL Server which you will have hosted by some service provider. Your Access FE can then connect to the SQL Server in the cloud. For this solution, the laptop needs Access installed and you need to distribute the FE to any remote user. This is the "middle" option as far as work you need to do. It is not hard to upsize to sQL Server but unless you have already used good client/server techniques with your forms and queries, you will have work to do to make the application function well. For a small database with only a few users, this option would run around $5 per month for the hosting.

Options 1 and 2 will cost thousands of dollars and take hundreds of man-hours for an application your size. Option 3 can be done in a few hours and might require the purchase of a couple of Remote Connect client licenses. Option 4 could take a few dozen hours depending on how quick you are and how much you have to change to make the app client/server complient plus the $5 per month for hosting the BE. You could actually try option 4 without making any application changes but you are likely to find it too slow.

Hello Pat,

Thank you very much for detailed post as it very helpeful and I understant it much better.

I agree that third option is the best and I will definitely go with that. Can you tell me what are the disadvantages of this method? Which are the main differences in compare with standard-local access to datababes from end-users point of view? Speed, availability, safety etc?

Thank you.
Adi

i was looking at implemeting as web databases, and there are added problems.

i forget the correct terminology, but one issue is that web pages are not data aware. you can reclick processes, use the back button and repeat processes that you should not be able to. so design of web databases needs to follow a different paradigm to a normal database.

@Pat

Since OP's question is recurring quite often and you have provided a succinct outline of options, may I suggest that you nail it more permanently to the bulletin board for the enjoyment of the hoi polloi, or else I guess it might slide into oblivion pretty pronto.

Hello again,

Can you please answer me on these questions:

- Can you tell me what are the disadvantages of this method (no. 3)? Which are the main differences in compare with standard-local access to datababes from end-users point of view? Speed, availability, safety etc?

- Since this database will be used by 7 employees (from job, home etc), when I make change to database on ONE place I woud like that ALL 7 users see that change, so I assume I don't have any choice but to place it on server and then allow users to connect via Terminal Services?

Many thanks in advance!
Adi

When you use Citrix/Terminal Services, the only difference to the user is he has to start the Citrix/Terminal Services session first. Many admins set this up as a desktop shortcut so the user just clicks the shortcut and either a remote desktop opens or the Access app opens depending on how it is set up. If you run multiple apps via Citrix/TS usually you will open to a desktop but if you run only one, you can open the app directly.

I don't do the tecnical work to set up Citrix or TS but I can tell you that the admin must be instructed to make sure each user gets his own copy of the FE. They will all try to publish a "shared" FE and that is not correct so make sure they understand the importance of SEPARATE copies for each user. Citrix/TS can be set up to have the Access FE copied into each user's workspace when he connects. That always gives him a personal, fresh copy. You would send updated FE's to the admin and he would make sure they replace the existing FE. So, when the user shuts down and reopens, he gets your updated FE. The BE stays on the server and is accessible outside of the Citrix environment for the folks who are local. So you can have a combination of local LAN users connected to the BE via their local FE's as well as remote users connecting via Citrix/TS to the same shared BE over the internet.

@spike - good idea, I put it in FAQ's

Thank you for the helpful information.
May I ask you to expand a little from the following please?

"It is not hard to upsize to sQL Server but unless you have already used good client/server techniques with your forms and queries, you will have work to do to make the application function well"

I would like to try the SQL route in the future and wish to know how to make the Front End function correctly - namely the "good client/server techniques"

And - can the front end be a runtime only? My intended users are actually just viewers of historical information and will not be allowed edits.

I too would opt for the Terminal Server solution. It is no big deal to set-up individual Front Ends. You may need a roaming licence or use VPN for security.

You could "thin-out" the client PC's and when they login they always use the Terminal Server. This would mean putting Outlook and all the applications onto the Terminal Server. The login process would be the same no matter whether or not they in the office or remote. I don't think Exchange Server will co-exist with Outlook although you can dump Outlook and use Web Mail.

If users want to use (remote) printers in their homes the drivers need to be loaded onto the Terminal Server and this way the Terminal Server should be able to print remotely. Default Printer settings may have to be changed if they are using the same Laptop at home and in the office.

You also need to pretty generous with the Session TimeOut to allow for any outage disruption.

To me the singular advantage of the Terminal Server is that it only transmits screen images over the WAN. The Terminal Server talks directly to the File Server over the LAN to retrieve data. The only problem may arise is performance if the server side bandwidth is highly contented and the upspeed can’t cope with the traffic. That can be resolved with a more symmetrical or business broadband which can have a 1:1 contention (share ratio) rather than domestic service which can be 1:50 plus.

Simon

Pavl,
Access apps developed by non-professionals typically use forms bound to tables or to queries without criteria. The app then uses filters or seek/find to possition itself to different records. This isn't a problem with Jet/ACE back ends but you loose all the advantage of SQL Server if you use this methodolagy. Apps written this way that are upsized without FE changes are usually slower after moving to SQL Server rather than faster.

To gain the advantage of having a "real" RDBMS as the BE, you need to limit the amount of data requested from the server by using queries with selection criteria. There are various methods that you might use and it really depends on how your users need to search for data. The simplest method is to add a combo to each main form. The combo is unbound and is used to select a single record. In the AfterUpdate event of the combo, you requery the form:
Me.Requery
The query you use as the RecordSource for the form uses a where clause that references the combol. The upshot of this method is the form opens empty and ready to add a new record. Only after you select something from the search combo and tab out of it will the form fill with the selected record.

there may be other changes needed to your queries to make sure that Access can "pass them through" to the server rather than asking the server for lots of data and running the query locally. Search for articles on "optimizing Access for client/server" for other suggestions.

The runtime can be used whenever the user does not need to make design changes to any forms/reports/queries/macros/code. So it is usually the best choice for users. Always go with the most recent version of the runtime available. They can run older applications as well. You don't want to end up with users having multiple versions of Access installed. Developers may have multiple versions but users shouldn't.

Thank you Pat H.
On first reading I think I already use the method you have described but will read carefully again to check.
The database is small but will be used with a large image folder of pdf pages linked to command buttons on the forms.
All my forms so far are "display only" upon the selection of either a name typed in and then requery or select a date from a combo (limited to list values within range available) followed by requery.
I also have a junction table which is used to put individual people into "teams" based on event participation. The teams are displayed on subforms in various ways.
Also have an aircraft selector which is three combos to enable choice of part of the aircraft code followed by requery. Am working on preventing it "blanking" the form if the chosen combination does not exist in the recordset.
Have a navigation subform for visible/hide forms (desribed to users as going from page to page). And a further subform for selection of modal popups to present more detailed information.
The only user edits are going to be from a popup form which is pre-populated with the chosen record. Viewer can edit but the changes are saved to a seperate table [TblUserEdits]. Will set up a report or similar to output pdf or spreadsheet for email to me.
That was the method I had planned for initial test distribution via DVD.
The one big problem is Apple Mac - apart from VM of some kind it seems that a web front end is the only way to go. Hence the initial question.

just to add to this. i have many pc's that i need to connect to. i connect to these using log me in. it is pretty basic and and it is basically desktop sharing. its free and they even do an iphone app that is pretty swift to. you can purchase pro versions but i have been using the free version for about 3 years now without any security issues.

ps. this is not an advert. i do not work for log me in. etc etc etc

Re: Need more info on option 4

@Pat

We are currently set up to access our database through terminal services. We are a small business with only 5 users. The problem I am hearing is that the terminal services connection is very slow or delayed and so the users are not tapping in during home visits as they need to be doing. Because of this I have been given the task of setting up on the cloud. I think the easiest solution is to use the dedicated cloud server in conjunction with remote desktop hosting services through Access Hosting Services but this service is a bit out of our budget so I am inquiring about option 4. I have not worked with SQL so this is new territory for me however I am excited about the opportunity to broaden my horizon. Because I am so unfamiliar with this can you please tell me what software is needed to complete the upgrade to SQL, we currently have SBS 2008. Also, which hosting service is best to use once the upgrade has been completed?

Your input is greatly appreciated.

i have started a new thread asking how i would link my database to the cloud or put it in the cloud altogether. i think i may have found my answer but i feel it is way out of my league. what i want is for clients to book appointments online. so i need to either drag data to the cloud as and when requested or drag data from the cloud for use in the front end.

how would i communicate with my tables on the web if the tables are stored on a desktop.

Thanks for your reply, Pat, I agree with you.

Adi, I just wanted to share with you a possible option that worked pretty well for me.
It's called Web-Access. It's not an automated tool to convert MDB files into a Web app, but rather a service which you can use to transform your MDB file into a real web app. I found it the best way to get a responsive website, written in PHP and Javascript starting from a MDB file.
You log a request , they return you an offer, and you can follow up the progress in real-time.
They are not cheap, but I liked their support and the end result was great.

www levaweb .com

Another solution could be PHPMaker. This is good for very simple MDB app, useful to CRUD the database (Manage your records), but you need to implement the logic and template of your website by yourself.

www hkvstore .com *** PHP-Maker

Hope it helps!

This is probably a dumb question, but has Access 2016 changed the game on creating a web-usable app? I ask this because I finally upgraded to Office 2016 recently at home, and they offer, under "New" to create a web app among their various templates which is different. They seem designed to be used with Sharepoint. I'm thinking there may be a simpler way to go. I'm working with what will be a hobby project, tracking productions of as many Shakespeare festivals as I can get schedules on. So, three tables to start - lists of plays, festivals, and production schedules with dates. Would like the visitor to be able to search by play, look up performances by state or region, etc.

I started working with Access because at this point it's what I know best.