MS Access VS MSQL Server

[nector]

We dumped MS SQL Server for MYSQL as relational database due to speed we have been using this MYSQL databases with MS Access as front end and has proved to be reliable and our users like the speed compared to MS SQL Server which makes the speed horrible even if you do what is so called tunning the gain is not much.

Now we need to move further by providing the same solution through the mobile phone. Since MS Access cannot handle this, is there any platform that provide coding, nice forms and reports that can be interesting?

(1) Java NetBeans is ok, but the forms and reports are quite challenging especially JFrame forms

When you are using MYSQL our users fail to find any difference between storing the data on a local computer and cloud-based computer, that is the main reason why MS Access is popular, and we will keep it that way

 

[Pat Hartman]

Your title has nothing to do with your question. You might want to change it.

 

[dalski]

I wondered why people only seem to talk about MS SQL Server/ Azure. Azure seems to be ridiculously expensive & always wondered why don't people use PHP/ MySQL...

 

[CJ_London]

providing the same solution through the mobile phone. Since MS Access cannot handle this

Providing your phones support terminal server- it can be done at a cost - and clearly form design needs to take the screen size into account and how users navigate the app

 

[GPGeorge]

Having done some work with PowerApps for smart phone and tablet environments, I can tell you the main reason we don't write our apps to work on smart phones is that--except for some really simplistic applications-- their form factor and screen size militate forcefully against that approach. Your phone is fine for doom-scrolling Facebook or watching YouTube videos. It's not so good for data entry involving more than making a selection from a dropdown.

 

[Isaac]

for a simple data entry/viewing app you can try AppSheet if you like.

 

[Isaac]

I wondered why people only seem to talk about MS SQL Server/ Azure. Azure seems to be ridiculously expensive & always wondered why don't people use PHP/ MySQL...

I think that popularity begets popularity.
Once a system (like MS Sql) is popular, that exponentially grows and increases the amount of community/online information about it, which then attracts more people to use it. Nobody wants to use something that has "less" online information, so I think the ones that became popular early just stayed that way, to some extent it may be that simple. Curious that you say Azure is ridiculously expensive, I didn't know.

 

[Minty]

Azure has a cost but it varies tremendously depending on what you set up. ~£50 a month gets you a scalable, resilient Cloud based SQL server for a basic system, with redundancy and backups. I don't think that's a lot.

~£200 a month gets you something you could run most medium/larger sized businesses on. If you have MS O365 subs and 50 employees it's a tiny cost in the great scheme of things.

Enterprise level is more expensive, but it would be.

 

[Isaac]

Azure has a cost but it varies tremendously depending on what you set up. ~£50 a month gets you a scalable, resilient Cloud based SQL server for a basic system, with redundancy and backups. I don't think that's a lot.

~£200 a month gets you something you could run most medium/larger sized businesses on. If you have MS O365 subs and 50 employees it's a tiny cost in the great scheme of things.

Enterprise level is more expensive, but it would be.

That doesn't sound bad at all

 

[dalski]

So is Azure quick when using Access as the front-end? I think I've read quite a few posts stating it is nut. My limited understanding is that MS Access does not run well with online solutions as it was designed to work on local machines/ networks & something to do with packet-sizes on a network being very small. If a record doesn't get updated properly resulting in catastrophic errors. You'll know plenty more than me.

My question is what is the fastest solution? From this post it seems MYSQL is the best option? I think I've seen Remote Desktops being another good solution; VPN's certainly not.

 

[GPGeorge]

Azure has a cost but it varies tremendously depending on what you set up. ~£50 a month gets you a scalable, resilient Cloud based SQL server for a basic system, with redundancy and backups. I don't thin

So is Azure quick when using Access as the front-end? I think I've read quite a few posts stating it is nut. My limited understanding is that MS Access does not run well with online solutions as it was designed to work on local machines/ networks & something to do with packet-sizes on a network being very small. If a record doesn't get updated properly resulting in catastrophic errors. You'll know plenty more than me.

My question is what is the fastest solution? From this post it seems MYSQL is the best option? I think I've seen Remote Desktops being another good solution; VPN's certainly not.

In Post # 1 you indicated, "We dumped MS SQL Server for MYSQL as relational database due to speed we have been using this MYSQL databases with MS Access as front end and has proved to be reliable and our users like the speed compared to MS SQL Server which makes the speed horrible even if you do what is so called tunning the gain is not much."

I have never before seen the claim that a Hosted MySQL database can significantly outperform a comparably configured Azure SQL database, although I've also never heard the reverse, either. Nor have I seen documentation that a locally installed instance of MySQL outperforms a locally installed instance of SQL Server.

