Backend choice (1 Viewer)

Monardo

Registered User.
Local time
Tomorrow, 00:26
Joined
Mar 14, 2008
Messages
70
Hello,

Probably asked many times, but I guess I could not choose correct keywords in Google to get answer.

So question is simple, I am torn between using either MySQL (or MariaDB) or POSTGRES as a backend for Access. Is there preferred choice and why? Better ODBC driver for either of them? Any other considerations?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:26
Joined
Oct 29, 2018
Messages
21,358
Hello,

Probably asked many times, but I guess I could not choose correct keywords in Google to get answer.

So question is simple, I am torn between using either MySQL (or MariaDB) or POSTGRES as a backend for Access. Is there preferred choice and why? Better ODBC driver for either of them? Any other considerations?
Hi. I'm just curious, Access is a Microsoft product, so why is SQL Server not an option for you?
 

Monardo

Registered User.
Local time
Tomorrow, 00:26
Joined
Mar 14, 2008
Messages
70
Hi. I'm just curious, Access is a Microsoft product, so why is SQL Server not an option for you?

The server PC is Linux and although I read that SQL Server has been ported to certain Linux distros, somehow I don't feel comfortable. Also price?
You are welcome to change my mind?
 

isladogs

MVP / VIP
Local time
Today, 21:26
Joined
Jan 14, 2017
Messages
18,186
The Express version of SQL Server is free and likely to be more than sufficient for your needs ... though I've no idea whether that works in Linux
 

Monardo

Registered User.
Local time
Tomorrow, 00:26
Joined
Mar 14, 2008
Messages
70
I just googled 'mysql v postgresql on linux'

and came up with many threads such as this one

Hi, my question is not about mysql v postgresql in general (on which there are tons of articles), but rather if there is any preference when used in conjunction with MS Access.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:26
Joined
May 7, 2009
Messages
19,169
MySQL is Free, i don't know if it same with postresql.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 21:26
Joined
Feb 19, 2013
Messages
16,553
if there is any preference when used in conjunction with MS Access.
a quick search on this forum for 'postgresql' finds a number of threads mentioning it, but none that expresses a preference.

My only comment would be mySQL is a more established product and postgresql has suffered a number bugs and security issues, apparently fixed.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 08:26
Joined
Jan 20, 2009
Messages
12,849
This page offers perspectives on why one might be chosen over the other of the two.
The main thing that stood out for me was the greater compliance PostGre has with SQL standards.

I work at an MS SQL Server site so I don't have an opinion on the others.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:26
Joined
Feb 19, 2002
Messages
42,976
I've used Access as a FE to a half dozen or more BE databases. My personal favorite is DB2 by IBM but it is not free. Sybase was the worst. I've never used a "free" BE except for SQL Server Express because nothing is actually "free". If something is presented as "free", that means that YOU are the product. In the case of SQL Server Express, MS offers it basically as a learning tool due to its limitations. None of my clients would ever qualify to use it to support a production app so I just use it on my home PCs to allow me to develop there and deploy elseware,

The nice thing about the way Access handles ODBC is that you would have little to no trouble switching the BE after development provided you used linked tables and Access SQL. If you used pass-through queries and stored procedures and views, etc., converting becomes a significant effort. I have one app that I sell that has the option to install an ACE BE or SQL Server and for one client I did a custom Oracle BE that took about an hour to set up. The internal app has only about 20 lines of code that are different for ACE from a "real" server BE and the code worked fine for both SQL Server and Oracle and would probably work for other RDBMS' as well. In fact, I almost always start development with an ACE BE but design with ODBC techniques. That way, I have as little to do with my client's DBA as possible. Once the app is ready for testing, I give the DBA the schema, he builds the test database and I relink to the test db. I do basic testing and then turn it over for the client to test with. Since with large clients, the DBA's are reluctant to allow me to touch "their" server, I rarely get developer access to even the test database. With small clients, I can pretty much do anything I want to do including stealing all their data. It seems to be one extreme to the other. But, given that I'm not a DBA, I prefer to do the basics in Access and only move when the structure is mostly complete.
 

SandyAP

New member
Local time
Today, 21:26
Joined
Oct 17, 2020
Messages
4
I've used Access as a FE to a half dozen or more BE databases. My personal favorite is DB2 by IBM but it is not free. Sybase was the worst. I've never used a "free" BE except for SQL Server Express because nothing is actually "free". If something is presented as "free", that means that YOU are the product. In the case of SQL Server Express, MS offers it basically as a learning tool due to its limitations. None of my clients would ever qualify to use it to support a production app so I just use it on my home PCs to allow me to develop there and deploy elseware,

