INSERT INTO appears to be asynchronous?

I have an application with two connections to the same Access DB.
On one, I execute an "INSERT INTO", and immediately after, on the
other, I execute a "SELECT", attempting to retrieve the record that
was just added.
But about 5% of the time, the SELECT retrieves 0 records.
If I add a 500ms delay, it seems fine, but this isn't an acceptable
solution for performance reasons.
I've tried the same with SQL server, and no such problems arise.
I'm using the "Microsoft Access Driver (*.mdb)" version 4.00.6304.00.
I haven't made any calls that would allow the "insert" operation to
run asynchronously, but it appears to be...or I suppose alternatively,
the SELECT could be looking at an out-of-date cache (but these seems
unlikely).
Any clues?

re: INSERT INTO appears to be asynchronous?

On Apr 27 2005, 09:40 pm, wizofaus@hotmail.com (Dylan Nicholson) wrote in
news:7d428a77.0504271740.269175a5@posting.google.c om:
[color=blue]
> I have an application with two connections to the same Access DB.
> On one, I execute an "INSERT INTO", and immediately after, on the
> other, I execute a "SELECT", attempting to retrieve the record that
> was just added.
> But about 5% of the time, the SELECT retrieves 0 records.
> If I add a 500ms delay, it seems fine, but this isn't an acceptable
> solution for performance reasons.
> I've tried the same with SQL server, and no such problems arise.
> I'm using the "Microsoft Access Driver (*.mdb)" version 4.00.6304.00.
> I haven't made any calls that would allow the "insert" operation to
> run asynchronously, but it appears to be...or I suppose alternatively,
> the SELECT could be looking at an out-of-date cache (but these seems
> unlikely).
> Any clues?[/color]

Take a look at
http://msdn.microsoft.com/library/de...l=/library/en-
us/office97/html/adjustingwindowsregistrysettingstoimproveperforman ce.asp

especially the FlushTransactionTimeout setting. The article is for Jet 3.5,
but should be applicable to 4.0 as well.

It seems that you use ODBC to get to the Jet database, therefore you cannot
use DBEngine.SetOption to change that setting on the fly, and will need to
make changes to the registry on the machine where your app(s) are running.

--
remove a 9 to reply by email

re: INSERT INTO appears to be asynchronous?

