Incrementing record number in multi-User db

[paulette33]

Hi -

I have an Access 2002 database used to track changes. I have a form to add a record to that table. That form updates the data using an ADO connection. Currently, that table is structure with the change id as the primary key. The number is basically a number that is incremented, i.e. it goes to the database gets the last number and then adds 1 to it. That number is then displayed on the form for a new record. After all the data has been entered that number and the rest of the data is written to the table.

Here is my concern: Since the db is a multi-user db, I can see a scenario where someone is adding a new record at the same time that someone else is. Since the new ID number is not written to the table until they click save, both users will have the same id. Any thoughts on how to get around that? I have done some searches and looked through the FAQs. But, I'm just not sure what the best approach would be.

Thanks for your help!

 

[MichaelRed]

faq700-184


MichaelRed

 

[willir]

Also, look at using random instead of sequential numbers, and the replication ID. These options are available in the "field" area after you select autonumber for the field.

 

[paulette33]

Thanks MichaelRed & Willir! Just another question, I've read that FAQ, but I'm just wondering if that would work if I am generating that number up front, i.e. when a new form loads I go and get the next id. The reason I was grabbing the number in the beginning is to display the Record # on the form. But, would it be better to not display the record # and assign a number after the save? If I do that, then would that eliminate the possiblity of duplicating the numbers? I will also take a look at the replication id. I've never looked/used that before.

Thanks again!

 

[willir]

paulette33

The "record number" for which way and what??

Consider the following situations...

You use an ORDER BY clause in the SELECT statement by a transaction date. Fine your records are displayed sequentially by date, oldest record first. Record 1 = oldest record, Record 2 = 2nd oldest....

But now you realize that it makes sense to display the newest record first, so use again use the ORDER BY but with the DESC option. Great, records are displayed sequential by date, but in reverse order. Now Record 1 = newest record, 2 = 2nd newest...

Next, you have 1000's of records in a contact database, and you realize you can get rid of some contact information. You never contact them, they dont work for the same compnay, etc. So you delete records 5, 15, 99. Does this mean we now have to go through and re-number the records??

The record number is only relevant to the order the records are displayed.

Using a sequential number to identify records, and to always keep the numbers sequenced with no gaps is a lot of work with no real meaning.

The purpose of identifying a record by "a" number is to ensure the record can be uniquely identified.

Consider rule #7 in the following link ... The Ten Commandments of Access

I will often hide the ID number from users.

There are two situations where the primary key is important to the end user.
- Use of text string codes such as vender codes - this simplifies entry of records and allows one to know the parent record in a one-to-many when looking at the child records. Example: Vender is "Neat Stuff Inc" you identify their invoices with the vender code NSTFF.
- Control numbers such as cheque number, purhcase order number or invoice number where the number sequence is used for "control". Here, any missing numbers have to be explained for autdit purposes. And this requires a special approach to generating a sequential number in a multiuser environment requries coding and a table to stroe the control number(s). Using autonumber for this type of situation is definitely not advised.

Richard

 

[paulette33]

The purpose of the record # is so the user has the ability to search for that particular entry to either view or update the record. All other IDs are hidden from the user. The form only displays one record at a time. They do not have an ability to scroll through all of the records. I am essentially using the form for data entry and display purposes.

Based on the info provided, autonumber is definitely out of the question. But, if I provide the number to the user after the record has been saved to the table, it seems that might eliminate the duplicate key problem. Here's a little more detail to what I as thinking. Since I am updating the table using an ADO connection and writing the data to table, by using the .AddNew that should create the next record. I can trap the error if the number being saved is a duplicate and continue to increment up until the save is successful. Then after the save, return the record number back to the user. Does that seem like it would work?

 

[MichaelRed]

base solely on the ten second glance at your profile, you probably have little understanding of the code in the faq, hence your question(s). wheather you 'obtain' the record number before, during or after the record 'saving' os generally of little or no relevance. the convenience of the user should (usually) be the primary consideration. if they need to note the record number for some purpose, it is better to get (and therfore be able to display / record it) prior to saving the record, otherwise there is little need to displawy it, so no need to generate / obtain it until the record is saved. in any of these secnarious, the routine in the faq will always generate a unique value and contention issues are automatically handled within the routine.

unfortunatly, that is the end of the good news. the procedure(s) are intended as an example, not as a cut and paste exercise. in many / most cases, individuals wanting to generate custom Key values want to have some control over the 'content', such as encoding the date and or user id (name?) in the key, and the procedure(s) were aimed at that audience. It would take some effort to modify the procedures to reduce the return to the simple "Long" value of the incremmental counter and --again from the very brief glance at your profile and the question(s) i am not at all sure that you are sufficiently versed in the details of VB(A) code to do the modifications.





MichaelRed

 

