8

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

Community
  • 1
OSKM
  • 199
  • 1
  • 7
  • 2
    Sharing your research helps everyone. Tell us what you've tried and why it didn’t meet your needs. This demonstrates that you’ve taken the time to try to help yourself, it saves us from reiterating obvious answers, and most of all it helps you get a more specific and relevant answer. Also see [ask] – gnat Dec 11 '13 at 12:26
  • 1
    @gnat i trust the added detail above prevents any more down votes, to be clear if anyone can link me to an answer that genuinely answers the question i will be delighted as i have spent several hours trying to locate this before resorting to asking the question. (i know i should have said this originally above but i forget that not all question askers on this forum take the need for research before asking for granted, some seem to think it is a help file!?!) – OSKM Dec 11 '13 at 12:47
  • MS Access vs. SQL Server Express: see here http://stackoverflow.com/questions/5704654/sql-server-express-vs-ms-access But you might also take SQL Server compact edition into consideration, that is much more similar to Access than SS Express. – Doc Brown Dec 11 '13 at 13:49

5 Answers5

8

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!

Kieveli
  • 660
  • 6
  • 10
  • Many thanks for the answer, my only issue with it is "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" on the inherited application they have 4 concurrent users (app on local machine db on shared drive) and no issues with being locked out. Useful comment on the row no's though and i will now research your other suggestions. – OSKM Dec 11 '13 at 13:00
  • I moved away from access quite some time ago, so I wouldn't be surprised if my details are stale. Currently I'm using Java with Postgres. – Kieveli Dec 11 '13 at 13:10
  • 2
    @OSKM I believe the ldb file is used when a write is happening (to clarify - it's used all the time, but will typically only "lock users out" when a write is happening). This question on SO about [MS Access (MDB) concurrency](http://stackoverflow.com/questions/694921/ms-access-mdb-concurrency) goes into a lot more detail. In general, the "file share over network" model can work, but has many issues as the number of users scale. – Daniel B Dec 11 '13 at 13:18
  • 5
    -1, you should really delete that wrong facts about MS Access from your answer, like the "supports only one user at a time" or "get slower after >50k rows", those things are plain wrong. And suggesting a full client server DB as an alternative, with full administrative overhead, is IMHO not a good suggestion. Access is a fine tool if you know what its good for and for what not. – Doc Brown Dec 11 '13 at 13:56
  • @DocBrown - The information isn't entirely wrong. A lock file is used, it does prevent multiple users from writting to the same file ( at the same time ), and performance can be concerned with Access. – Ramhound Dec 11 '13 at 14:08
  • @DocBrown sorry I disagree assuming that this application or even the organisation is going to grow to any reasonable size Access isn't the technology of choice, granted it may be "fine for now" but by stepping away from it early a great many problems can be avoided in the future. – Mrk Fldig Dec 11 '13 at 14:24
  • 6
    I prefer C#, but VB is okay if someone feels comfortable with it. It gives you access to exactly the same libraries as C# and offers more or less the same programming concepts as C#, even if the grammar and the language concepts are very different. Microsoft said that new language features would be introduced in parallel in both languages in future. – Olivier Jacot-Descombes Dec 11 '13 at 17:11
  • 2
    -1 for the commentary on VB. VB.NET is just as functional as C#, although the syntax is more annoying. I used to work for a development company that exclusively used VB.NET with a MSSQL backend. – Bobson Dec 11 '13 at 19:10
4

I've had some experience regarding this issue in the past in a large corporation.

As user base grows, security and performance issues emerged because the Access files where the data resided had to be stored in a network shared folder, and all users had to have W/R access to it.

This is not theorethical, this is real-world experience, Access databases don't scale well to tens of simultaneous users, let alone a couple of hundreds geographically spread.

With free and good database systems like PostgreSQL available, there's little reason to go with Access except the following: lack of thechnical know how.

Tulains Córdova
  • 39,201
  • 12
  • 97
  • 154
2

Personally if you're able to move away from access do it, I've had clients who have really suffered because they simply couldn't or wouldn't "bite the bullet".

As Kieveli mentioned Access databases are horrible when it comes to storing large amounts of data, remember applications should always be designed with the benefit to the end user in mind. Imagine you've got an office full of people who have a horribly slow system wasting hours of their time every single week?

Speaking from personal experience moving an access database to SQL Server really isn't that painful providing you plan properly, the question is do you have the technical skills in house to move to something like vb.net etc.

Mrk Fldig
  • 232
  • 1
  • 4
1

Access databases don't scale well but they are easy to handle and distribute. If you are using an Object Relational Mapper (O/R-mapper) supporting different database types as an interface to your database, your application will be much less dependent on a specific database type. This allows you to start with Access and to move to another database later.

I did this once and started a project with Access and a C# front-end, because databases can be set up and developed very easily with Access. Later I moved to an Oracle database because my customer has an Oracle database. Only a few changes where required in my application.

Olivier Jacot-Descombes
  • 1,664
  • 10
  • 14
0

Jet-SQL is horribly documented. That alone is a reason for me to stay away from Access.

https://stackoverflow.com/questions/11265817/where-can-i-find-a-complete-reference-for-microsoft-access-sql

T-SQL (SQL Server) on the other hand is much more flexible and you can find tons of documentation, blogs and experts that can help you.

Of course Access is a desktop database while SQL Server is a full blown Client-Server database.

There are some other desktop databases out there (I have been using Advantage Database Server, but that's a niche product, too).

Community
  • 1
Jens Mühlenhoff
  • 109
  • 4