Automatically observing changes in database tables

Question

currently I'm working on a project with the following setting:

• There is a proprietary application which performs transactions on a Microsoft Access database (which is actually stored as an .mdb file)
• The API of the application doesn't have enough functionality for the requirements we're facing
• Our application has direct access to the database

We're designing a C# application which has to react to events which are not triggered through the API, so the only way I can think of is to somehow observe the database directly and react accordingly to custom events.

One approach would probably be to query the DB time triggered and react, but this possibly leads to performance issues when the DB is large. I would rather appreciate an approach, where the DB itself notifies our application if specified events occur.

EDIT: The proprietary application is Enterprise Architect modeling tool, so the expected number of concurrent users will definitely not exceed 10 users and the database size will not be larger than 50 MB. Would it probably be an acceptable solution to outsource the polling activity in a separate thread and notify the rest of the application by events?

Concerning the number of users and database size, which load can the MS Access database approximately handle?

Is there any well-known pattern/approach for this kind of problem available and if you don't know any, how would you face this problem?

Answer 1

The most common solution for this kind of problem is: try to design your C# application in a way it does not have to react to changes within your database. Even when using a "real" client/ server database where you have things like triggers available, most client/server applications avoid the need for a "push model" where the server has to send a "trigger event" to the client. Indeed, there are only few databases which provide such a mechanism. At least client applications typically don't rely on getting all events, since the network between client and server may fail sometimes.

Of course, I don't know your requirements, and how much scope is left for dicussing them, and some requirements can be really hard to be implemented without an appropriate event mechanics.

If you really need this, I guess polling is the only option you have.

Would it probably be an acceptable solution to outsource the polling activity in a separate thread and notify the rest of the application by events?

Technically: probably yes, but you have to measure the performance for your specific case. Crucial factors are how much data has to be scanned in each cycle, and which latency is acceptable (seconds? ok. miliseconds or fewer? may become hard).

Answer 2

Microsoft Access(2010+) have data macros which are like triggers, but I don't think they run unless an Access application is running or if an ODBC connection can recognize them. You may not have the ability to add these.

It's not too much to have a service pole the database. As far as your performance concerns, the frequency may need to be less than you prefer. The number of concurrent users is going to affect this as well. You have no control over this third party app to optimize performance on that end.

I have no idea if this is a pattern.

Answer 3

Acess will let you use linked tables to tables in another db especially SQL server. Is it possible to migrate your data to such? Then you can use triggers or jobs to do the montioring you need.