Dimitri Furman wrote:[color=blue]
> On Apr 27 2005, 09:40 pm, wizofaus@hotmail.com (Dylan Nicholson)[/color]
wrote in[color=blue]
> news:7d428a77.0504271740.269175a5@posting.google.c om:
>[color=green]
> > I have an application with two connections to the same Access DB.
> > On one, I execute an "INSERT INTO", and immediately after, on the
> > other, I execute a "SELECT", attempting to retrieve the record that
> > was just added.
> > But about 5% of the time, the SELECT retrieves 0 records.
> > If I add a 500ms delay, it seems fine, but this isn't an acceptable
> > solution for performance reasons.
> > I've tried the same with SQL server, and no such problems arise.
> > I'm using the "Microsoft Access Driver (*.mdb)" version[/color][/color]
4.00.6304.00.[color=blue][color=green]
> > I haven't made any calls that would allow the "insert" operation to
> > run asynchronously, but it appears to be...or I suppose[/color][/color]
alternatively,[color=blue][color=green]
> > the SELECT could be looking at an out-of-date cache (but these[/color][/color]
seems[color=blue][color=green]
> > unlikely).
> > Any clues?[/color]
>
> Take a look at
> http://msdn.microsoft.com/library/de...l=/library/en-
>[/color]
us/office97/html/adjustingwindowsregistrysettingstoimproveperforman ce.asp[color=blue]
>
> especially the FlushTransactionTimeout setting. The article is for[/color]
Jet 3.5,[color=blue]
> but should be applicable to 4.0 as well.
>
> It seems that you use ODBC to get to the Jet database, therefore you[/color]
cannot[color=blue]
> use DBEngine.SetOption to change that setting on the fly, and will[/color]
need to[color=blue]
> make changes to the registry on the machine where your app(s) are[/color]
running.[color=blue]
>[/color]
I've tried adjusting all of FlushTransactionTimeout, SharedAsyncDelay,
ImplicitCommitSync, UserCommitSync, tried it with and without explicit
transactions (auto-commit mode on/off etc.), and all with no success.
Assuming that is, that this can all be set via the ODBC connection
string (I'm not using DSNs per se).
I suspect it is something that maybe can only be controlled via the Jet
engine interface directly, but we have no intention of changing the
code this much.

re: INSERT INTO appears to be asynchronous?

On Apr 28 2005, 09:19 pm, wizofaus@hotmail.com wrote in
news:1114737565.563075.108890@l41g2000cwc.googlegr oups.com:
[color=blue]
>
> Dimitri Furman wrote:[color=green]
>> On Apr 27 2005, 09:40 pm, wizofaus@hotmail.com (Dylan Nicholson) wrote
>> in news:7d428a77.0504271740.269175a5@posting.google.c om:
>>[color=darkred]
>> > I have an application with two connections to the same Access DB.
>> > On one, I execute an "INSERT INTO", and immediately after, on the
>> > other, I execute a "SELECT", attempting to retrieve the record that
>> > was just added.
>> > But about 5% of the time, the SELECT retrieves 0 records.
>> > If I add a 500ms delay, it seems fine, but this isn't an acceptable
>> > solution for performance reasons.
>> > I've tried the same with SQL server, and no such problems arise.
>> > I'm using the "Microsoft Access Driver (*.mdb)" version
>> > 4.00.6304.00. I haven't made any calls that would allow the "insert"
>> > operation to run asynchronously, but it appears to be...or I suppose
>> > alternatively, the SELECT could be looking at an out-of-date cache
>> > (but these seems unlikely). Any clues?[/color]
>>
>> Take a look at
>> http://msdn.microsoft.com/library/de...l=/library/en-
>>[/color]
> us/office97/html/adjustingwindowsregistrysettingstoimproveperforman ce.asp[color=green]
>>
>> especially the FlushTransactionTimeout setting. The article is for Jet
>> 3.5, but should be applicable to 4.0 as well.
>>
>> It seems that you use ODBC to get to the Jet database, therefore you
>> cannot use DBEngine.SetOption to change that setting on the fly, and
>> will need to make changes to the registry on the machine where your
>> app(s) are running.
>>[/color]
> I've tried adjusting all of FlushTransactionTimeout, SharedAsyncDelay,
> ImplicitCommitSync, UserCommitSync, tried it with and without explicit
> transactions (auto-commit mode on/off etc.), and all with no success.
> Assuming that is, that this can all be set via the ODBC connection
> string (I'm not using DSNs per se).[/color]

Are you saying that you are somehow including those settings in the
connection string? That definitely doesn't work.
[color=blue]
> I suspect it is something that maybe can only be controlled via the Jet
> engine interface directly, but we have no intention of changing the
> code this much.[/color]

Since you are not using DAO (as far as I can tell), you should try
adjusting those settings under the registry key mentioned in the MSDN link
I posted. This is an alternative to using DBEngine.SetOption in DAO. You
may need to look up details on the registry value types that have to be
created - they do not exist by default.

--
remove a 9 to reply by email

re: INSERT INTO appears to be asynchronous?

I tried setting them all in the registry too, both under Jet 4.0 and
Jet 3.5 (I also tried the PageTimeout setting).
None of them seem to have any impact - unless I have to reboot or
something.
They *did* exist by default though, interestingly enough.

re: INSERT INTO appears to be asynchronous?

On Apr 29 2005, 01:01 am, wizofaus@hotmail.com wrote in
news:1114748398.718290.278300@o13g2000cwo.googlegr oups.com:
[color=blue]
> I tried setting them all in the registry too, both under Jet 4.0 and
> Jet 3.5 (I also tried the PageTimeout setting).
> None of them seem to have any impact - unless I have to reboot or
> something.
> They *did* exist by default though, interestingly enough.[/color]

You did this on the machine where your app is running, not on the one where
the MDB is located, right? Also, you have to use the general Jet registry
branch under
HKML\SOFTWARE\Microsoft\Jet\...
not the Access-specific one under
HKLM\SOFTWARE\Microsoft\Office\...\Access\Jet\...

You may have to reboot after making these changes - it's definitely worth a
try.

I'm not sure what else to suggest - since you are using ODBC, your options
are somewhat limited. With DAO, you could do DBEngine.Idle dbForceOSFlush
to make sure that Windows cache is flushed to the MDB file.

--
remove a 9 to reply by email