How to ensure DB changes complete?

17 Sep 2006 3:03 AM

Gardner Andersen

I have two apps that run on different machines. The first app inserts a
row to an MS Access table using an ODBCDataAdapter object. Then, it sends
a message to the second machine that a new row has become available using a
winsock object. Finally, the second app receives the message and reads the
new row.

Problem is...

The actual data update completes codewise on app #1, but the message is
received and the data read in app #2 before MS Access is done taking in the
new data (I guess).

I can tell this because the data is actually added, but the second app
doesn't see it unless I put in an arbitrary delay.

I'd like to avoid unnecessary delays, plus there is the question of whether
or not the delay is not long enough at one point (because maybe the DB is
busy), what happens.

Ideas?

17 Sep 2006 5:08 AM

Gardner Andersen

This is *crazy* behavior! Here's my sequence of events now:

App #1 - Write new record using ODBCDataAdapter

App #1 - read # of rows in table using ExecuteScalar - get 21 rows

App #1 - send message to app #2 using winsock object

App #2 - receive message

App #2 - read # of rows in table - get 20 (yes, TWENTY)

check DB manually, contains 21 rows

If I put in a msgbox to simulate an arbitrary delay of a second or so,
then App #2 reads the correct number of rows. What amazes me is that
App #1 sees the proper # of rows, passes off to app #2 and it doesn't.
I'm so confused that my frickin' head hurts.

Gardner Andersen <bolob***@hotmail.com> wrote in
Show quoteHide quote

news:Xns983A158903Fbolobabyhotmailcom@216.196.97.136:

> I have two apps that run on different machines. The first app inserts
> a row to an MS Access table using an ODBCDataAdapter object. Then, it
> sends a message to the second machine that a new row has become
> available using a winsock object. Finally, the second app receives
> the message and reads the new row.
>
> Problem is...
>
> The actual data update completes codewise on app #1, but the message
> is received and the data read in app #2 before MS Access is done
> taking in the new data (I guess).
>
> I can tell this because the data is actually added, but the second app
> doesn't see it unless I put in an arbitrary delay.
>
> I'd like to avoid unnecessary delays, plus there is the question of
> whether or not the delay is not long enough at one point (because
> maybe the DB is busy), what happens.
>
> Ideas?

17 Sep 2006 10:14 AM

Theo Verweij

Change the setting 'Safe Transactions' from 0 to 1 in the advanved
settings of the Access ODBC Driver.

Gardner Andersen wrote:
Show quoteHide quote

> This is *crazy* behavior! Here's my sequence of events now:
>
> App #1 - Write new record using ODBCDataAdapter
>
> App #1 - read # of rows in table using ExecuteScalar - get 21 rows
>
> App #1 - send message to app #2 using winsock object
>
> App #2 - receive message
>
> App #2 - read # of rows in table - get 20 (yes, TWENTY)
>
> check DB manually, contains 21 rows
>
> If I put in a msgbox to simulate an arbitrary delay of a second or so,
> then App #2 reads the correct number of rows. What amazes me is that
> App #1 sees the proper # of rows, passes off to app #2 and it doesn't.
> I'm so confused that my frickin' head hurts.
>
>
>
> Gardner Andersen <bolob***@hotmail.com> wrote in
> news:Xns983A158903Fbolobabyhotmailcom@216.196.97.136:
>
>> I have two apps that run on different machines. The first app inserts
>> a row to an MS Access table using an ODBCDataAdapter object. Then, it
>> sends a message to the second machine that a new row has become
>> available using a winsock object. Finally, the second app receives
>> the message and reads the new row.
>>
>> Problem is...
>>
>> The actual data update completes codewise on app #1, but the message
>> is received and the data read in app #2 before MS Access is done
>> taking in the new data (I guess).
>>
>> I can tell this because the data is actually added, but the second app
>> doesn't see it unless I put in an arbitrary delay.
>>
>> I'd like to avoid unnecessary delays, plus there is the question of
>> whether or not the delay is not long enough at one point (because
>> maybe the DB is busy), what happens.
>>
>> Ideas?
>

17 Sep 2006 6:02 PM

Gardner Andersen

Thanks, but that solution produced no change in behavior.

I have decided to append all of the data to the message being sent to
second application. I'll just get the data from there for the time being.

Theo Verweij <tverw***@xs4all.nl> wrote in news:e0v1YIk2GHA.3516
@TK2MSFTNGP06.phx.gbl:

Show quoteHide quote

> Change the setting 'Safe Transactions' from 0 to 1 in the advanved
> settings of the Access ODBC Driver.
>

18 Sep 2006 5:13 AM

GS

look into ODBCDataAdapter and see if there is something like transaction
end or end of transaction you can use to flush the new record to database
before passing off to App#2 via winsock.

Oh, btw , I think there is something called async execution that supposedly
allows you flexibility of taking of there thing instead of waiting for a
database completion.. And this may be the source of your pain!

Show quoteHide quote

"Gardner Andersen" <bolob***@hotmail.com> wrote in message
news:Xns983A158903Fbolobabyhotmailcom@216.196.97.136...
> I have two apps that run on different machines. The first app inserts a
> row to an MS Access table using an ODBCDataAdapter object. Then, it sends
> a message to the second machine that a new row has become available using
a
> winsock object. Finally, the second app receives the message and reads
the
> new row.
>
> Problem is...
>
> The actual data update completes codewise on app #1, but the message is
> received and the data read in app #2 before MS Access is done taking in
the
> new data (I guess).
>
> I can tell this because the data is actually added, but the second app
> doesn't see it unless I put in an arbitrary delay.
>
> I'd like to avoid unnecessary delays, plus there is the question of
whether
> or not the delay is not long enough at one point (because maybe the DB is
> busy), what happens.
>
> Ideas?