Shared acces in MS Access mdb's

[Johan Barkhuizen]

Hi Guys

Does anyone know of a solution to establish and read data (something like
NOLOCK dirty read in SQL) from a MS Access DB with a VB app using it
without crashing the VB app?

It's a Access 2000 DB with VB.Net running. These seems to be no parameter
in the ODBC to not lock tables when accessing them, so while they're being
updated the VB app just crashes ...

Anyone have any ideas?

Regards
J

 

[Guest]

Hi Johan,

It's a Access 2000 DB...

While you may have created it using Access, it's more technically correct to
refer to it as a JET database. Access does not appear to be involved in your
scenerio.

Are you saying that you are getting page (or table) locking, instead of
row-level locking? If so, try implementing the code shown in this KB article:

PRB: Jet 4.0 Row-Level Locking Is Not Available with DAO 3.60
http://support.microsoft.com/?id=306435

Also, do you have any memo, OLE Object or Hyperlink data type fields in the
affected table(s)? If so, you might want to consider breaking these out to a
separate table, related 1:1. The reason I say this is found in the KB article
shown below:

"Also, record-level locking is not enabled for Memo data types."

Source: http://support.microsoft.com/kb/275561, under the title:
"Record-level locking".


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Brendan Reynolds]

You mention ODBC. If you're using the ODBC provider to connect to a JET
database from a .NET app, you might want to try using the OLEDB Provider for
JET instead. Other than that, I would suggest asking the question in the
ADO.NET newsgroup, microsoft.public.dotnet.framework.adonet

 

[Johan Barkhuizen]

Hi Tom,

Thanks for the prompt reply. We have few users accessing this .mdb
accross the network from Excel and Access using ODBC. The default "Get
data" functions from i.e. Excel (its just practical for users to use
Office to access this data) locks the tables and causes the VB .Net app to
crash/have 'Open State' errors. You're right that the error stems from a
Access and ODBC missing feature. I get the feeling this will not happen
in a SQL server - Excel ODBC scenario.

It still seems impossible to import data into Excel while it's in use by
other App. Have you seen a solution for this somewhere by chance?

Regards
Johan

 

[Johan Barkhuizen]

Hi Brendan

Thanks for the reply. I've seen many posts re. errors with
multi-threading using .mdb dbs. While is OK on a programming level -
accessing it with other copies of Access and the default ODBC just doesnt
seem possible.

 

[Jamie Collins]

Thanks for the prompt reply. We have few users accessing this .mdb
accross the network from Excel and Access using ODBC. The default "Get
data" functions from i.e. Excel (its just practical for users to use
Office to access this data) locks the tables and causes the VB .Net app to
crash/have 'Open State' errors.

How are your Excel users connecting to the mdb e.g. via a User DSN:
Control Panel, Administrative Tools, Data Sources (ODBC), Add, choose
'Microsoft Access Driver', Finish, Advanced, select 'Read Only' from
the list and change the vale to 1.

Jamie.

--

 

[Johan Barkhuizen]

Hi Jamie,

Yes indeed that was the 1st thing I tried - it still locks the table when
reading and crashes the VB app. One needs a "dirty read" option like
nolock in SQL server i.e. "select * from table (nolock) with ...."

This is not supported by ODBC. So how do you share access?

Regards
Johan

 

[Jamie Collins]

One needs a "dirty read" option like
nolock in SQL server i.e. "select * from table (nolock) with ...."

This is not supported by ODBC. So how do you share access?

Are you entrusting Excel users to set up their own read-only
connections to Access? You can use an OLE DB connection with MSQuery in
Excel but I only know how to make an OLEDB connection programmatically.
See:

http://msdn.microsoft.com/library/d.../vbaxl11/html/xlproConnection1_HV05200029.asp

For an OLE DB connection, you can use Mode=1 in the connection string.
See:
ADO Provider Properties and Settings

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/adoproperties.asp

Jamie.

--

 

[Johan Barkhuizen]

Hi Jamie

Thanks for the reply

All I can find is the mode you set up locking, Mode 1 = Row-level locking"
Mode 2 = Table level The VB app works very hard and updates constantly.
So it will crash if you lock anything while accing the mdb. I still
cannot find a "dirty read" option like Nolock.

How one can NOT lock the DB while reading is above me, no solution seem to
exist.