@ locking records over a network

  • Thread starter Thread starter 111
  • Start date Start date
1

111

I'd like the admin to go into certain records and lock the data so it is only
viewable and not editable by the other users on the network. The only way this
makes sense to me is to lock the data at the table level since each user has
his on application (forms) he's working from. But I can't see in the table set
up any features that would allow me to lock the textbox data?

Any suggestions?
Tks
Steve
 
The proper approach to using ms-access is to split your application into a
what is called a front end (the forms/code/reports) and the back end (data
only).

So, you might want to get your setup correctly. Further, you generally
SHOULD distribute a mde to each users pc. A mde is pre-compiled version of
your application, and the design/forms etc cannot thus be changed by your
users. You can then continue to develop updates and modify your development
front end (the mdb) while users are suing the existing mde file.

Once you get a correct setup for multi user, then the next step is to setup
security. The security (workgroup) file can be shard on the server just like
the back end tables. Once that is done, then you can assign which users can
update, or just view particular tables. So, you kind want to setup two steps
here.

Also, one should build a nice interface and "hide" the ms-access stuff so
users can't mess around with things they don't need to see.

If NONE of your users are supposed to be able to edit that table, then you
can avoid using security, and distribute a mde to those users that are NOT
supposed to update the tables, and distribute another mde to those users
that ARE allowed to update the tables. However, from a management point of
view, the time invested to use ms-access security is probably the best
solution here.
 
Dear Albert, thanks for the advise
I do have a front/back end application set up. (though i don't have the mde.
used yet)
What I'm trying to do is this.
On one form i have buy rates and sell rates listed . These may change from time
to time but at some point i need them 'locked' so that the user can only view
them and not change them. I should also state that only certain records need to
be locked ie: a customer may have 10 shipment currently in progress (we're in
the shipping business). During the transit of the shipment the buy and sell
rates for that particular shipment may change. Once the shipment has been
delivered i need to LOCK that particular record (or shipment) so that noone can
change the buy / sell fields. These are over a network and as i said each user
has his own front end application on his computer.I need (as the admin) to be
able to lock certain records when required.

any other suggestions?
Tks
Steve
 
Hi Steve,

If you want to prevent two operators from simultaneously opening the same
record for editing, in other words, you want to lock individual rows within
a recordset while they are being changed, the sample in the article 252317
does the exact thing you are after. You can transfer the VB code to VBA and
get the same effects.

When you transfer the code, if you use Windows NT authentication to login
(use the account logging into Windows NT domain to login into SQL Server),
use the connection string like below;

strConnect = "Provider='sqloledb'; Data Source=win2k-sqlserver;" & _
"Initial Catalog='Pubs';Integrated Security='SSPI';"

In the above connection string, what you need to modify is the Data Source.

If you use SQL Server authentication to login in, use the connection string
like below;

strConnect = "Provider='sqloledb'; Data Source=win2k-sqlserver;" & _
"Initial Catalog='Pubs';User ID=sa;Password=12345;"

In above connection string, you need to modify the Data Source, User ID,
and Password.

Note: win2k-sqlserver is the name of the SQL Server, Pubs is the database
name in the SQL Server.

252317 SAMPLE: Rowlock.exe Demonstrates Row-Level Locking Using ADO & SQL
Server
http://support.microsoft.com/?id=252317


Please feel free to reply to the threads if you have any concerns or
questions.



Sincerely,

Alick Ye, MCSD
Product Support Services
Microsoft Corporation
Get Secure! - <www.microsoft.com/security>

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
| Newsgroups: microsoft.public.access.forms
| Subject: @ locking records over a network
| From: (e-mail address removed) (111)
|
| I'd like the admin to go into certain records and lock the data so it is
only
| viewable and not editable by the other users on the network. The only way
this
| makes sense to me is to lock the data at the table level since each user
has
| his on application (forms) he's working from. But I can't see in the
table set
| up any features that would allow me to lock the textbox data?
|
| Any suggestions?
| Tks
| Steve
|
|
 
111 said:
Dear Albert, thanks for the advise
I do have a front/back end application set up. (though i don't have the mde.
used yet)
Click to expand...

Ok, the above is ok, but you should make the goal of each user getting a
mde.

On one form I have buy rates and sell rates listed . These may change from time
to time but at some point i need them 'locked' so that the user can only view
them and not change them.
Click to expand...

Ok, the above is fine, but you don't say who, how, when the record gets
locked? This is certainly NOTHING to do with a network issue. Simply put,
you need some option on the form (button, or whatever) that sets if the
record is locked. You can even add a few fields like lock date, or even a
true/false field if the record is locked. So, the solution is not network
locking, but YOU THE developer simply come up with a means for the operator
to freeze the record. This process is going to be part of your business
rules and how the application runs.

Thus, you can check on the forms on-load if editing of the record will be
allowed (you can set the allow edits to true or false). Once again, it is
your code/logic that will dot the record locking. In other words, you
obviously need someone with authority to "lock" a record. Once locked, then
other users will not be able to modify the record. This "fact" that the
record is locked is going to be a simple field in the database that you
check when the form loads. If you do for some reason actually load forms
with more then one record (poor design), then you also have to use the
on-current event to also check if the record can be modified.

