• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1149
  • Last Modified:

MS Access alternative for the web

I am about to start a project which needs to access customer data from probably SQL Server and/or Mysql. I need it to be able to be accessible via the web and probably integrated into a WP website and possibly a standalone app (in time) for iPhone/android.

Up until now I have only had experience with in house MS Access front ends and am looking for an alternative. This company is considering moving all of their databases to the cloud and I need to come up with a few suggestions.

Any ideas?
  • 7
  • 4
  • 4
  • +3
6 Solutions
Ideas, there are too many to mention.  Presumably you mean the data will be on sql cloud based server that are accessible.

You can develop a cloud based app that installs/runs and distributed through a web server.

Or using a web based interface depending on the complexity that your interface has to encompass. Web based, you control when and how it is updated .
A distributed cloud app will need to often get updated for the new features/changes to take effect.

An android/Apple app is doable and depends on how you want the data exchanged I.e. Xml data exchanged between the app and a web server or you can use the many tools to have a custom service on the cloud server/s and use a custom API data exchange between your stored cloud data and external apps.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The more common web languages would be PHP, Java, ASP.NET (just to name a few). Those languages allow you to build a dynamic, interactive website, so you'd be wise to start the process of learning one. Since you have a MSFT background, ASP.NET would likely be the easiest, but it's by no means the only one.

Using Access as a FE to cloud-based servers (like Azure) seems to be a no-go prospect right now, due simply to performance issues. Access 2013 also introduced "web apps", but they're not really ready for prime time, at least in my opinion. They're fine for standard CRUD operations, but if you need anything more than just basic data input, they're not the ticket.
This company is considering moving all of their databases to the cloud
WHY?  What do they hope to gain?  As a business owner, I would NEVER give up control over my data.  Sure, there are valid business reasons to move some data to the cloud if it needs to be accessible outside of the corporate LAN, but all data?

Once all the data is moved to the cloud, you are pretty much limited to writing web apps and living with replacing all your client/server apps with web apps. Access does offer a web based solution but it is dependent on SharePoint and since it doesn't have a programming language you are severely limited in what you can do.  Not to mention the limited features in queries and the inability to run action queries, etc.  I don't consider it a viable platform for anything but the most basic of applications and even then, you are likely to get into it and find it simply will not do what you need to do.

I have a client who has all their data on the web.  The reason is that it comes from utility companies and is processed by a third party who also hosts it.  They have nothing but trouble to the point where they are in the process of building a local SQL Server database to mirror the one on the web!!!!  Now, their situation is somewhat different from yours in that they never had their data in house to begin with.  But they are an example of why I wouldn't do it.
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

PatHartman, the simple answer to your question is the cost of maintaing servers, infrastructure and redundancy geographic........

As you point out there are significant drawbacks.

Some if all/most their user base is remote, it makes sense to rely on an external entity that over more than 5 years has demonstrated 5 9s SLA for service as well as the cost of cloud has gotten lower.

It does not have to be web based apps, it just makes sense for rapid deployment.
you can still write access based front end, using the SQL server that is in the cloud performance of which will decline based on how many queries you need to generate to populate the display.
I have been trying to use a hosted SQL Server solution off and on for several years and the performance is dismal.  It would be good if MS would spend some time optimizing the way Access uses ODBC.  There is no reason for it to be significantly worse than a web app that processes the same data.

The best success I have had is with using Citrix.  In some cases remote people have better response than local people because the Access app runs on the Citrix server so there is no latency.  I've used the solution with SQL Server back ends as well as Jet and ACE.  If the client is buying an entire "server" in the cloud, that could be an option.  Then you don't have to change the Access app at all.

The performance on the web is higher because presumably the web server has a high speed connection to the sql presumably they are on the "same" network
while your Access ODBC based up is connected on a 1, 2, 3, 7, 45MB link.

By building the logic/processing within the SQL using storedprocedures, you can significantly improve the performance of your Access GUI which will be closer to web.
i.e. send 50/60 byte request, get huge amount of data with this, your performance will be limited on how quickly the complete data makes it back and displayed.
Effectively making your access GUI as a thin client.

