?
Solved

Unique ID's in an access database using vb.net

Posted on 2007-10-05
8
Medium Priority
?
314 Views
Last Modified: 2013-11-26
Hi,

Like in MSSQL you can create new unique ID's using newID() is there a way to do this in an access database.

Many Thanks
Lee
0
Comment
Question by:ljhodgett
8 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 1000 total points
ID: 20022637
You can define a field as Autonumber in the tables design.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20022686
in vba, you can use

currentdb.Execute "Alter table tableName add column newID counter"
0
 
LVL 61

Expert Comment

by:mbizup
ID: 20022692
Clarifying... If you define the field as Autonumber inside Access, any new records created through your VB.Net interface will automatically be assigned a unique number.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:ljhodgett
ID: 20023058
With Autonumber isn't this an incrementing number for the table and not for the database as in MSSQL.

Best Regards
Lee
0
 
LVL 61

Expert Comment

by:mbizup
ID: 20023099
Yes.  My understanding of the newID() function is that it will return a totally unique alpha-numeric "number", and it will never be repeated within that database (or on that computer?).    AFAIK, Access has nothing like that.  The closest is the Autonumber, which  is unique for that table.
0
 
LVL 14

Assisted Solution

by:ragoran
ragoran earned 1000 total points
ID: 20024157
You could simulate newID() behavior with a dedicated table that contains just an autonumber field.

When you need a newID:
1- Append a row in the table
2- Query max(IDcolumn) to fetch the number inserted
3- Delete all records where IDColumn < fetched number (no need to keep more records than the last one)

If you run in a multiuser environment, encapsulate this logic in a transaction.


0
 
LVL 61

Expert Comment

by:mbizup
ID: 20024442
@ ragoran:
That's a great workaround!
0
 
LVL 14

Expert Comment

by:ragoran
ID: 20024512
@mbizup:

thanks for the good word
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question