generating ID automatically

[lakshya]

generate ID

I have one problem. I have to generate ID automatically in my software which contains a combination of text and numbers. The main thing is that in access database, I hav given its datatype text because its alphanumeric but tell me can a text field be autoincremented to generate ID. If yes then please give me one example for generation.

 

[jmcilhinney]

Does this field have to have a specific format? If not then generate a GUID, using the Guid.NewGuid method, in VB and use that as the key.

 

[lakshya]

Format

Yes the field has a specific format. It's an Enquiry ID generation. ID has to be generated as ENQ+Course code for which enquiry is done(string)+Automated number.
example: ENQCCLW0001 or ENQCISS0002
I have been able to get the ENQ + Course Code. but how to generate number?

 

[DavidT_macktool]

You will not be able to do it automatically.
You need to look into writing your own sub to assign the ID.

Typical process would go like:
Get the last record of the dataset using a 'like' clause (where the like is the ENQ + Course Code).
Get the last 4 characters of the last ID used. Would be "0002" in your example.
Convert to an integer.
Add 1 to the integer.
Convert back to string.
Add the correct number of leading zeros and use that in your new ID string.


Create a Sub that performs these steps and returns your next "incremented" ID.


Hope that helps...

 

[kulrom]

In access you cannot make autoincremental field unless if the datatype is autonumber datatype.
But you can always do that from your frontend ... just find the last record and get the value of that field ..then increase that for one

Regards

 

[jmcilhinney]

I would recommend not using some function that bases its calculation on the last record in the table. I would recommend using a KeyManager table, that stores the next sequence number to be used. That way if you delete a record you will not reuse that sequence number. Just create a table that has two columns: TableName and NextSequenceNumber. You perform a query to get the next sequence number for the table you're inserting the row to. When you successfully insert a row you increment the NextSequenceNumber value and update the KeyManager table.