The nice thing about the way Access handles ODBC is that you would have little to no trouble switching the BE after development provided you used linked tables and Access SQL. If you used pass-through queries and stored procedures and views, etc., converting becomes a significant effort. I have one app that I sell that has the option to install an ACE BE or SQL Server and for one client I did a custom Oracle BE that took about an hour to set up. The internal app has only about 20 lines of code that are different for ACE from a "real" server BE and the code worked fine for both SQL Server and Oracle and would probably work for other RDBMS' as well. In fact, I almost always start development with an ACE BE but design with ODBC techniques. That way, I have as little to do with my client's DBA as possible. Once the app is ready for testing, I give the DBA the schema, he builds the test database and I relink to the test db. I do basic testing and then turn it over for the client to test with. Since with large clients, the DBA's are reluctant to allow me to touch "their" server, I rarely get developer access to even the test database. With small clients, I can pretty much do anything I want to do including stealing all their data. It seems to be one extreme to the other. But, given that I'm not a DBA, I prefer to do the basics in Access and only move when the structure is mostly complete.
Hi Pat
I've just joined the forum. I'm an Access "power user" looking to develop a commercial application using Access as a front end. Can you point me to a good source for "ODBC techniques" so I can also develop using ACE as the backend? For reasons of security, scalability and IT prejudice against Access, I'm likely to deploy using either MYSQL or MS Server Express, neither of which I am familiar with. Which would you recommend and why?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:26
Joined
Feb 19, 2002
Messages
42,976
I haven't seen one. You will find articles that tell you that you must use unbound forms and you must use stored procedures, etc. However, since Access is a RAD (Rapid Application Development) tool, it comes with baggage and if you are willing to accept the baggage, you really ought not to give up all the advantages that bound forms give you by doing all that manual work yourself. So, in my opinion, ignore that advice if you intend to use Access as your FE. Use bound forms and saved querydefs. You might gain some speed advantage to using views in some situations but the views will need to be updateable so you can bind them to forms for updating. You can use a query to select a view and apply criteria to it just as you would to a table. In the 25+ years I've been using Access, I've only written a couple of stored procedures and those were for very complex reports with subreports or for nightly batch processing that I wanted to run on the server rather than on a departmental computer.

The essentials are that you have to understand that a big benefit of using a RDBMS is to take advantage of server side processing and limit the data pulled down by Access. To do that, you bind your forms to queries and those queries include selection criteria that severely limit the number of rows and columns returned. One row is optimal for the main form. Subforms have to bring what they have to bring although you could control that also if necessary. You also need to understand that the server does not have VBA installed and so if you include UDF's or VBA functions in your queries, they cannot be processed by the server and that makes Access have to do some extra work to separate out those parts of your query that the server won't understand. Some VBA functions have SQL equivalents and those are fine. So, depending on where in the query your non-SQL functions appear, Access may have to ask the server to bring down entire tables and then Access will perform the joins and where clauses locally. You want to avoid this at all costs so be aware of how you use functions in your queries. Access will attempt to "pass through" all queries but you can defeat that so be careful. Don't use delete queries that delete all the rows in a table. Use Truncate instead. It simply drops the table and doesn't do any logging. Access runs all queries inside a transaction. You can tell this because you get a message telling you how many rows will be affected and asking if you want to continue. If your query affects too many records, Access won't ask, it will just do the update. So, sometimes, if you have large updates that you want to run, it might be more efficient to run them as pass through queries rather than letting Access embed them in a transaction.

The real power of Access is its ability to link to tables in any data source that supports ODBC. You don't have to understand the technology, you just have to understand the limitations as I mentioned above. As long as you use linked tables and Access queries, you can swap the BE at will. An application that I write initially linked to ACE tables, can in a few minutes be converted by relinking the tables to DB2 or Oracle or SQL Server with no changes. If you have ever worked with multiple RDBMS', you know that they all use their own variant of the SQL language. There is a basic definition of SQL and each RDBMS adds additional features and might use slightly different syntax. If you build an app using VB or C or anything EXCEPT Access and you want to change your BE from SQL Server to Oracle, you have to rewrite all your queries and stored procedures and views. Not so with Access. Querydefs are the equivalent of views and your VBA code serves as stored procedures and those don't change when you swap the BE's unless you are using some special feature.

I would use SQL Server Express since if your clients have SQL Server, they would be able to use their installation of SQL Server instead of Express.

