Best way to use an Access database in the cloud

I really want to learn how to create a MS Access database and be able to use it in the cloud.  I've been looking into MS Azure but so far it seems a little "clunky".  I ended up with over 100, yes 100, emails back and forth between myself and MS support to finally get it to work just with a little test database I had created.

Does anyone have a better solution?  I really want to use an Access front-end linked to a cloud-based backend.

Any suggestions would be much appreciated.

--Steve
SteveL13Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Are you sure Azure is clunky VA a learning curve using Sql server?

Many of the cloud databases are proprietary Sql or Nosql db's.

Access is not meant for the kind of stress and size that you would need for many useres.  You can place access on any MS webserver or perhaps amazon hosting
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<< I've been looking into MS Azure but so far it seems a little "clunky".  >>

Not sure what you mean by "clunky",  but let's sort through some things.    Azure is SQL Server in the cloud, so things you do with Access normally with a JET/ACE DB on a local LAN are not quite the same.

But it's really no different then that of using SQL Server on the local LAN either and Access does make a good front end tool for use with it and I totally disagree with Scott that "Access is not meant for the kind of stress".  There are many Access based apps using SQL Server as a datastore over the internet that work perfectly fine.

However when using SQL (or any true client/server setup), you want the server to do as much work as possible and minimize the data traveling over the wire.   The later even more so when your working over the internet.

That requires a different mindset and approach to Access based apps.  For example, your simply not going to bind a form to a table, but rather fetch records as needed.   You will not be using VBA expressions in queries because SQL doesn't understand VBA and you'll want to push the query execution server side.  It's things like that which make a night and day difference in terms of performance.

 So here I agree with Scott; there is a learning curve with how to work with SQL server over the internet.  If your expecting to work with Access over the internet just as you do on a local LAN, that's simply not going to happen.

 Your choice there would be to have a company both host the DB and Access app on a server, then RDP into it.  That way, your still allowing for remote users, but the only thing going across is KVM (Keyboard, Video, and Mouse).  As far as the app is concerned, it's running locally.

Beyond that, there are Access Web Apps, but they are in their infancy and very limited at the moment, and are a totally different animal.  They operate as a true web app via Sharepoint with a SQL server back end.

Jim.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
Access is not meant for the kind of stress and size that you would need for many useres
This statement more correctly refers to Jet/ACE NOT to Access.  Many people conflate the two.

Access is a rapid application development tool.  It is the FE to ANY relational database that supports ODBC.  Think of it as the C++ or ASP part of the app rather than the data store part of the app.  Access is closely linked with Jet and ACE (and that is what causes the confusion) because Jet/ACE is the database used to store the "Access" objects.  However, there is nothing that restricts you to Jet/ACE to store your data.

Moving from Jet/ACE to SQL Server, et al usually means certain types of application change.  The biggest one being the shift from using filters to queries with criteria.  The difference is that with filters, all the data is brought to your local computer for processing and the selected records are "filtered" for viewing.  With queries with criteria, the queries are sent to the server for processing and ONLY the requested data is returned.  This can make a huge difference in responsiveness when the BE tables are more than a few thousand rows.

The next generation of Access apps will link to cloud based databases.  Due to the chattiness of Access and its proclivity to "talk" to the server, you may find that the overhead of doing this over the web requires you to switch many of your queries from "Access" queries against linked tables to real pass-through queries.  Even though Access attempts to pass-through all queries, there is more overhead associated with "Access" queries than straight pass-through queries.

I have not yet successfully built an Access app with a cloud based BE.   The connections have always been too slow.  The people I have spoken with who have had success with Access linked to remote databases, host their own BE.  They don't use a third party service bureau.  I haven't had a client able to do that and working with third party providers has not proven fruitful for me.

Rather than attempting to lock Access into SharePoint, MS would have served us better by cleaning up the Access interaction with ODBC databases so that it worked more efficiently over the web.  There really isn't any reason that Access should perform worse than any web app connected to a remote data source.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Gustav BrockCIOCommented:
>  .. your simply not going to bind a form to a table, but rather fetch records as needed.

