Member Avatar for //Gonz

I have written a program which allows agents of a call centre the ability to access a database (ms access) containing names of solicitors to allocate to.
the database is stored centrally and i need a way to update the local dataset on the agents machine when a change is made to the database.

I understand how to add, remove, update fields to the database using a OleDbDataAdapter but I need something that does the reverse.

Thanks in advance.

  • 2 Contributors
  • 3 Replies
  • 199 Views
  • 1 Hour Discussion Span
  • Latest Post Latest Post by //Gonz
Member Avatar for nmaillet

Access provides a file based database. This is quite different than an Oracle or SQL Server database. The latter have several processes which work as a middle-man between the client and the physical file(s). This would allow (I certainly should be possible, albeit I've never attempted it) the processes to see the updates to specific table, and notify connected clients (assuming the connections actually remain open). An Access database, on the other hand, has several processes (client applications) making changes directly to the file, and no real method of pushing a notification to any of the other processes.

As an aside, I've heard that you shouldn't use SQL Server Compact (another file based database) databases on network drives (if that's how you're currently doing it), as the file locking can be flakey, which could cause some concurrency issues. I don't know if Access has some method of overcoming this, but you should look into that.

So, you have a few options (non very ideal), other than moving to another database platform:

  • Write a custom server application as a wrapper for the database, that is able to manage the connections and push out notifications.
  • Poll the database for changes.
  • Have each client application connect to each other (or a central location, if possible) to push updates to each other. I wouldn't really recommend this though.

Unless anybody has some ideas, that's all I can think of. Good luck.

Member Avatar for //Gonz

thanks so much for your help mmaillet. I will have a think into which solution you suggested would be most adequate.

thanks again

Member Avatar for //Gonz

I have come across a relatively simple way of doing this. Use a FileSystemWatcher to watch the .accdb file then in the event of someone accessing the database, re fill the dataset.
I hope this will help anyone else with the same issue.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.