It's not clear if this is an application you are selling to the public or building for a large client. Much as I love Access, it isn't really well suited to a shrink-wrap install although there are tools that can install the Access runtime and set various Windows security values for you. But the real downside is that Access is not a compiled platform so you don't distribute an .exe. VBA is interpreted which is why you need Access (or the runtime) installed and the best you can do is "compile" to an .accde which I recommend that you then rename to .accdr. The .accde can be decompiled to produce your source code so it may be harder to retain control over your intellectual property.

I only have one app that is sold to the public but it is not "shrink-wrapped". It is a high end product for which the clients pay thousands of dollars each year for support and training. It is a niche product so there really isn't much risk in the client attempting to steal the code plus the contract he signs is enforceable so I don't worry about it. The clients can install using an ACE BE or SQL Server. I don't do the SQL Server install, I send a .bak file to the client's DBA and he does the install and sets up al the backups and security. I
 

Isaac

Lifelong Learner
Local time
Today, 14:26
Joined
Mar 14, 2017
Messages
8,738
No offense, but just some practical advice & wisdom: If you can fairly be categorized as "power user" right now, and have next to no knowledge of either serious Access deployment nor back ends, you shouldn't be the person making this decision and taking first point as developer on the project. That just doesn't make sense, and creates a huge risk for your employer, or anyone who relies on your end product.

Databases & apps aren't something you start out on by making your first step be the role of primary developer of a commercially used product.

I see too much of this, and sometimes it makes sense to encourage the person to think twice...I mean, if I became interested in learning how to pilot a plane, my first step would NOT be to take over as the Pilot on a passenger jet from Miami to London.
 

SandyAP

New member
Local time
Today, 21:26
Joined
Oct 17, 2020
Messages
4
I haven't seen one. You will find articles that tell you that you must use unbound forms and you must use stored procedures, etc. However, since Access is a RAD (Rapid Application Development) tool, it comes with baggage and if you are willing to accept the baggage, you really ought not to give up all the advantages that bound forms give you by doing all that manual work yourself. So, in my opinion, ignore that advice if you intend to use Access as your FE. Use bound forms and saved querydefs. You might gain some speed advantage to using views in some situations but the views will need to be updateable so you can bind them to forms for updating. You can use a query to select a view and apply criteria to it just as you would to a table. In the 25+ years I've been using Access, I've only written a couple of stored procedures and those were for very complex reports with subreports or for nightly batch processing that I wanted to run on the server rather than on a departmental computer.

The essentials are that you have to understand that a big benefit of using a RDBMS is to take advantage of server side processing and limit the data pulled down by Access. To do that, you bind your forms to queries and those queries include selection criteria that severely limit the number of rows and columns returned. One row is optimal for the main form. Subforms have to bring what they have to bring although you could control that also if necessary. You also need to understand that the server does not have VBA installed and so if you include UDF's or VBA functions in your queries, they cannot be processed by the server and that makes Access have to do some extra work to separate out those parts of your query that the server won't understand. Some VBA functions have SQL equivalents and those are fine. So, depending on where in the query your non-SQL functions appear, Access may have to ask the server to bring down entire tables and then Access will perform the joins and where clauses locally. You want to avoid this at all costs so be aware of how you use functions in your queries. Access will attempt to "pass through" all queries but you can defeat that so be careful. Don't use delete queries that delete all the rows in a table. Use Truncate instead. It simply drops the table and doesn't do any logging. Access runs all queries inside a transaction. You can tell this because you get a message telling you how many rows will be affected and asking if you want to continue. If your query affects too many records, Access won't ask, it will just do the update. So, sometimes, if you have large updates that you want to run, it might be more efficient to run them as pass through queries rather than letting Access embed them in a transaction.

The real power of Access is its ability to link to tables in any data source that supports ODBC. You don't have to understand the technology, you just have to understand the limitations as I mentioned above. As long as you use linked tables and Access queries, you can swap the BE at will. An application that I write initially linked to ACE tables, can in a few minutes be converted by relinking the tables to DB2 or Oracle or SQL Server with no changes. If you have ever worked with multiple RDBMS', you know that they all use their own variant of the SQL language. There is a basic definition of SQL and each RDBMS adds additional features and might use slightly different syntax. If you build an app using VB or C or anything EXCEPT Access and you want to change your BE from SQL Server to Oracle, you have to rewrite all your queries and stored procedures and views. Not so with Access. Querydefs are the equivalent of views and your VBA code serves as stored procedures and those don't change when you swap the BE's unless you are using some special feature.

