Discussion Locked

Get ADO event when database is changed?

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

We have a database-client Application an we are using MSDE/SQL -Server 7.0 as Server. We want to get an Event for exmple when a new record is added to the database or deleted by another application.
We are using VB6 SP4 for development. We have already tried using ms ADODC Control or
Ado Recorset with events and Serversided cursor location.
Has anybody an idea how it works.

5 replies

by glen_mcleod

In order to do this in pure ADO, you're going to have to have a connection open to the database at all times and a dataset open on each table at all times as well, which is not the best of ideas.

Using the 'withevents' keyword is a good start, but server-side won't work, it'll have to be client-side.

Personaly, I'd use a timestamp column for detecting additions. You can cache the latest timestamp locally, and check for larger values each time you connect to each table.

For deleteions,I'd think that a locally cached 'Count(*)' for comparisons might be the way to go.

This solution won't give you the automatic events that you're looking for. You may want to look into MSDE/SQL Server triggers to call a server side .DLL that you can declare 'with events' from your client code.

Glen

by thomas seeger

We have written our own OLEDB-Provider for another datosource and with this provider it works we get events from the provider by using the adodc Serverside Cursor and Cursortype = OpenDynamic.
Is this feature not supportet from the SQLServer OLEDB-Provider .

The trigger is way but difficult if your appliction is running on 5 pc?s and you have only one SQl Server

by shmaltz

I whould use a trigger that updates a table (or even just a text file) that the database has change and then use ADO to check for the value of the table or text file to see if it has change.
I think that to use a text file is the best idea since the ADO will not have to connect to the database to check if it has change.
To do the above add a trigger that runs the 'Master..xp_cmdshell' to create a text file with the echo command and that file should contain the data that it has changed.
However be aware that you might run into errors if the SQL server tries editing the file when the program is reading it. For that reason its best to test to see if a text file already exists if it does than you should have the SQL server creating a file with a new name.

This discussion is closed to new replies.