Bad idea to use autonumbering IDs to track snail mail?

[southen]

While I consider myself a techie, I am absolutely clueless when it comes to Access, so please excuse any dumb questions.

I have a database at my department at work that everyone there has used well before I took the job. The department has always used the autonumbering on each row as a tracking ID for incoming snail mail; enter the mail into the tracker, and put that (sequential autogenerated) tracking autonumbering ID on the piece of snail mail. However, I've read on some forums I believe that you should never use that autonumbering for anything critical (like tracking documents!), because the file index could become damaged or corrupt, and in a case such as ours, all of a sudden we'd lose your entire tracking system.

Is there some other way of doing this, of creating autogenerated numbers each time you create a new line entry in the Access DB? Or are my concerns completely unfounded or nothing very likely at all?

Thanks a ton!

 

[Solo712]

While I consider myself a techie, I am absolutely clueless when it comes to Access, so please excuse any dumb questions.

I have a database at my department at work that everyone there has used well before I took the job. The department has always used the autonumbering on each row as a tracking ID for incoming snail mail; enter the mail into the tracker, and put that (sequential autogenerated) tracking autonumbering ID on the piece of snail mail. However, I've read on some forums I believe that you should never use that autonumbering for anything critical (like tracking documents!), because the file index could become damaged or corrupt, and in a case such as ours, all of a sudden we'd lose your entire tracking system.

Is there some other way of doing this, of creating autogenerated numbers each time you create a new line entry in the Access DB? Or are my concerns completely unfounded or nothing very likely at all?

Thanks a ton!

Hi southen,
there have been all sorts of problems reported with autonumbering. Some relate to compacting the database. So, yes, autonumbering should probably be avoided for stuff that is critical. You can create a safer sequential record number by something like:

NewSeqNo = Nz(DMax("SeqNo", "MyTable"),0) + 1

Since I work a lot in environments that are filtered and need sequence numbers for multiple db segments, I prefer the following which I believe is the safest way of doing sequences :

Private Function NewSeqNo() as Long
Dim dbs as Database
Dim rst as Recordset
DIM SQLstr as String
 
Set dbs = CurrentDb
SQLstr = SELECT * from MyTable WHERE MyFilter = " & ThisFilter & " ORDER BY SeqNo"
Set rst = dbs.OpenRecordSet(SQLstr, dbOpenSnapShot) 
 
NewSeqNo = 1
 
If not rst.EOF
   rst.MoveLast
   NewSeqNo = rst!SeqNo + 1 
End if
 
rst.Close
dbs.Close
set rst = Nothing
set dbs = Nothing
 
End Function

One of the immediate advantages of doing sequence numbers thios way is that when you delete a table, or the last batch of added records, the numbers arr reusable immediately and you do not run into astronomic autociphers during testing. Hope, this has been useful.

Best,
Jiri

 

[southen]

Thanks a ton for the responses! I like the idea of autonumbering, but the practicality of regular backups also makes sense.

If you told me to stick that autonumbering substitute code in my PHP script, no problem. But I have absolutely no idea where in the Access database I put it whatsoever. As a followup question, the office has it so that you can click on the autonumber and it brings up a pop-up like thing where you can enter more information. If the autonumbering gets corrupted, does the ability to do that also go away? Does that make sense?

Thank you both SO much again, the responses so far are great.