Since the users must edit the data through that form, the simply addition of
a field that states if the record can be edited can suffice. Of course, you
might add a few fields as to who, and "when" the record was frozen. In fact,
you could even put a date range for this freeeze if you wanted.

The rest of this is of course setting up who is allowed to lock a record.
For this authority, you probably should start using ms-access security.
 
Thanks again Albert.
I'm glad to here this is not a big deal. But I"m still confused where to start.
I have limited 'code' experience.
If the backend (raw data tables) are on the network and each user has a front
end application on their computer, how do I lock a particular record so that
any of the users can't change the data in certain fields (on their respective
front end applications?)

TKs
Steve
 
The easy way as mentioned would be to add a field to the table. This field
could be called:

UserLock

The default value for the above field would of course be false.

Then, when the forms loads, you could check the value of the field like:

if me.UserLock = True then
me.AllowEdits = false
else
me.AllowEdits = true
endif

The above code would also have to be put into the forms on-current event if
you allow record navigation.

If you complete new to coding, then you do have some learning. However, the
above would work. I would just for now do some testing with the actual
"check box" placed on the form. In the checkbox after update event, you
would also have to have the above code (that is 3 places now!).

During the "test" phase, anyone would be able to check/un-check that box.
However, once you get the code working, then the next step would be to
restrict who can disable edits/changes to the record.
 
Hi Steve,

The sample in the article 252317 does the exact thing you are after. You
don't need to change the main code, however, you need to create the
interface Form in Access and change the connection string accordingly.

252317 SAMPLE: Rowlock.exe Demonstrates Row-Level Locking Using ADO & SQL
Server
http://support.microsoft.com/?id=252317

Please feel free to reply to the threads if you have any concerns or
questions.



Sincerely,

Alick Ye, MCSD
Product Support Services
Microsoft Corporation
Get Secure! - <www.microsoft.com/security>

This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------
| Newsgroups: microsoft.public.access.forms
| Subject: Re: @ locking records over a network
| X-Complaints-To: (e-mail address removed)
| X-Trace: news01.bloor.is.net.cable.rogers.com 1069904303 24.42.101.23
(Wed, 26 Nov 2003 22:38:23 EST)
| NNTP-Posting-Date: Wed, 26 Nov 2003 22:38:23 EST
| Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGXA06.phx.gbl!TK2MSFTNGXA05.phx.gbl!TK2MSFTNGP0
8.phx.gbl!news-out.cwix.com!newsfeed.cwix.com!cyclone01.bloor.is.net.cable.r
ogers.com!news01.bloor.is.net.cable.rogers.com.POSTED!not-for-mail
| Xref: cpmsftngxa06.phx.gbl microsoft.public.access.forms:245632
| X-Tomcat-NG: microsoft.public.access.forms
|
| Thanks again Albert.
| I'm glad to here this is not a big deal. But I"m still confused where to
start.
| I have limited 'code' experience.
| If the backend (raw data tables) are on the network and each user has a
front
| end application on their computer, how do I lock a particular record so
that
| any of the users can't change the data in certain fields (on their
respective
| front end applications?)
|
| TKs
| Steve
|
|
| In article <w4qwb.483406$6C4.64239@pd7tw1no>,
(e-mail address removed)
| says...
| >
| >| >> Dear Albert, thanks for the advise
| >> I do have a front/back end application set up. (though i don't have the
| >mde.
| >> used yet)
| >
| >Ok, the above is ok, but you should make the goal of each user getting a
| >mde.
| >
| >
| >> On one form I have buy rates and sell rates listed . These may change
from
| >time
| >> to time but at some point i need them 'locked' so that the user can
only
| >view
| >> them and not change them.
| >
| >Ok, the above is fine, but you don't say who, how, when the record gets
| >locked? This is certainly NOTHING to do with a network issue. Simply put,
| >you need some option on the form (button, or whatever) that sets if the
| >record is locked. You can even add a few fields like lock date, or even a
| >true/false field if the record is locked. So, the solution is not network
| >locking, but YOU THE developer simply come up with a means for the
operator
| >to freeze the record. This process is going to be part of your business
| >rules and how the application runs.
| >
| >Thus, you can check on the forms on-load if editing of the record will be
| >allowed (you can set the allow edits to true or false). Once again, it is
| >your code/logic that will dot the record locking. In other words, you
| >obviously need someone with authority to "lock" a record. Once locked,
then
| >other users will not be able to modify the record. This "fact" that the
| >record is locked is going to be a simple field in the database that you
| >check when the form loads. If you do for some reason actually load forms
| >with more then one record (poor design), then you also have to use the
| >on-current event to also check if the record can be modified.
| >
| >Since the users must edit the data through that form, the simply
addition of
| >a field that states if the record can be edited can suffice. Of course,
you
| >might add a few fields as to who, and "when" the record was frozen. In
fact,
| >you could even put a date range for this freeeze if you wanted.
| >
| >The rest of this is of course setting up who is allowed to lock a record.
| >For this authority, you probably should start using ms-access security.
| >
| >
| >--
| >Albert D. Kallal (MVP)
| >Edmonton, Alberta Canada
| >[email protected]
| >http://www.attcanada.net/~kallal.msn
| >
| >
| >
|
|
 
Back
Top