As you pointed out, one has to start with an analysis of business needs, requirements, budget, resources and then go from that.
A newly formulating business fewer capital demands for a cloud based deployment versus ramp up an location, or co location with dedicate servers, et.................
The app is already optimized to work with SQL Server and requests the minimum set of data.  The local database has hundreds of thousands of rows and works quite well.  A copy with the largest table holding only 100 rows for testing was so slow you could watch the pixels as the form rendered.

The connection is the primary limit for the data getting back and forth. I've given up on Access as a GUI/Front/only.

No matter how optimized the app is if it has to send a request, receive a response and send a request to receive another response, for sometime while performing logic on in between the requests,
versus send an exec proc "action",parameter1,parameter2,parameter3 and get the complete response.
your functional overhead is lower, but one can not have everything stored in stored procedures/functions ......
I was just talking about painting the screen.  That isn't anything that would require a stored procedure.  And if you are going to talk about using unbound forms, then you shouldn't be using Access to begin with.  Access works very well with local server based tables.  Not so well via an internet connection.  Maybe MS will fix that but probably not since they are hell bent on pushing everyone into Access web apps.  It's like the people who created the Win 8 fiasco are now running the asylum on the Access team.
It's like the people who created the Win 8 fiasco are now running the asylum on the Access team.
Vice versa.
I was in on the O2007 Excel/Access pre-release panel.
'It's a billion dollar business!'

The folks that brought us the Ribbon and the whole 'look ma no code, just throw everything away that you did with VBA and start again with the new macro language and a 50K row limit in SharePoint with no upgrade path' (2007, remember) went on to the Windows team.  That's be Sinofsky, Jensen Harris and Julie Larson-Green.

They went on to Metro and Windows RT.  Sinofsky and Harris are gone.  Larson-Green went up the food-chain.
Nadella talks about MS being a 'platforms & productivity' company.  We'll see if he comes to realize that VBA on multi-monitor PC's is what that really means or if we are going to get more 'Mobile, Mobile Mobile!' shoved down our throats.

I actually called MS to ask just what makes O365 make sense.  Vastly larger numbers of workers are NOT more mobile than they were five years ago.  How does 1 TB of data in the cloud make sense when upload speeds are typically so poor?
My ISP wants $5500 to bring fibre to the door and $1350/mo for symmetric 10 Mbps up/down access to the internet.  How is that supposed to make sense?

Upload speed and network latency are the big hurdles with 'all my data lives somewhere else!' scenarios.  Rarely is there a cost-effective solution to be had.

ShawnAuthor Commented:
Thanks everyone for participating. I have been out of the scene for a while and was hoping there has been a breakthrough in my absence. :)

I think almost everyone has good points and we may be able to sum it up as follows:
Access works best for internal use, aka the business LAN. It can work in the Cloud with Sharepoint but is extremely limited (no VBA, etc.) as Pat and Nick pointed out. Access could also be used as a thin local client but has severe latency problems with a remote backend.

Pat also mentions that through Citrix it works well on the cloud as the back end may be in the same cloud. I have had similar success using Terminal Server for remote employees.

The best solution I have come up with is threefold:
1) Local Access version for in house employees
2) Terminal Server (or why not Citrix) for remote employees and
3) Using web based languages (as Scott mentioned) for restricted client access to the same back end. I chose CFML way back and liked it BUT it took days, sometimes weeks to program something which could be done in Access in hours. This is my problem/dilemma....and even then I have yet to see a web app with as many features/power as an Access front end.

Am I missing something here? Is my search for an Access "Alternative" futile or are there decent, robust, cost effective solutions out there? if so, what are they? I am particularly interested in 3) for client access.

PS I did mention they were considering moving everything to the Cloud and yes this is because it is a smaller company with older servers and a limited budget to maintain internal servers. I either need to show them ways to do this with solutions other than Access or convince them it is better to at least keep one server in house. It seems so many iphone/ipad apps are popping up these days it would be a shame not to utilise these resources provided cost and development time are not prohibitive for smaller companies.
BUT it took days, sometimes weeks to program something which could be done in Access in hours.
even then I have yet to see a web app with as many features/power as an Access front end.

You are asking the choir if there's another way to heaven except through Jesus :)
So I don't know if you'll get any answer other than 'No'

