locking of DB

[Katie]

VB6 + Access 2003

I have a app that can add record, modify and view report.
the app deploy in around 10 peoples PC. but i want only 1 user can enter the
add record form at the same time.

any locking of the form can be implemented?

My thinking way like this:
Whenever somebody click the added form, a value recorded down in either
registry or a text file; meanwhile it will check if any entry in the
registry or text file,
if blank
can in and do the add
else
msgbox (xxx uesr is using)

any other suggestion? thanks for all expert advise!!!

[Veign]

You want the form to lock?

You should look into the CursorType / LockType properties of ADO

LockType:
http://www.devguru.com/technologies/ado/8658.asp

CursorType:
http://www.devguru.com/technologies/ado/8651.asp

--
Chris Hanscom - Microsoft MVP (VB)
Veign's Resource Center
http://www.veign.com/vrc_main.asp
Veign's Blog
http://www.veign.com/blog
--

"Katie" <Katie@question> wrote in message
news:%23Iz2i19...@TK2MSFTNGP12.phx.gbl...

[Someone]

I am going to guess here.

Use adLockPessimistic, however, I am not sure if you can use that with
Access. adLockPessimistic is not supported when the CursorLocation property
is set to adUseClient. Read the entire Remarks section in LockType Property.
You can be sure what type of locking you are using by checking LockType
property AFTER you open the recordset. Example:

rs.Open sSQL, cn, adOpenKeyset, adLockPessimistic, adCmdText
Debug.Print rs.CursorType ' This may not be what you expect.
Debug.Print rs.LockType ' Nor this.

The provider is free to choose another LockType without generating an error.
This is a perfectly normal behavior! The same is true for CursorType
Property. This is provider specific.

If you get it to work, you would want to unload the form from Form_Load. You
can't use Me.Unload or "Unload Me", use the following indirect method
instead:

Private Const WM_CLOSE = &H10
Private Declare Function PostMessage Lib "user32" Alias "PostMessageA"
(ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam
As Long) As Long

Public Sub UnloadForm(ByVal hWnd As Long)
PostMessage hWnd, WM_CLOSE, 0&, 0&
End Sub

"Katie" <Katie@question> wrote in message
news:%23Iz2i19...@TK2MSFTNGP12.phx.gbl...

[Martin]

U can lock the table. And unlock when closing form.

Or make a table with a record with the formname and the user using it.
if user is blank u can set the username so all know its in use.

"Katie" <Katie@question> schreef in bericht
news:%23Iz2i19...@TK2MSFTNGP12.phx.gbl...

[Dave]

That method works as long as you provide a means of clearing the locks if
the program (or Windows) crashes while the lock is set.

What I sometimes do for apps using a Access database is have an instance
that needs exclusive access to a form create an empty file in the same
folder as the database and hold it open then if another instance tries to do
the same thing it sees the file and tries to delete it, if it fails it knows
another user is doing the same exclusive activity. This automatically clears
up any spurious "locks" which may be left about.

Best Regards
Dave O.

"Martin" <M...@community.nospam> wrote in message
news:eqGnRAC...@tk2msftngp13.phx.gbl...

[.]

yes i lock the DB with adLockPessimistic already. but seems still not enough

"Veign" <NOSPAM...@veign.com> wrote in message
news:euvBAd%23vFH...@tk2msftngp13.phx.gbl...

[Ralph]

"Katie" <Katie@question> wrote in message
news:%23Iz2i19...@TK2MSFTNGP12.phx.gbl...

I'm no expert, but I can advise you on one point.

No RDBMS has any inherent means of insuring concurrency. They do go to great
lengths at insuring data integrity and avoiding deadlocks. All data
integrity mechanisms (locks, cursors, etc) can help to provide a practical
illusion of concurrency, but are doomed to fail at some frequency of use.

In order to insure concurrency you have to create your own GateKeeper to
manage a queue, usually through a separate service (MTS, ActiveX Exe, ...).
In order to manage multiple requests for permission to entering the queue
you have to create a TicketMaster to dole out tokens.

In your case only one app at a time has a token to open. However, I highly
advise you not to go there if you don't have to, as anytime you add a
'human' to the process you also add another level of complexity. For
example, what happens when the app successfully requests a token, opens an
"Add Form", and the user then goes on a coffee break? You will have to add
an expiration date to your tokens.

You might want to 'rethink' what you are doing and why.

hth
-ralph

[Someone]

> yes i lock the DB with adLockPessimistic already. but seems still not
> enough

The DB may have refused to use adLockPessimistic without telling you. It may
have chosen to use adLockOptimistic without telling you. This is perfectly
fine in ADO.

As I said in my earlier email, check LockType property after opening the
recordset to see if the DB accepted it, or switched it to something else. No
errors are generated when the DB does not support the Lock or Cursor type
that you wanted, it's DB specific.

"." <a@a> wrote in message news:%23T08XSd...@TK2MSFTNGP11.phx.gbl...