Access as an interface
Well, people have a lot to do with Access. The main reason for the negative opinion is that it is not widely used by developers who do not have the concept of proper database design and end with these barely working, terribly designed applications that bring horror to the heart of developers.
Having said that, Access is nothing more than a quick application development tool with a very low entry barrier.
Good and bad
Access is a fairly old product line spanning nearly 20 years. Many people object to access as an technology based on an early history: since backward compatibility is what Microsoft considers important, Access has retained most of its features, good or bad, for many years.
You can see the choice of VBA, non-winform forms, the promotion of modules over OOP to be a poor choice, but the continuity of these decisions at an early stage made access to a stable Access platform.
VBA: This is a language that never dies. It is clumsy, outdated, lacking the latest achievements in languages, but it is also fast, easy to learn, flexible, easy to use (for example, calling the Win32 API is very simple), and it can be interfaced with external libraries (the event is written in. Net )
Related forms: By default, Access makes it easy to create working applications without a line of code. Most queries can also be created without having to access SQL at all. If you are a control freak, it can be annoying, but it's easy to dump code and control everything from there.
Third-party integration: Despite the fact that Access has many add-ons, I would not recommend using most user controls or third-party libraries if you do not need it. They are very difficult to deploy in an environment with limited user rights, and version control can become hairy.
Tape: it can be good or bad, depending on who you are talking to, but Microsoft has invested a lot in it, and it will probably be there for a while. At the very least, the ribbon and advanced controls in Access 2007 and later versions of the application look and behave in a modern way. The horrible user interfaces of the old have passed, now you can do really beautiful things with themes, HTML layouts and a modern ribbon.
Reliability
Most objections, especially about reliability, are simply no longer true. A carefully designed Access application can support dozens of concurrent users. I have a decent size application that manages procurement / stock / quality / parts / projects for a manufacturing company that has 150 users, of which 50 are usually connected at any given time. I have not had any corruption for many years.
Of course, you should always remember that Access is a multi-user file database, so you cannot expect it to work without risk in environments with unreliable or slow networks and connecting to the WiFi access firewall really poses problems, just like You will not work with a large Excel file wirelessly.
Maintenance is part of the application life cycle. Preventative maintenance is extremely important.
Do not wait for something to go wrong: create some administration tools in your application that will help you check the status of your data (make sure everything is consistent, detects invalid user inputs, etc.). Also compress the database regularly (I do this every night after creating the backup as part of the daily automated tasks on the backend server).
Some random clues
- Make sure that your front-end and back-end are separate: only the data should be on the backend, and the front should be installed on each user computer.
- In your interface, open a permalink to a dummy table in the database: a permanent connection will significantly improve performance. For this reason, see question .
- The backend should be in a shallow network resource (do not put it deep in multiple directories).
- Make sure that you are serious about how you will automatically deploy your updates to all customers.
There are many ways to do this. I developed my own, but you can use ClickOnce or Tony Toews Autostart updates - Use Runtime : Your external application can run without user intervention with its internal components if you deploy Access runtime on your computer. He is also free.
- Don't fight the tool: access has a certain way to do something. Just use the available tools without code until you have exhausted the capabilities of the RAD environment. You may be surprised at how much you can achieve without any code whatsoever.
- Nothing prevents you from using OOP: defining classes and manually binding your classes to your forms, managing data updates, etc. Access’s default behavior tends to be fast-paced and great for small projects, but if you suspect your project might grow, plan for the future just like you would with any other infrastructure.
Access as a database database
Now, if you want to use an external interface written in something else, say, C #, it is quite simple to use Jet / ACE drivers to connect to the Access database.
- Jet is an older driver for Access and only supports
.mdb files. 32-bit drivers are installed by default on Windows (still in Win8), and you can always rely on them. - ACE is the new
.accdb format used by Access2007 / .accdb . It has increased limits and improvements over the older version, but it can still talk with .mdb .
You will need to install the Microsoft Database Engine for all clients (not necessary if the full version or Runtime of Access 2007/2010/2013 is already installed). - Do not attempt to use 64-bit versions of the Access / ACE driver. This is the future, but it introduces many new problems, especially if you use third-party libraries or some of your clients have 32-bit Office installed (you cannot mix and match 32-bit and 64-bit office components). Similarly, do not build your external C ++ / C # / Java / Python application in 64-bit mode and expect the 32-bit ACE driver to work. Keep everything in 32 bit, do something working, and then check out 64 bit versions if you really need to.
- Always keep, as stated above, at least one database connection from your code.
- Ideally, try to ignore any access-specific code to simplify it later if you need to switch to a server database. You can use ORM, which can talk transparently with various reverse content, or you could at least separate your SQL queries from resource files or individual objects, which can be easily replaced later.
- Depending on the load (basically, how much data needs to be changed in the database), the Access client interface can easily accommodate 20-100 users without problems. Everything will get worse if there are a lot of investments and updates happening all the time. There are many tricks that you can use to do better and scale access well, but you are essentially limited by the type of application you are building and how users access their data.