I would use SQL Server Express since if your clients have SQL Server, they would be able to use their installation of SQL Server instead of Express.

It's not clear if this is an application you are selling to the public or building for a large client. Much as I love Access, it isn't really well suited to a shrink-wrap install although there are tools that can install the Access runtime and set various Windows security values for you. But the real downside is that Access is not a compiled platform so you don't distribute an .exe. VBA is interpreted which is why you need Access (or the runtime) installed and the best you can do is "compile" to an .accde which I recommend that you then rename to .accdr. The .accde can be decompiled to produce your source code so it may be harder to retain control over your intellectual property.

I only have one app that is sold to the public but it is not "shrink-wrapped". It is a high end product for which the clients pay thousands of dollars each year for support and training. It is a niche product so there really isn't much risk in the client attempting to steal the code plus the contract he signs is enforceable so I don't worry about it. The clients can install using an ACE BE or SQL Server. I don't do the SQL Server install, I send a .bak file to the client's DBA and he does the install and sets up al the backups and security. I
Pat, thanks very much for your detailed response. As always, the more I learn, the more my circle of ignorance grows! That said, I am a problem solver and I will investigate the challenges and address the problems one by one till I arrive at a feasible solution. At the moment I am still scoping out what my approach will be.

My application is intended for general sale to commercial businesses. I believe I can take my many years’ experience in my field of expertise to create a software application which offers new approaches and powerful features not present in rival software applications. Once I have my developed and tested my prototype, I plan to do field testing in parallel with the current systems of a few willing companies.

My experience of developing similar but less fully featured apps, leads me to believe that my application will not be heavy on data processing and the use of pass through queries will be unnecessary. Indeed, I expect the majority of my customers will currently be using very basic systems based on MS Word and MS Excel, or at worst, horrendously complex MS Excel applications!

I have always realised that the new features in my application could be easily mimicked by rival software companies just looking at promo materials. I hadn’t however appreciated that a compiled application could be decompiled/hacked so easily. There are one or two enormously tricks I do in Access which I would like to remain secret and which could only be achieved otherwise by enormous processing power. I will be investigating the various companies offering Access deployment and security packages.

Thanks for not entertaining my mention of MySQL.
 

SandyAP

New member
Local time
Today, 21:26
Joined
Oct 17, 2020
Messages
4
No offense, but just some practical advice & wisdom: If you can fairly be categorized as "power user" right now, and have next to no knowledge of either serious Access deployment nor back ends, you shouldn't be the person making this decision and taking first point as developer on the project. That just doesn't make sense, and creates a huge risk for your employer, or anyone who relies on your end product.

Databases & apps aren't something you start out on by making your first step be the role of primary developer of a commercially used product.

I see too much of this, and sometimes it makes sense to encourage the person to think twice...I mean, if I became interested in learning how to pilot a plane, my first step would NOT be to take over as the Pilot on a passenger jet from Miami to London.
Thanks for the advice Isaac. I think in life, those who stand tallest are the ones with Dunning Kruger syndrome. I’m not one of these. The more I learn the more my circle of ignorance grows. It will be my first commercial product, but I have developed several sophisticated database apps which have proven their worth and robustness of a number of years. I’m now on a new learning curve.
 

isladogs

MVP / VIP
Local time
Today, 21:26
Joined
Jan 14, 2017
Messages
18,186
I have always realised that the new features in my application could be easily mimicked by rival software companies just looking at promo materials. I hadn’t however appreciated that a compiled application could be decompiled/hacked so easily. There are one or two enormously tricks I do in Access which I would like to remain secret and which could only be achieved otherwise by enormous processing power. I will be investigating the various companies offering Access deployment and security packages.

For a detailed article, related code & an example app outlining a wide range of security features, see:

And, of course, I also have a number of other 'tricks' not mentioned in the article!
 

Isaac

Lifelong Learner
Local time
Today, 14:26
Joined
Mar 14, 2017
Messages
8,738
Thanks for the advice Isaac. I think in life, those who stand tallest are the ones with Dunning Kruger syndrome. I’m not one of these. The more I learn the more my circle of ignorance grows. It will be my first commercial product, but I have developed several sophisticated database apps which have proven their worth and robustness of a number of years. I’m now on a new learning curve.
Oh, ok, glad to hear I was mistaken. I think your "the more I learn" sentence is very wise, this is exactly how I see the world of technical design as well; the more I learn, the deeper I realize it goes and the more I understand how low I rank--odd thing, technology.
 

Users who are viewing this thread

Top Bottom