Oh, that depends. For smaller recordsets it may work perfectly well, and even with larger, a snappy method is to pull a snapshot (easily set in the properties of the form) - and even across slow lines of, say, 1 Mbit/s.

/gustav
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
The problem with snapshots is that they are not snappy for the initial pull of data since your forcing a pull of every record over the wire.

 The main point is however if you approach client/server apps like you do what one typically does with Access, it's not going to work well.

Jim.
Gustav BrockCIOCommented:
Well, then you miss to see some of my apps where many forms (not all) work perfectly well bound to tables linked via ODBC over a slow 1.5 Mbit/s ADSL2+ line.

/gustav
PatHartmanCommented:
Gus,
All of my apps are bound to linked ODBC tables.  The difference between a Jet/ACE and an ODBC connection is that you need to use queries with select criteria to limit the number of rows being returned.  Many Access apps rely on forms bound directly to tables or to queries without any criteria.  Those apps will always be slower if you swap out Jet/ACE for SQL Server without making any other changes simply because they will require Access to pull ALL the table data from the server so the user can filter it locally rather than using queries with criteria so the server can do the filtering and return only the rows you need.  Whether the BE is on your server or in the cloud, these apps will not gain any performance increase by switching to SQL Server and many will see a performance decrease due to the additional network overhead.  I occasionally use pass through queries but only for batch updates.

If you are achieving adequate performance against a BE in the cloud I would love to discuss it with you in another thread or privately.
Gustav BrockCIOCommented:
I currently run a project off a SQL Server hosted at Amazon in the tiniest instance available. Some forms, bound the traditional way via ODBC, opens instantly. A form retrieving 10.000 records loads in two seconds.

So when Jim tells "it's not going to work well" it is not always so.
Of course, some things will not work. Indeed one has surprised me as simple as it is:

    Delete * From SomeTable

This takes "ages" (many minutes) via ODBC. However, if I call:

    Delete From SomeTable

with a pass-through query, it runs in a split second.

/gustav
PatHartmanCommented:
The reason that the Delete * takes so long is because Access runs it inside a transaction.  Remember, Access always asks you before committing a batch update if you want to back it out.  That is because the initial update is temporary and if you say "no", the update will be rolled back.  If you say "yes", the update will be committed.  When updating a large table, you may get a message telling you that the table is too large and the update cannot be undone because there isn't enough memory to hold the entire update.

Access simply sends the pass-through query to the server.  There is no backing out.  Once the action query is sent, it is as good as committed.  You get no second chance.
Gustav BrockCIOCommented:
That could explain it, though no message is seen when you run CurrentDb.Execute ...

/gustav
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
gustav,

<<
So when Jim tells "it's not going to work well" it is not always so.
>>

 That's true...there are specific instances when you might get away with it, but what I'm saying is in general you can't expect to take an Access app already development, move it to a cloud hosting service, and expect the same level of performance.   I think you would be hard pressed to disagree on that point.   I think you would also have to agree to that that is usually more the case than not.

 Your client expectations also play into this somewhat.  You said:

"A form retrieving 10.000 records loads in two seconds."

 I have some clients that would find that unacceptable and considerably slower than what their used to.

Jim.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<The reason that the Delete * takes so long is because Access runs it inside a transaction. >>

 That's not the only reason....depending on the delete, JET may issue one delete to the BE for each row.  

 If it can, it will send the whole delete through, pass-through or not.   But many times, it decides to carry out the processing and when it does that, it issues one delete for every row.

 You can see this by looking with ODBC tracing turned on.

Jim.
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
>  I really want to use an Access front-end linked to a cloud-based backend.

I think we may be interpreting the use differently.    My assumption was an  app that could be used for web/mobile by many different people.    You probably wouldn't build a site like Experts Exchange with Access for instance.

Although I have not used Access as a web db in a very  long time, I found it clunky to work with vs using sql server.  But there was a learning curve as I was comfortable with access.  At least at that time, there were actually only a few things that had to change in sql statements.
SteveL13Author Commented:
I truly appreciate all of the valuable feedback.  I believe that for now I am going to go down the MS Azure route.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.