So, the question in my mind is where that assertion originated? And in what context was the benchmarking established?

Obviously, a locally installed database, whether MySQL or SQL Server, will outperform a remotely hosted database. But the assertion here is not based on local vs hosted instances.

Did you do the benchmarking internally? Did it come from a consultant or an online source of some kind? Did you compare a locally installed SQL Server instance against a locally installed MySQL instance? Thanks. I'm always interested in others direct experiences and what we can learn from each other.

 

[RonPaii]

So is Azure quick when using Access as the front-end? I think I've read quite a few posts stating it is nut. My limited understanding is that MS Access does not run well with online solutions as it was designed to work on local machines/ networks & something to do with packet-sizes on a network being very small. If a record doesn't get updated properly resulting in catastrophic errors. You'll know plenty more than me.

My question is what is the fastest solution? From this post it seems MYSQL is the best option? I think I've seen Remote Desktops being another good solution; VPN's certainly not.

Any server backend support by Access will work, but may be slower than a "Access" BE depending on how the FE was coded i.e. opening a form linked to a table without a filter. The corruption issue on WAN is only with an "Access" BE.

 

[Minty]

So is Azure quick when using Access as the front-end? I think I've read quite a few posts stating it is nut. My limited understanding is that MS Access does not run well with online solutions as it was designed to work on local machines/ networks & something to do with packet-sizes on a network being very small. If a record doesn't get updated properly resulting in catastrophic errors. You'll know plenty more than me.

My question is what is the fastest solution? From this post it seems MYSQL is the best option? I think I've seen Remote Desktops being another good solution; VPN's certainly not.

We support in excess of 40 databases of various sizes that are hosted in Azure SQL with Access front ends.
Whist it will never be as quick as a local SQL backend, if it is carefully designed the "slowness" is minimal, and with sensible planning can out perform a local Access backend, particularly with complex data operations.

As for the SQL vs MySQL question @nector assertion is simply incorrect. I have never seen any evidence that supports that and can only assume his attempt to use an Azure hosted SQL server was very poorly implemented. I'd like to see the empirical data that backs it up.

EDIT: A correctly set up remote desktop solution is effectively a locally run solution, so will always be quick, but has an associated cost and requires local infrastructure, something a lot of SME's try and avoid.

 

[GPGeorge]

I decided to see what Chatty and Claude, a couple of my AI buddies, have to say about benchmarking performance for various database platforms. I figure they should have access to a lot of that kind of research which I don't have without hours of crawling the web. Unfortunately, it turned out there is not much reliable information available. True, there is a lot of stuff published by one vendor or another regarding how great their own product is. And not so much objective head-to-head comparisons that can be held up as evidence.

On the other hand, both LLMs offered similar advice on how I should go about making valid comparisons. As one would expect, apples to apples and oranges to oranges comparisons are best.

If your primary workflow involves read-only statistical data retrieval for a research website, for example, you have to compare databases in that environment.

If your primary workflow involves heavy transactional workloads, say for a commerce website selling attachments for power tools, you have to compare databases in that environment.

It would be silly to compare MySQL in the cloud running a read-only website to SQL Azure running a commercial site selling shoes. Different workloads, different usage patterns and no valid comparisons. Even the version of the database could have significant impact on results. Again, apples to apples and oranges to oranges or all bets are off.

If your primary workflow involves local processing, you have to install both MySQL and SQL Server on a local server and compare performance in that environment. If your primary workflow involves distributed users connecting remotely to a database, you have to have a remotely hosted MySQL instance to compare to a remotely hosted SQL Server instance. Or whatever your environment happens to call for.

Anything else is a biased attempt at justification for an inherently subjective decision.

Chatty suggested MySQL -- being optimized for data retrieval for websites -- might be expected to outperform SQL Server in that environment, where SQL Server is more optimized to support data transactions and could be expected to perform better under those workloads. Claude was a bit less definitive, but said much the same.

That actually reflects well the thinking behind my request to the OP for exactly how his team benchmarked their implementations of SQL Server and MySQL to arrive at the reported conclusion. It's entirely possible that it was based on objective comparisons in real-world situations. On the other hand, without that kind of verification, it remains an equivocal assertion that has to be questioned, as Minty does in post #13.

 

[Isaac]

Any server backend support by Access will work, but may be slower than a "Access" BE depending on how the FE was coded i.e. opening a form linked to a table without a filter. The corruption issue on WAN is only with an "Access" BE.

And depending on the ODBC driver , I once tried connecting Access with Salesforce and it was slow as molassess. But so is Access with Sharepoint, at least somewhat slow