How to migrate a Access database to be available b

Lets say I have a single access file (or two files split into a file with data and a frontend file). I want to make this available to users in a variety of locations.

This question has been asked before, but some answers are old eg

list here

SO 2879281 here

SO 11979044 here

SO 32667061 here

There seems to be many ways this can be achieved, and I just wanted to ask for opinions to see whether I've missed any options:

the options all have pros and cons. The most significant differences are in terms of :

Cost & Time
Need to re-write the access database to be in SQL Server
Need to re-write the front end
Concurrent use.
Scalability of solution and performance.
Use SQL Server to store the data.
Simplicity!

Here's the options as I see it.

1. Create a desktop front end file linked to the sql server data.

Each user has their own copy on the PC of the front end file. You manage your own SQL Server database / instance yourself or use MS Azure or similar web service. (There is a free version of SQL Server that can be installed and used on a server, or you can us

PROS: You don't need to re-write you front end file.

CONS: You need a SQL Server instance. You need to migrate your data tables (and you may need to do some work to replicate them in SQL Server, especially if you have data macros, or use special data types (like Yes/No, Multipel select combo boxes and a few others). There are free tools that do most of the migration and paid for ones that do more.

2. Store a file on dropbox or similar and facilitate sharing

This works if ONLY one user will ever need to UPDATE the database at a time.

Store the file on dropbox and use a vbscript like that described here to grab the file from dropbox to the local disk, allow the user to update it, then put it back to dropbox. (A mechanism is used to stop other users grabbing the file IF another user has grabbed it.). This approach could be adapted to allow users to grab the file for READ ONLY purposes if another user has grabbed it for updating.

Note that this method minimises the need to re-write the database and can use free online file storage to facilitate sharing

Note that 2 could be modified to Auto update a front end file stored on a users PC as suggested in option 1.

PROS: Cheap! Minimal changes.

CONS: Only one user updating at a time. You need to write fairly complex VB scripts. You need to manually set up dropbox for each user and it's sync to c drive.

3. Use Access 2010 to create a Web app. (The data gets stored in Sharepoint lists, and the forms use macros to respond to the users actions. Info here and video demo here

CONS: An "old school" way of doing it - Access 2013 is better.

4. Use Access 2013 web apps. This stores the data in SQL Server and uses new Access features to provides a web GUI (ie Views). You can also access the same SQL Server data from a desktop access front end (for reporting etc).

CONS: for 3 & 4

The "forms" that you create do not have as good functionality as a desktop front end as VBA cannot be used - macros are used instead. But this can be ok in many cases.

A sharepoint subscription is required and this may stop your database being widely available as (most of) the users will need sharepoint accounts.

You will probably need to re-write your existing access forms. PROS: More scalable as SQL Server is used. Easy to create and manage using Access.

5. Use a virtual hosted desktop server. mentioned here eg Amazon Web services.

I need help with this. As I understand it this might be quite costly. I have questions like:

5a: Does each user need to "subscribe" to their own workspace? Do these workspaces need MS Access/office licenses? (eg If so Amazons would be $40/user/month), or can you just use an Access runtime (so Amazon would only be $25/user/month).

5b. Can each user's workspace link to the same access data file stored on a fileserver or can one workspace get to another workspaces files. There are costs for having a fielserver.

6. Use third party software.

eg $50/user/month EQL Data plugin claims to be able to Run unmodified Access applications, right inside a web browser.

CONS: Expensive! ? PROS: Might be easier than other methods above.

7. STOP using MS Access and write or re-write the whole database using another language.

This is always an option. My main question here is if you are proficient at Access and a VB or C#, will creating the same app. in Access be quicker than in VB, java, C# (etc). Mates of mine write apps in JAVA and it takes them ages to do things that I can do in Access in minutes. Also, the great thing about Access is that it's often "end users" that create the database for themselves and it gives them what they need.

8. Up-size the data from Access to SharePoint

Except from SO here

You can up-size the data from Access to SharePoint (or low cost office 365). SharePoint tables now support relational data, so “basic” master to child tables (and cascade delete) is supported when you move Access tables up to SharePoint (you are however limited to PR of auto number, and child records (foreign keys) must be a long number.

Depending on the size and number of rows of data, the above solution is OFTEN better then moving tables up to SQL server. A VERY nice feature of this setup is that your application can continue to run without any internet connection. The instant you find some Wi-Fi etc., then your data syncs.

So I would not write off the SharePoint table’s option, as they can work VERY well with many kinds of Access applications, including ones with related tables. And the “off line” mode can be great for those laptops in the field without an internet connection.

9. Use remote control software like teamViewer to allow use of a remote PC and hence the the database. Only allows one user at a time!

The cheapest way (IMHO):

8. Move your data tables to SQL Server and keep MS Access frontend

Use Upsizing wizard to convert your shared Microsoft Access database (which contains only tables) to SQL Server database.
Because now you are connecting to MS SQL, in your Access client application
- change tables location (connection string now points to MS SQL database)
- in your VBA code, manually update SQL queries containing Access-specific syntax to standardized SQL (logical – the Upsizing wizard cannot see them in code, so it cannot convert them automatically)

Done.

Of course, this can have some pitfalls, but at time when I was doing this (it was Access 97 and SQL Server 95), upsizing process finished without any errors and complete migration including VBA updates was done in about half a day.

If you finish the process, you can continue using Access frontend and if you are interested, you can slowly start re-evaluation whether you will update to another frontend. If you have some Access programmers around, they will be probably happy with vb.net, because VBA → VB.NET is more native than VBA → C#. I have actually done it and I am very happy with VB.NET. All cutting-edge technology reachable in C# is reachable also in VB.NET.

Regarding SQL server: You can use free SQL Server Express edition until you are happy with its limitations. Currently it is 10 GB database size and single CPU (but multiple cores).