Simultaneous access to MS Access

[bpel]

I've been asked to write a program for a small company. They will have just a few users (1 to 4) accessing a database. The database will probably grow to just 20,000 records at the most. If multiple users are accessing simultaneously and one of them adds or updates a record, they want the screens of the other users to be refreshed and reflect the newly added data.

My thought is to write the application in MSVC++ and connect to an MS Access DB using ODBC. My dilemma is in deciding the best way to handle the updating of all the users screens when one user has added a record.

1) I could have the application spawn a thread, which would kickoff a timer. Say every 20 seconds, the timer would run a query against the DB and see if any records have been added. If so, it's time to refresh the data here on this machine.

2) I could also maybe write a service that would run on the server with the database. The application on each users machine would interact with this service, not the database. The service would accept all the requests from the users (clients), interact with the DB and respond to the clients. If a record is added or updated, the service would send a msg or invoke a callback on the client and tell it to update.

3) I'm not sure about this, but I think there also might be some broadcast function/message that I can use.

Does anyone have any suggestions or warnings or previous experience with this?

Thanks very much!
Brian

[cjard]

either poll the db, use sql server and event notification services, or perform a network braodcast that will stimulate the other machines to update.

don't use access for a networked database; you're asking for trouble.. use oracle express or sqlserver. they were written for networked multi user access, rather than storing grandma's recipe collection

[dannystommen]

2) I could also maybe write a service that would run on the server with the database. The application on each users machine would interact with this service, not the database. The service would accept all the requests from the users (clients), interact with the DB and respond to the clients. If a record is added or updated, the service would send a msg or invoke a callback on the client and tell it to update.

I think the best way is doing this like you offered your self

[KrisSimonis]

If you really MUST use MS Access, go with your option 2.
Otherwise, I would highly recommend using a different database to store your data. Since it's only a small company, MySQL is probably the best solution, since it's free, and can handle what you need.

You could use MS SQL Server or Oracle, but that's wasting a lot of money on licencing here IMHO.

[dannystommen]

You could use MS SQL Server or Oracle, but that's wasting a lot of money on licencing here IMHO.

MS SQL Server Express is free and would also be good for this situation

[Alsvha]

MS SQL Server Express is free and would also be good for this situation

Especially because with SQL Dependency, you can be notified in your client application when updates happen in the database and thus all connected clients can be updated by that event without having to do a lot of extra work.

(Perhaps this can be done in MySQL but I'm not terrible used to working with that)

[dglienna]

What's wrong with a 3-tier system? Keep a system in the middle. It can only access the server with Access running, doing the queries.

The third tier gets data from the second machine.

Run everything thru tier 2 and you can tell what's been changed, and when to refresh

[Alsvha]

What's wrong with a 3-tier system? Keep a system in the middle. It can only access the server with Access running, doing the queries.

The third tier gets data from the second machine.

Run everything thru tier 2 and you can tell what's been changed, and when to refresh

Well, for one it provides a level of complexity which isn't necessary if choosing different tools for the task
It would require the middle system to know when data is changed by parsing/analysing the existing data with the data of each incoming data manipulation functions, keep track of all users and alert them of changes etc.
While of course possible - it just seems to me that when such complexity is introduced to solve the problem with the tools available - it is most likely the wrong tools for the job

(if it isn't a school project or the tools are 100% decided/given )

[George1111]

I agree that complexity needs to be removed wherever possible

There is a statement in VB6 used to Redimension an array - so you could start with an array of 1 and keep redimensioning it every time you added an element to the array

On the other hand, if you knew that you could never have more than 1000 items in the array (ever) then you could define the array at 1000 and be done

This is called eliminating complexity



Similarly with your problem, what if the data did change - then every machine needs to be refreshed

What if every machine was refreshed periodically even if the data didn't change ?

What is lost ? Nothing

In simple terms - if an operator touches a machine then refresh the data, otherwise refresh the data on a periodic basis

This is not a pure solution, but the most simple


=============================================

Another, more exact solution is as follows

Say you have 10 machines in a network, all needing to be refreshed as the data changes

Say machine 3 causes the change to the data

1) The central database is changed (preferable SQL Server Express)
2) Machine 3 writes out to a specific folder (on the server) 9 files 1.txt 2.txt 4.txt 5.txt 6.txt 7.txt 8.txt 9.txt 10.txt containing nothing of importance - perhaps the time
ALTERNATELY - machine 3 writes to a folder on each of the other 9 machines, which is being "watched" by the program
3) Every machine is running a "watch" on that specific folder and will be triggered to run a routine if something is changed in the directory
4) Machines check if their value (eg, Machine 1 checks for 1.txt to exist) if their value exists, the data is refreshed on their screen and the 1.txt is deleted

Since even machine 3 is running a "watch" it will also go looking for 3.txt but will not find it (its data has already been refreshed)

Now this technique may not appeal to the purists out there, but gee - it will certainly work, and could be programmed by a novice