ADO Locking Mode and MS Jet 4.0 Provider

ADO Locking Mode and MS Jet 4.0 Provider

Post by Ronald Dod » Sat, 31 Jan 2004 22:19:46


Can someone please help me out with this? I am using VBA within Access and
would like to use ADO if feasable, so as eventually, I can see about having
the data taken over to MS SQL Server, but have to build my DB program, get
it working, and I do have various requirements involved. One of these
requirements is dealing specifically that I must be able to have Record
Level Locking finctionality. I would hate to have to create a work around
solution just to get what I would like to do as one would think it would be
less efficient not to mention the various things I would have to take into
account with regards to concurrency issues.

What must I do to have record level locking mode within an Access DB file
via ADO programming using MS Jet 4.0 OLE DB provider?

I have already created work arounds dealing with:

Record numbers to allow for check digits

Data validation to allow for mouse users to click on certain form controls
such as a form reset command button without triggering the data validation
code, but yet, still allow for the stringent data validation checks to take
place should the user click or move onto something else that requires the
data to be checked before moving on. This custom centralized code was not
easy to program as there were a lot of things I had to take into account,
but it's working like a charm for me now. One may say to use the
BeforeUpdate Event, but that causes issues with mouse users as I indicated.
However, for my code to work, FORMS AND CONTROLS MUST BE UNBOUND, which in
turns creates the need for me to work with ADO and/or DAO coding.

I have found an issue with DAO coding that's inconsistent to how it's
documented dealing specifically with it's Edit Mode, which does not work as
it should. For this reason, I have turned to ADO coding and see if I can
get what I need and require, and at this point, I'm not finding the
solution, but I'm also currently chalking it as a learning experience and
have been for the last week digging into the OLE DB provider stuff and it's
workings.

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
 
 
 

ADO Locking Mode and MS Jet 4.0 Provider

Post by Paul Cleme » Sun, 01 Feb 2004 02:27:46


Can someone please help me out with this? I am using VBA within Access and
would like to use ADO if feasable, so as eventually, I can see about having
the data taken over to MS SQL Server, but have to build my DB program, get
it working, and I do have various requirements involved. One of these
requirements is dealing specifically that I must be able to have Record
Level Locking finctionality. I would hate to have to create a work around
solution just to get what I would like to do as one would think it would be
less efficient not to mention the various things I would have to take into
account with regards to concurrency issues.

What must I do to have record level locking mode within an Access DB file
via ADO programming using MS Jet 4.0 OLE DB provider?

I have already created work arounds dealing with:

Record numbers to allow for check digits

Data validation to allow for mouse users to click on certain form controls
such as a form reset command button without triggering the data validation
code, but yet, still allow for the stringent data validation checks to take
place should the user click or move onto something else that requires the
data to be checked before moving on. This custom centralized code was not
easy to program as there were a lot of things I had to take into account,
but it's working like a charm for me now. One may say to use the
BeforeUpdate Event, but that causes issues with mouse users as I indicated.
However, for my code to work, FORMS AND CONTROLS MUST BE UNBOUND, which in
turns creates the need for me to work with ADO and/or DAO coding.

I have found an issue with DAO coding that's inconsistent to how it's
documented dealing specifically with it's Edit Mode, which does not work as
it should. For this reason, I have turned to ADO coding and see if I can
get what I need and require, and at this point, I'm not finding the
solution, but I'm also currently chalking it as a learning experience and
have been for the last week digging into the OLE DB provider stuff and it's
workings.

DAO does not support row-level locking directly but there are some workarounds using ADO. See if the
following helps:

PRB: Jet 4.0 Row-Level Locking Is Not Available with DAO 3.60
http://www.yqcomputer.com/ ;en-us;306435&Product=acc


Paul ~~~ XXXX@XXXXX.COM
Microsoft MVP (Visual Basic)

 
 
 

ADO Locking Mode and MS Jet 4.0 Provider

Post by Ronald Dod » Sat, 07 Feb 2004 02:29:03

hank you for the reply as I didn't have much luck in either of the 2 Access
DAO/ADO programming newsgroups with this issue. The link you have provided
looks to be promising and I will have to give it a try. I will reply back
with my findings after I give it a shot and see how it works. Currently, as
I have found early on with ADO programming, OLE DB programming seems to be
quite integrated in with ADO programming, so I'm also continuing on with my
OLE DB programming language learning, so as I can get a reasonably good
handle on that too. Of course, given how I tend to learn, I'm having to
pick apart the materials, and then put it back together in a manner that I
best understand the materials without losing the context that the materials
are using.

I can certainly understand that DAO doesn't support record level locking
directly as it's programming language is primarily for Access 97 and prior,
(I'm assuming at this point on) and only put in Access 2000 and later for
backward compatibility reasons as I have learned that MS has no plans to
further enhance DAO. Record level locking wasn't introduced until Access
2000. As I go along with my DB programming, I'm finding more reasons as to
why to go with ADO coding and less to go with DAO coding, especially if my
DB will eventually go into a better DB program like SQL Server, which does
not support DAO coding.

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

"Paul Clement" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...
< XXXX@XXXXX.COM > wrote:
and >> would like to use ADO if feasable, so as eventually, I can see about
having> > the data taken over to MS SQL Server, but have to build my DB program,
ge>
> it working, and I do have various requirements involved. One of the>e
> requirements is dealing specifically that I must be able to have Rec>rd
> Level Locking finctionality. I would hate to have to create a work
ar>und
> solution just to get what I would like to do as one would think it woul>
be
> less efficient not to mention the various things I would have to take> into
> account with regards to concurrency >ssu>s.
> > What must I do to have record level locking mode within an Access>DB
file
> via ADO programming using MS Jet 4.0 OLE DB>pro>ider?
> > I have already created work arounds d>ali>g with:
> > Record numbers to allow fo> ch>ck digits
> > Data validation to allow for mouse users to click on certai> form
controls
> such as a form reset command button without triggering the>data
validation
> code, but yet, still allow for the stringent data validati>n checks to
take
> place should the user click or move onto something else>that requires
the
> data to be checked before moving on. This custom centr>lized code was
not
> easy to program as there were a lot of things I had t> take into
account,
> but it's working like a charm for me now. >ne may say to use the
> BeforeUpdate Event, but that causes issues with mouse>users as I
indicated.
> However, for my code to work, FORMS AND CONTROLS MU>T BE UNBOUND, which
in
> turns creates the need for me to work wi>h A>O and/or DAO coding.
> > I have found an issue with DAO coding that>s inconsistent to how it's
> documented dealing specifically with it's Edit M>de, which does not work
as
> it should. For this reason, I have turned to