By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,871 Members | 2,304 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,871 IT Pros & Developers. It's quick & easy.

INSERT INTO appears to be asynchronous?

P: n/a
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?
Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
On Apr 27 2005, 09:40 pm, wi******@hotmail.com (Dylan Nicholson) wrote in
news:7d**************************@posting.google.c om:
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?


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
Nov 13 '05 #2

P: n/a

Dimitri Furman wrote:
On Apr 27 2005, 09:40 pm, wi******@hotmail.com (Dylan Nicholson) wrote in news:7d**************************@posting.google.c om:
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?
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.

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.

Nov 13 '05 #3

P: n/a
On Apr 28 2005, 09:19 pm, wi******@hotmail.com wrote in
news:11**********************@l41g2000cwc.googlegr oups.com:

Dimitri Furman wrote:
On Apr 27 2005, 09:40 pm, wi******@hotmail.com (Dylan Nicholson) wrote
in news:7d**************************@posting.google.c om:
> 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?
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.

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).


Are you saying that you are somehow including those settings in the
connection string? That definitely doesn't work.
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.


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
Nov 13 '05 #4

P: n/a
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.

Nov 13 '05 #5

P: n/a
On Apr 29 2005, 01:01 am, wi******@hotmail.com wrote in
news:11**********************@o13g2000cwo.googlegr oups.com:
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.


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
Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.