You are here:

Using MS Access/User permission level and auto numbering

Advertisement


Question
QUESTION: Hi Scott, I have two unrelated questions, I was wondering if you can help..
1) I created an Access data base file that resides on a shared drive and the tables being edited by multiple users. Is it possible to create passwords so each user can access only certain tables?
2) Completely unrelated to the above: I created and then deleted some records from a table. (Example, deleted fields 18, 19, 20). The auto number of the primary key field, counted these records and continue numbering like these exist (example, instead of letting me recreate records 18, 19, 20 it jumps to record 21). Can I set the auto numbering so that is will not keep these empty records? Thanks and regrds, Nir.

ANSWER: If you check out my blog (see address below), I address both questions. One blog is on using Login Security with VBA which addresses question 1. The other on sequential numbering though that only partially addresses question 2.

The answer to question 2 is no. You cannot set Autonumbering like that. In fact. using the Autonumber datatype is not suitable for sequential numbering at all. As soon as a number is generated it is used up even if you don't save the record. What the blog does not address is back filling numbers that have been deleted.

However, I question the need for that. First, its not a common practice to delete records, especially when sequential numbering is needed. Instead records are marked as inactive so the sequence is maintained. If you want to explain why you need to back fill I may be able to offer alternatives or suggest a way to do it.

On a separate note, your first question bothered me because it indicated you may not be using best practices. You referred to "a" database file on a share and that "tables" are being edited and you ask about allowing users to "access only certain tables". Well users should NEVER access tables. Any interaction between the user and a table should be done with forms. Allowing users direct access to tables is dangerous. In addition, restricting access to the data in certain tables needs to be done using forms as outlined in my blog.

Also, EVERY multiple user Access app NEEDS to be split between a back end (the tables) and a front end (everything else). Only the back end should reside on the network share. The front ends should be local to the user and contain links to the back end. using a single Access database file invites corruption and other problems.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Thx Scott. The reason I need to delete records is perhaps due to wrong utilization...pls advice. Currently, the way I run my mini production is by letting Access and its auto number PK to detrmine my parts serial numbers. This means that when parts are being scrapped I have holes in my table because there are records that had been removed. Should I simply disregard these or shoukd I set my own serial numbers and let access run the auto number as  aPK? The latter means that in each record I will have two fields with non repeating numbers...right? Does it mean that the new serial number field must ve filled up for every record? Thx.

ANSWER: Is there a reason why serial numbers need to be exactly sequential? Shouldn't you know when parts are scrapped to keep records of that?

These are not Access questions, but basic questions you need to answer about your manufacturing process. If you need to record each product made and what happened to it, then you might need to keep each record and note scrapped parts. On the other hand, you may need only apply serial numbers to finalized parts and not record scrap individually.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

P.S. thanks for the donation

---------- FOLLOW-UP ----------

QUESTION: Hi Scott, I have a question related to SELECT Query. I have parent and child tables with one to many relation.
I am trying to find all the values of the foreign key (in the parent table)that are NOT shared by both...how do i do this?
For example, one table includes records of computers and the other table includes serial numbers SN of processors that suppose to go into the computers. I would like to find the SN of the processors which have not yet been put into the computers....that are still in the warehouse.

Answer
The Query wizard will walk you through creating a unmatched query which will show all records in one table without a matching record in another table.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

P.S. Allexperts limits the number of followup questions you can ask. So if you have another question its best to start a new question.

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.