I have inherited an application that stores data in multiple tables in Microsoft Access; the Access DB is only used for storage, all data processing is handled by the app (VB.net).
Is this good practice? I need to build a similar app from scratch and am unsure of what is 'normal' practice.
I have tried Googling the question in various formats and checked through numerous results (including many on this forum) but all questions seem to relate to where you are programming within Access rather than simply using it for data storage.
I guess I could be more specific and say would SQL Server Express or similar be a better option and if so why?
If you are reading this post due to having a similar question then check out this link provided by @DanielB below – https://stackoverflow.com/questions/694921/ms-access-mdb-concurrency
Best Answer
MSAccess databases aren't typically used for a couple reasons. In the past, they were flaky and didn't accept multiple connections at once. When the MSAccess database is being used, a lock file is created (ldb). When that lock file is present, no one else can access the database. I found that when there was a single use application, MSAccess's performance would severely degrade after about 50k rows. This is probably better now, but it certainly wasn't tuned for larger uses.
What is more typical is to use a more robust database system like postgres, mysql, or MSSQL. For single connection databases, I've used Derby (with Java).
As far as VB goes, you won't find professional solutions using VB as client software to a database. Well, perhaps there are some solutions being sold, but personally, I would avoid them.
Typically, your processing will be done in a language like C#, C++, Java, Perl, Python, or other popular languages. Libraries will be used to connect to the database that are separate from the language. Some solutions will use SQL to query and receive data, and other solutions will use a Persistence library to create objects from the data (this is becoming more common).
As far as best practice goes, I've always found it best to be consistent. If you have a shop of four people that understand MSAccess and VisualBasic, then it makes a lot of sense to continue doing it this way. If there is a goal in the company to move away from it due to failures in the past, you can keep using VB and switch to another database. Look into Linking Tables in MSAccess - I used a VB application on an MSSQL database by linking the MSSQL tables into an MSAccess database. The VB didn't know the difference between a naitive MSAccess table, and a linked table located on another server. The VB solution was still flaky, but worked much better with larger datasets.
Hope this helps!