[dRahme]

Hello, Here is a way I accomplish this. I have a db where I cannot use a primary key in the convention sense so I increment in a manner posted in your original question.

The user goes to a new record and gets a new number (DMax +1). That number is then appended to a 'used numbers' table and cannot be reused.

It does not matter that the record the user is on is saved. The number is saved and therefore cannot be duplicated by another user.
I have been running this db for 5-6 months now with 3-4 users and have not had a problem.


Private Sub cmdGetNewNumber_Click()
On Error GoTo Err_cmdGetNewNumber_Click

Dim NoUsed As Integer

'Get New Number
NoUsed = DMax("[noUsed]", "tblNumbersUsed") + 1

'Check to see if user is on a new record
If IsNull(Me.txtID) Then

'Use the number
Me.txtID = NoUsed

DoCmd.SetWarnings False

'Insert number into 'Used Numbers Table'

DoCmd.RunSQL "INSERT INTO tblNumbersUsed (noUsed) VALUES ('" & NoUsed & "');"

DoCmd.SetWarnings True

Else:
MsgBox "Please move to a new record.", vbInformation

End If



Exit_cmdGetNewNumber_Click:

Exit Sub

Err_cmdGetNewNumber_Click:
MsgBox Err.Description
Resume Exit_cmdGetNewNumber_Click

End Sub

 

[azaleatech]

If I may be allowed to rant for a moment, the please hear me out on this.

I'm a little troubled by all of the negative talk out there concerning the use of Replications IDs (a.k.a. globally unique identifiers or GUIDs in "common speak") as a unique key in Microsoft Access. Do a Google search on "Microsoft Access Replication ID" and you will quickly see what I’m talking about. This article is a good example: [URL unfurl="true"]http://msaccessadvisor.com/doc/10042[/url]. It’s my contention that the author of this article does not address the real value in using Replication IDs in Microsoft Access, and the real problem in not using them where long integer values are incapable.

I think this negative light is in part due to the fact that many people do not utilize globally unique identifiers or Replication ID values for their intended purpose. The keyword here is "globally unique." GUIDs created by the Microsoft Jet database engine in such a manner as to guarantee uniqueness across space and time. GUIDs are not unique to Jet or replication; many programs create and use GUIDs. For example, the Windows® operating system assigns a GUID to every ActiveX control installed on your system.

Many argue that Replication IDs are difficult to compare with the human eye, but that's not their intended purpose – uniqueness across space and time is their intended purpose. Another argument that gets made against Replication IDs is the need for additional storage space. Replication IDs generally require 16 bytes to store as opposed to a long integer value which requires 4 bytes. In a table with 10,000 records, using a Replication ID instead of a long integer value only amounts to a difference in database size of approximately 117 KB.

The premise of the argument here is that we want the database engine to do the work of generating and maintaining our unique keys and to not have to write additional programming language code to perform this function. Why reinvent the wheel when an external algorithm can only be less efficient having to do at least one query against the database to accomplish the same purpose? One important thing to consider is that the Auto-number (increment or random) long integer values generated by the Microsoft Jet database engine are only unique within the context of particular table in which they are used. With regard to use as a unique key, this is the single most important difference between the long integer and Replication ID types in Microsoft Access.

This important difference becomes apparent in situations where you are archiving deleted records from one table to another table (i.e. an archive table) and want to maintain a unique key across two different tables. The maintenance of these unique keys has to be done programmatically (outside of the database design) when using auto-number long integer values because once an auto-number long integer unique key value is deleted, it is possible for the database to reuse that value, possibly reusing a unique key that is in use in the archive table and in effect creating a conflict in our archiving scenario.

A practical example of this is a USERS table where a user record (a row in this case) is deleted, but we don't want to lose all the information about the user for security reasons. We want to save some of the user record into an archive table so that we can resolve references to this user even after the user record is deleted.

Thanks for entertaining my thoughts. Any feedback is welcome.

Sincerely,

Ben Roberts
Azalea Technology
broberts@azaleatech.com
[URL unfurl="true"]http://www.azaleatech.com[/url]

 

[earthandfire]

paulette33

The following code which I posted in thread700-1088348 should meet your requirements (and its easy to modify if you need).

Public Function GetNewNumber(TableName As String, FieldName As String, ACount As Long) As Long
Dim rs As Recordset
Dim n As Long

  On Error GoTo TableInUse
  Set rs = CurrentDb.OpenRecordset(TableName, , dbPessimistic)
  rs.Edit
  n = rs.Fields(FieldName)
  rs.Fields(FieldName) = n + ACount
  rs.Update
  rs.Close
  GetNewNumber = n + 1
  Exit Function

TableInUse:
  Resume
  
End Function

An explanation is in the thread referred to


Hope this helps.