Dealing with the limitations of the Web model when you come from Access is very, very hard.
I've used Visual Web Developer a bit with, VB.Net syntax.
It's a long, long way from VB6/VBA in terms of practicalities.
SO many of the things that you take for granted with Access are very painfully time-consuming to work out how to get a browser to do the same task.  And debugging ASP.Net is just evil compared to VBA.

I am leaning away from browser-delivered content.
It's just not rich enough.
You CAN deliver VB.Net executables via the web that consume web data.
A lot of what was formerly delivered via ActiveX has gone that route.
We have fleet tracking via FleetComplete.com.
The desktop application is clearly one of these web-data, local client deliverables and works well.

Every time they push out an update though, that's 65 MB of pull-data and about 6 minutes of my life shot because they don't have the bandwidth to really punch it up to me effectively.
That's my two cents.

One solution we haven't discussed is the Access hybrid app.  This is a normal client/server Access app that is linked to Azure via SharePoint 2013 or newer.  The app can have web pages that are rendered using SharePoint and what we think of as normal forms - including VBA in the Access FE.  The downside is you have to distribute the FE as you normally do with LAN based apps although I think you can use SharePoint to distribute the FE.  I'm not sure how responsive this linking via SharePoint to Azure actually is.  I am trying to build one to test it but there are so many moving parts to getting an environment I haven't gotten there yet.  I have an O365 account but that is insufficient so I signed up for a SharePoint account but I haven't yet been able to connect them.  I use my client's O365 from my home computer occasionally and I keep getting conflicts with logons.  I'm not sure it is possible to actually have two separate O365 accounts which is my current situation and get them to both work at the same time.

I have yet to see a significant web app that I actually like.  In particular I hate web mail.  I also find them clunky and unfriendly and I can't for the life of me figure out why they are all the rage.  I was using COBOL and CICS back in the 70's and except that web pages are much prettier, they have about the same amount of functionality except that the majority my CICS transactions had to load in under 2 seconds.  A certain portion of "heavy" ones could go to 10 seconds.  With all the moving pictures and sound etc that is currently the vogue on web pages, they are always too slow for me.  Not to mention all the scrolling.  Have I mentioned that I HATE scrolling.  Public web pages put so much crap in the header that you can't ever see anything and they never put their scrolling buttons at both the top and the bottom of the form so you always have to scroll all the way up or all the way down to go to a different page.
ShawnAuthor Commented:
Nick, love the choir/heaven analogy.

Pat, let me know if you have any luck with Azure/O365 though I had a look at it and it seemed more like a CITRIX/Terminal Server type solution. Nice rant on web apps. Seems we concur.

Anyone else have success with any other solution?

Maybe an iPod/iPhone app, at least for quick and simple tasks? Haven't gone this way before so any advice is welcome.
Any and all of the 'Mobile, Mobile, Mobile!' solutions have the same basic conundrum.
What's the most cost-effective, secure place to put my data that everyone I want to have access to it can get to it with relatively little hassle?

