Solved

How to convert Access database to a web app?

Posted on 2016-07-14
3
110 Views
Last Modified: 2016-07-29
I have an access database I would like to make accessible online. Is Sharepoint the best way to do that? Is there another way by converting the Access database to SQL?
0
Comment
Question by:brasiman
3 Comments
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 150 total points
ID: 41711106
There is no true conversion path from a client/server Access app to an Access web app.

If you need to distribute your app to users not located on your LAN, the far and away superior method is to use Citrix or RDP.  That allows you to leave the app as it is although you could convert the BE to SQL Server if that makes sense.

If you have enough users, it makes sense to host the app yourself.  Otherwise, thee are service bureaus that will host the app for you.

Access is not optimized to use SQL Server (or Azure) over a WAN.  You could do it but it would probably involve massive changes to convert the forms from bound to unbound.  Switching to SharePoint Lists has other problems.  The SharePoint lists are not a true relational database and the performance degrades with more than a few thousand rows in your tables.

If you don't have to make everything available online, you might be able to come up with a compromise that uses SharePoint Lists to pass data between the Access client/server app and SharePoint forms to allow remote users some functionality such as viewing a customer list or part master that the Access app replaces nightly.  Or possibly to do some limited data collection.
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 150 total points
ID: 41711148
There's this:

www.eqldata.com

They can host your Access db on the web. It's not free, of course, but it's a quick and easy way if you have only a few users.
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 200 total points
ID: 41712722
<<I have an access database I would like to make accessible online. Is Sharepoint the best way to do that? Is there another way by converting the Access database to SQL?>>

  Just to add/clarify a few things:

"Access is not optimized to use SQL Server (or Azure) over a WAN.  You could do it but it would probably involve massive changes to convert the forms from bound to unbound. "

 While that first part is true, there are things you can do to make it work fairly well.   JStreet technologies has done quite a bit of work in this area, and offers some tips and tricks for making that happen:

"Best of Both Worlds"
http://www.jstreettech.com/downloads.aspx

But that leads into the 2nd part; changes are required, almost to the point of a re-write in some cases, which as Pat said, would be considered massive.  it really depends on what/how much of the app you want to make available to users.   It's something you should not dismiss right off.

Access 2007/A2010 Web Databases use SharePoint lists for storing data, and that is a non-starter.  As Pat pointed out, SharePoint lists are not a great way to store relational data.

Access 2013/2016 Web Apps use SharePoint for a the Front End, and Azure for a back end to store the data (SQL Server in the Cloud).   This works well.

But in both cases, these Web versions are "Access" in name only, and are a different animal entirely from a desktop database.  It's not a one for one.  For example, you can't use VBA code in either.

However if your remote users only need basic CRUD (Create, Read, Update, and Delete) of information, then it might work well.   What you do is use the web app for them, and then connect to the Azure BE directly with a desktop database to do everything else (processing, reporting, etc).

 What that looks like is this:
https://msdn.microsoft.com/en-us/library/office/jj250134.aspx

 Your other option is to write a new front end for the web with something like ASP.Net, then point to the back end database that is used by your current app.

HTH,
Jim.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

What is Node.js? Node.js is a server side scripting language much like PHP or ASP but is used to implement the complete package of HTTP webserver and application framework. The difference is that Node.js’s execution engine is asynchronous and event…
Read about why website design really matters in today's demanding market.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
The viewer will learn how to count occurrences of each item in an array.

706 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