Locked Data? (2000)

Topic

New Reply

I work in an office where 3 workers are adding & deleteing data into an Access Database. Sometimes we screw up & delete info by mistake. Is there a way to lock data entered in individual cells within a table, so that this certain info can’t be changed?

Thanks in advance,

mark4man

Reply | Quote

Replies

Not easily. If you have 3 people changing the same records, the last one who saves wins. Access 2000 and later allow recordlocking, but that means that two people can’t edit the same record at the same time. When you talk about “deleting data”, what do you mean? Is it information within a field that is deleted, or are you talking about deleting entire records? If the latter, the easiest way to handle it is to add a Deleted field to each data table and use code behind forms to intercept the delete key and to set the Deleted field to true instead. However, if these people are changing and deleting data in the tables or queries, there is NO way to protect it.

Perhaps if you give more specific details about the problem, someone can come up with better suggestions.

Reply | Quote

Charlotte,

What’s getting deleted are prior entries, due to the fact that the info is being changed (by mistake) by a sebsequent user. It is information within a feild (individual cells.) Thanks.

mark4man

Reply | Quote

To avoid that sort of problem, you would have to prevent editing of the record once it has been added. It is possible to create forms that will do exactly that – let you view but not edit existing records, and also add new records. However if you editing at the table level, the only way to prevent that sort of thing is to implement security on the database and prevent editing from all userids but a special administrative account other then the Admin account. You could also consider moving the data to SQL Server which has more robust control over who can do what to records.

Reply | Quote

Everyone is talking about SQLserver. What is it? Do you have to buy it, or is it delivered with Access 2000? Is it diffucult to use?

Reply | Quote

SQL Server is a Microsoft application that runs on a network server that provides database functions, but does not provide a user interface. Access 2000 comes with a scaled down version of it called MSDE – if you create an ADP, it requires using either SQL Server of MSDE. Note that MSDE does not get installed by default when you install Access 2000. You can also buy various versions that range in price from a few hundred dollars for a developer (single user) version to several tens of thousands for the enterprise version that runs across multiple servers and provides automatic redundancy. The fundamenal difference is that SQL Server does all the work on the server, while with Access nearly all the work is done on the local workstation.

Reply | Quote

Here is a partial answer; others will no doubt have more complete information.

When you open an Access database, all the work is done by the processor on your PC. This holds even if you have split your database into frontend and backend Access databases.

SQL Server is the big brother of Access. As its name implies, it is a server system; the SQL Server software runs continually on a server PC and handles a lot of the data processing. Although SQL Server has its own interface (Enterprise Manager), users usually access SQL Server data by an Access or VB6 interface.

A (deliberately simplistic) example of the difference: Imagine a table with 100,000 records. You have a selection query that selects 500 records from this table. If the table is in an Access database (frontend or backend), the processor of your PC has to get the entire table and process it to return those 500 records. But if the table and query reside in a SQL Server database, the Access frontend sends a request to the server, the server machine executes the query and sends only the resulting 500 records to your PC. As noted, this is an overly simplistic view, but it gives you the basic idea.

SQL Server is more complicated, but much more powerful than Access. See here for more information.

Reply | Quote

If you are using a form to enter/edit data then you can use this in the current event of the form:

If Me.NewRecord = False Then
    Me.fldText1.Locked = True
    Me.fldText1.Enabled = False
    Me.fldText1.BackColor = vbGrey
Else
    Me.fldText1.Locked = False
    Me.fldText1.Enabled = True
    Me.fldText1.BackColor = vbWhite
End If

This will lock the field, fldText1 and turn it’s background to grey when the record moves to an existing field. If you start entering a NEW record, the field will unlock, change to white and allow your users to enter data in it.

Reply | Quote