I played with an Azure backend to an Access front end for a bit.
At that time, Azure logins required pre-listing the whitelisted permitted IP addresses.
So much for mobile ;(
Things may be better now
Performance wasn't great so I figured I'd use the beta Sync tools to keep a local server and an Azure server synced in real time.
It stopped the local clients dead every time I tried to turn it on.
So I set that project aside.
Only to discover months later that MS had been billing me ~$13/mo for a 300 MB SQL Server instance that I wasn't accessing because the tools for it to be useful weren't up to snuff.  I wasn't amused.

Things may be better now, but there's a lot of moving parts.
And a lot of hype.
The triumph of marketing over sense.
So, caveat emptor.
It's so much better to discover that you are building an app that will require significant monthly expenses and yet look and feel unfinished before you start building it.
And the first time I hear about ecstatic SharePoint users, I'll let you know.
MS and PHB's like it.
End users don't seem so enamoured of it.

Lots of details to work out, on continued monthly expenses, development costs, and UI experience
My client is thinking about using a tablet for data collection.  I immediately told the manager to not under any conditions run out and buy an iPad.  As far as I can tell, the only tablet that is a real computer capable of "work" as opposed to "play" is the Surface Pro.   The problem of course is connectivity.  Their clients typically don't have internet connections so you would need to bring along your own hotspot.  I've almost got him to the point of skipping that expense and just collecting the data and uploading it when they get back to the office or home.  But the real problem is usability.  Tablets are cool for watching movies and playing games that you can control with a finger.  They are unusable for typing unless you can type one handed.  So, luckily, the data entry task is primarily picking an option from a combo or option group so it is actually feasible.  It is unlikely that the care manager would be able to actually type his notes on the tablet so he will probably still write them on paper and type them in when he gets back to the office.  I'll look for something that can use a stylus and recognize handwriting but typing is simply not an option.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I have a client who insisted on using tablets for their shipping department. Basically, the shipping dept loads the truck, and then the driver is supposed to "sign" the tablet, and the tablet is supposed to create a PDF and store it on a server. Works okay, but the driver always has to type in several things, like the Trailer Number, Manifest Number, etc. As often as not, they key in the data incorrectly, which causes my software to pick up an errant value when creating ASN data to transmit, and they end up with penalties because their ASN data is wrong. They asked me what to do about it, and I said "Throw the tablets away and use a desktop machine". They were none too pleased with that response, and instead bought Bluetooth keyboards for the tablets, and then placed a small table next to each shipping bay where the user could put the table on the desk ... and use it ... sort of like a "desktop machine" :)

Tablets are great for watching movies and playing games, but they're not useful for every work application. Before you go down that route, I'd suggest you map out the scenarios in which they'll be used, and make sure they're a good fit for those scenarios. Otherwise, you'll end up with some somewhat useless tiny laptops.
I've been struggling with melding a complex Access FE with a BE, to enable multiple users to share the data. I tried Sharepoint for the BE, but its performance (latency) is a deal-breaker.

I've had great success by placing the FE & BE on a hosted Citrix server. While this works well, the host ISP monthly charges are based on the number of simultaneous users; plus, you pay for the MS Access licenses. This is a downer for small businesses. FYI: I checked out several Citrix hosts, and found that NovelASPect has the best pricing, and their support team is extremely responsive.

If anyone has had a good, cost-effective, experience with other Citrix hosts, I'd sure like to hear about it.
You should be able to use the Access runtime engine which is free.  You don't need the full version of Access.

If you have only a couple of people or if the people all have company desktops they can connect to, you can use Terminal services or Remote Desktop (newer name).  Some operating systems come with a license but for "home" operating system versions, you need to pay for a license which I think is around $100.  The company desktop must remain on but it can be locked.  People connect to their own desktop and access the LAN via that.  It is not as speedy as Citrix because with Citrix, you are running on the Citrix server and not incurring the overhead of the LAN.

If you don't have a company desktop to connect to then Remote Desktop isn't as useful.  I think two people can connect at one time to a server but I am unfamiliar with how multiple users are set up.  I have only used the one person connecting to his own computer setup.
I run a Windows 2008 R2 box as a terminal server as well as it's roles on the LAN.
There are Terminal Server CALs to be purchased.  You can pick per device or per user.
This works -- but like any remoting solution, is highly dependent upon network latency for a good user experience.
3G, 3G+ and what they call 4G but isn't LTE are all horrible end users experiences because of latency.
I have no experience with LTE.
Standard ISP experiences can be mixed.  If the LAN's internet face is a proper static IP and the 'away' internet face is also a proper IP, the experience can be reasonably good.  If you have ISP's that run everyone on a 10.x.x.x IP and channel everything through proxies, the experience can be horrible.  Satellite ISP and WAN wireless internets can also be very poor.  Satellite has high latency and WAN wireless can, too.  And you are on a shared bus.  Our guys do their remote work in the morning.  After kids come home from school, the experience becomes painful.
Bandwidth or tthroughput matter little when you mouse click takes 1.5 seconds to traverse the network, and the screen refresh takes a 1.5 seconds to return.

When you do not host the server, then the ISP shenanigans of making it take forever to get through the latency in a VPN are greatly lessened -- but you then have the expense of the host and the headache of upload bandwidth caps in getting your data to the host.  Much depends on the quality and competitiveness of your ISP market.
ShawnAuthor Commented:
thank you everyone for your input. Seems real solutions are rather limited :(

feel free to add anything if anyone come up with alternative suggestions.

cheers, Shawn
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 7
  • 4
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now