Trouble sequencing numbers in Access.

[qwert231]

I have an Access database with a table and 2 columns.

ybOrderNums webOrderNums
10000 20000

And I have a chunk of code that reads one of the numbers (depending on which type of job) increments the number, and replaces it with the new, incremented number.

However, I am getting some customers with duplicate numbers. Here is my code:

Dim strNewNumI, srcColumn, strNewNum As String
  Dim objTable As New DataTable("NewOrders")
  Dim objReader As OleDb.OleDbDataReader
  Dim objConn As New OleDbConnection(dbAccess)
  Dim cmdNewNum As New OleDbCommand("SELECT * FROM tblNewOrderNum", objConn)
  Select Case oType
   Case ordType.WebOrder
    srcColumn = "wOrders"
   Case ordType.YBOrder
    srcColumn = "yOrders"
  End Select
  objConn.Open()
  objReader = cmdNewNum.ExecuteReader
  Do While objReader.Read
   strNewNum = objReader(srcColumn)
  Loop
  objReader.Close()
  strNewNumI = strNewNum + 1
  cmdNewNum.CommandText = "UPDATE tblNewOrderNum SET " & srcColumn & " = '" & strNewNumI & "' "
  cmdNewNum.ExecuteNonQuery()
  objConn.Close()
  Return strNewNumI

I am not sure how to set this to pessimistic connections, so only one person can read the data at a time. Or should I do a stored procedure?

 

[link9]

If you need a uniqueID on each row, then use an AutoNumber field.

It's the ONLY way to guarantee uniqueness among your id's.

-paul

The answer to getting answered -- faq855-2992

 

[qwert231]

No, I am only going to have one row. That is the only row in the table.

 

[johnfrederick]

This is a bit off topic, but it looks like it is still open. It appears to me that you're adding records to an order table and assigning unique id numbers to them. As link9 suggested, the natural way to do that is to let Access manage an AutoNumber field. If you don't need to immediately know the AutoNumber, you could just add the records with an INSERT SQL command. If you need to know the new number, use DAO or ADO and after the .AddNew, but before the .Update method calls, you can read the new AutoNumber.

 

[jfrost10]

For some reason I thought I had posted to this topic...

anyway, qwert, tell me if I'm off here:

Whats happening is that you have a table with ONE row and TWO fields, and each field holds totals for the types of jobs being done. The problem is that if Paul and myself both access the site at the same time, and we both submit at the same time, there is a chance that we both get the same number (lets say 1000), and we both increment it by one (so the new value in teh db should be 1002), but when it gets saved to the database its still only 1001 because we over-wrote each other.

Am I right that thats what the issue is? Just clarification, since I'm not sure the unique id field is what you're looking for as a solution here.

thanks,

D'Arcy

 

[jfrost10]

qwert,

One solution is to write a stored query within your access application. All it would do is update the value by one when its called. That way your code isn't actually adding anything, its just firing a command to the db to increment the field.

D'Arcy