Setting provider specific value - flushing MSAccess

I have searched far too long for an answer to this riddle...and I know
it's probably right in front of me.

I have found articles using older versions of ADO that set Jet specific
properties on the ADO connection object. In ADO.NET 2.0 the Connection
does not have a Properties collection where I can set a key/value
setting that is specific to the provider. Where is the equivalent in
ADO.NET 2?

Specifically I am trying what the articles call the "Jet OLEDB:Flush
Transaction Timeout" as a way to disable the asynchronous "lazy write"
that the Jet engine does by default.

Kudos in advance to anyone that knows the answer.

Dennis

Paul
Thu Jan 25 10:02:37 CST 2007

On 24 Jan 2007 18:19:13 -0800, DennisWelu@cartegraph.com wrote:

¤ I have searched far too long for an answer to this riddle...and I know
¤ it's probably right in front of me.
¤
¤ I have found articles using older versions of ADO that set Jet specific
¤ properties on the ADO connection object. In ADO.NET 2.0 the Connection
¤ does not have a Properties collection where I can set a key/value
¤ setting that is specific to the provider. Where is the equivalent in
¤ ADO.NET 2?
¤
¤ Specifically I am trying what the articles call the "Jet OLEDB:Flush
¤ Transaction Timeout" as a way to disable the asynchronous "lazy write"
¤ that the Jet engine does by default.
¤

There are no provider specific properties for the OLEDB namespace library under ADO.NET. Any
provider specific properties must be supplied through the connection string.


Paul
~~~~
Microsoft MVP (Visual Basic)

DennisWelu
Thu Jan 25 11:17:13 CST 2007


> There are no provider specific properties for the OLEDB namespace library under ADO.NET. Any
> provider specific properties must be supplied through the connection string.

Thanks for the info, that narrows the universe. However, based on:

http://support.microsoft.com/kb/318161

there no longer seems to be a way to set the "Jet OLEDB:Flush
Transaction Timeout" at all because it cannot be used in the connection
string, only once a connection has been established.

So, in ADO.NET, how would someone write a record to a Jet table, then
go back to retrieve an autonumber (either increment OR random) value in
that record? Even though the connection is reestablished for a 2nd
query, the "lazy write" still hasnt written the value to the table from
the first query. (This table has an external unique "ID" that the user
works with, and an internal OID number that is autonumbered. We query
back based on "ID" to determine the OID so that we can add related
records with FK back to OID).

Dennis

DennisWelu
Fri Jan 26 15:14:20 CST 2007

Another example of where this caching needs to be turned off is for
some integration tests - they populate the db with values and then the
test runs but it doesn't see the values in the db unless we do
Thread.Wait(3000) or something like that.

Help?

Paul
Mon Jan 29 09:12:46 CST 2007

On 25 Jan 2007 09:17:13 -0800, DennisWelu@cartegraph.com wrote:

¤
¤ > There are no provider specific properties for the OLEDB namespace library under ADO.NET. Any
¤ > provider specific properties must be supplied through the connection string.
¤
¤ Thanks for the info, that narrows the universe. However, based on:
¤
¤ http://support.microsoft.com/kb/318161
¤
¤ there no longer seems to be a way to set the "Jet OLEDB:Flush
¤ Transaction Timeout" at all because it cannot be used in the connection
¤ string, only once a connection has been established.
¤
¤ So, in ADO.NET, how would someone write a record to a Jet table, then
¤ go back to retrieve an autonumber (either increment OR random) value in
¤ that record? Even though the connection is reestablished for a 2nd
¤ query, the "lazy write" still hasnt written the value to the table from
¤ the first query. (This table has an external unique "ID" that the user
¤ works with, and an internal OID number that is autonumbered. We query
¤ back based on "ID" to determine the OID so that we can add related
¤ records with FK back to OID).

For Access autonumber values, you use SELECT @@IDENTITY.

http://msdn2.microsoft.com/en-us/library/ks9f57t0.aspx


Paul
~~~~
Microsoft MVP (Visual Basic)