How can I retrieve unique no form a database 1

[rskomal]

How can I retrieve a unique no (say bill No) from an access database after saving a record/form loading. I am learning VB and I am interested in database programming.

Thanks

 

[zemp]

If you are looking at retrieveing a newly created autonumber field then look into the @@Identity property.

Otherwise post more details.

Thanks and Good Luck!

zemp

 

[harebrain]

The ADO approach would be to execute the Update method of the Recordset object, then re-read the autonumbered field. E.g.:

  Dim rsInvoice As Recordset
  Dim strSQL As String
  Dim blnIsNew As Boolean
  Dim intBillNo As Long

  Set rsInvoice = New Recordset
  strSQL = "SELECT * FROM Invoice WHERE ..."
  rsInvoice.Open strSQL, cnStr, , adLockOptimistic
  If blnIsNew Then rsInvoice.AddNew

  With rsInvoice
    .Fields(0) = ...
    .Fields(1) = ...
    .Update
    If blnIsNew Then intBillNo = .Fields("BillNo")
    .Close
  End With

HTH,
David

 

[VBOldTimer]

Actually, you can get the new number after calling AddNew and prior to calling the Update, just to throw in.
Make sure you use a server side cursor though.

It is possible to still have some (seldom) clashes though so a good practice to do a quick count query (fastest) on the table using the new number as criteria just to see if it is ok prior to calling the update:

rs.Open "Select Count(*) From sometable Where [ID] = " & NewNumber,,cmdtext

If rs.Fields(0).Value=0 then you should be good to go.

 

[johnwm]

Use zemp's solution if this app might become multi-user at any stage - it's the safest way to avoid clashes


________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[harebrain]

Unless I'm mistaken, @@identity is specific to SQL Server and not available to the MS Access programmer.

Thanks to VBOldTimer for his clarifying comments.

 

[johnwm]

harebrain
@@IDENTITY has been available from Access 2000 and JET4. There are some notes on MSDN, amongst them:

http://msdn.microsoft.com/library/d...cpconretrievingidentityorautonumbervalues.asp

about half way down the page


________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[SemperFiDownUnda]

VBOldTimer your method doesn't actually work with SQL server. The ID is not read unless you move off and back onto the record.
Ie if your IDENTITY field is called CustID
and you do
rs.AddNew
then look at rs.fields("CustID&quot.value it will be empty
even after you do
rs.Update
rs.fields("CustID&quot.value will still be empty
doing a rs.moveprevious and rs.movenext won't work either because you don't actuall step off the record and back on but rather step off the record then onto another. Sure your record is in there but just not where you left it 8)

Selecting numbers using MAX, as pointed out by the others, is also bad because in a multi user situation 2 people could get the same number.

 

[CCLINT]

This post, as is the question, is concerning a JET MDB.

You need to anyways use a server side cursor in most cases.

With ADO 2.1 and higher, using a server side cursor (and in some cases a Client side cursor for an ADO object written in code will work- but not with the ADO Data Control and a DataGrid), and a JET 4 mdb, (and Jet4 provider of course), the example shown by harebrain works just as accurate, or in-accurate, as would be with @@identity.

Both methods get the value of the identity column in the same manner, and it is stored in the same location - in a property the provider in the connection, which in this case the value is stored as a property by the local Jet Engine - which is residing of course on the [/b]client[/b].

It is the local JET Engine which handles this, well, even in a multi-user environment and using a server side cursor. Makes no difference which method is used.

This holds true for JET OLEDB and JET ODBC.

(Jet will handle this also well, in the same matter, when working with linked SQL Server tables, as it then uses a server side cursor.)

So, if it is possible to get clashes with the one method, it is also possible with the other and at the same risk.

The way that JET handles the Identity field value with a newly added record added via the recordset's AddNew method is, (surprise, surprise), by issuing, (in addition to an action insert query to insert the record), a SELECT @@Identity command after the insert, all wrapped in a transaction!

So, when you do this:

AddNew/UpdateBatch

JET does this:

INSERT INTO....
SELECT @@Identity

The same as you can do when running an action query.

You will however need to use @@Identity when working with action queries, such as inserting a record using INSERT INTO and then needing the newly inserted record's ID.


As far as the comment concerning using MAX:
the code above looks like that is not what is being done, but instead, seeing if the number actually exists after adding the record.

 

[CCLINT]

(My goodness! My english is getting worse and worse as the years go on - been out of an natural english speaking country a couple of decades now - these forums are about the only place where I "speak" english now-a-days.)

 

[harebrain]

Well, CCLINT, I gotta hang a star on that baby!

About @@identity availability in Access 2000 and later: idiot moi ! I looked for the information in the Access help file, to no avail; I should have known that the appropriate search location is ".NET Framework Developer's Guide" on MSDN. As far as Access and the box it came in go, @@identity is an undocumented feature, which makes it a... bug! (Was that a graceful exit? I don't think so... Anyway, thanks for the pointer.)

 

[SemperFiDownUnda]

Wooops I missed that ... but people saying Access could mean JET or SQL server. I wish people would say JET and SQL server 8)

Access is only a front end to a database engine. With Access 2000 this could be a JET engine OR a SQL engine. Depending on the engine you use will dictate what features you have.