[RESOLVED] Problem : Set the Seed of an AutoNumber using ADOX

[Jonney]

I have a problem when I am setting the Seed of an AutoNumber using ADOX. VB report errors when VB executing the codes if 2 or more VB applications are connecting to the table. But if I remove the line (bellow Bold),then it come to be multi-instances.

oCat.Tables("tblFile").Columns("ID").Properties("Seed") = NewID + 1

Dim oCnn As Object ' ADODB.Connection
Dim oRS As Object
Dim objJRO As Object

Dim oCat As Object 'New ADOX.Catalog
Dim NewID As Long  'this will contain the seed value.

Dim hFile As Long

' Insert the object into the database

    On Error GoTo vbErrorHandler

    If Not InitDB(oCnn, Me.DatabaseName, Me.UserName, Me.Password) Then Exit Function
    Set oRS = CreateObject("ADODB.Recordset") ' New ADODB.Recordset

    'reset the seed value of your AutoNumber field (the value of the new identity)
    Set oRS = oCnn.Execute("SELECT MAX(ID) FROM tblFile")
    NewID = oRS.Fields(0).Value  '0 specifies that we want the first field
    oRS.Close

    Set oCat = CreateObject("ADOX.Catalog") ' New ADOX.Catalog
    oCat.ActiveConnection = oCnn
    oCat.Tables("tblFile").Columns("ID").Properties("Seed") = NewID + 1
    oCat.Tables("tblFile").Columns.Refresh
    Set oCat = Nothing

[si_the_geek]

Thread moved from the 'CodeBank VB6' forum (which is for you to post working code examples, not questions) to the 'Database Development' forum

Is there a reason you are hiding the error message (which help explains the technical problem) from us?


As to the problems being multi-user based, that shows a massive flaw in what you are trying to do - because you are altering the table design, and that should generally not be done if there is a chance of other users being connected.

Why are you trying to re-seed the AutoNumber field anyway? That is something that should generally not matter at all, and even if it does it should usually be done manually. For more information, see the article Why don't AutoNumber/Identity/etc re-use deleted numbers? from our Database Development FAQs/Tutorials (at the top of this forum)

[Jonney]

Error report:

Run-time error '3704'
Operation is not allowed when the object is closed.

But I don't think the error is accurate.
OK,let me explain in details:
a. MS Access open the database file (Assets.mdb) and Open the table. (double click "tblFile")
b. Run my VB application
c. When codes executing into "oCat.Tables("tblFile").Columns("ID").Properties("Seed") = NewID + 1",VB reports error.
d. Close tblFile table window in Access
e. Run the code,there's no more error.
f. I try to run another copy of the same VB application
g. Run 2rd copy again,I got the same error.
h. quit one copy,run only one copy,then the code works and the Feild "ID" number is continuous and in sequence.(oCat.Tables("tblFile").Columns("ID").Properties("Seed") = NewID + 1)

Question: Does my code open connection exclusively? Not sure. But after I removed the line, that is OK.

Public Function InsertObject() As Boolean

Dim oCnn As Object ' ADODB.Connection
Dim oRS As Object
Dim objJRO As Object

Dim oCat As Object 'New ADOX.Catalog
Dim NewID As Long  'this will contain the seed value.

Dim hFile As Long

' Insert the object into the database

    On Error GoTo vbErrorHandler

    If Not InitDB(oCnn, Me.DatabaseName, Me.UserName, Me.Password) Then Exit Function
    Set oRS = CreateObject("ADODB.Recordset") ' New ADODB.Recordset

    'reset the seed value of your AutoNumber field (the value of the new identity)
    Set oRS = oCnn.Execute("SELECT MAX(ID) FROM tblFile")
    NewID = oRS.Fields(0).Value  '0 specifies that we want the first field
    oRS.Close

    Set oCat = CreateObject("ADOX.Catalog") ' New ADOX.Catalog
    oCat.ActiveConnection = oCnn
    oCat.Tables("tblFile").Columns("ID").Properties("Seed") = NewID + 1
    oCat.Tables("tblFile").Columns.Refresh
    Set oCat = Nothing
    With oRS
        .Open "select * from tblFile where id = 0", oCnn, adOpenKeyset, adLockOptimistic
    End With

    With oRS
        .AddNew
        .Fields("AssetsID").Value = Me.AssetsID

        ' Copy the file into the Recordset field#

        If Not (BuildRSFile(oRS)) Then
            oRS.CancelUpdate 'oRS.Cancel
            oRS.Close

            'reset the seed value of your AutoNumber field (the value of the new identity)
            Set oRS = oCnn.Execute("SELECT MAX(ID) FROM tblFile")
            NewID = oRS.Fields(0).Value  '0 specifies that we want the first field
            oRS.Close
            
             Set oCat = CreateObject("ADOX.Catalog") ' New ADOX.Catalog
             oCat.ActiveConnection = oCnn
             oCat.Tables("tblFile").Columns("ID").Properties("Seed") = NewID + 1
             oCat.Tables("tblFile").Columns.Refresh
            Set oCat = Nothing
            Set oCnn = Nothing
            InsertObject = False
            Exit Function
        End If

        .Fields("FileName").Value = Me.Filename

        ' Record the required date/times etc.

        .Fields("FileSize").Value = Me.FileSize
        .Fields("FileDateTime").Value = Me.FileDateTime
        .Fields("DateAdded").Value = Now()
        .Fields("AddedBy").Value = Me.AddedBy
        .Fields("Remarks").Value = Me.Remarks
        .Update
        .Close
    End With

    ' Refresh the DB Engine

    'Set objJRO = New JRO.JetEngine
    Set objJRO = CreateObject("JRO.JetEngine")
    objJRO.RefreshCache oCnn
    InsertObject = True

Exit Function

vbErrorHandler:
    oRS.Close
    Set oRS = Nothing
    Set oCnn = Nothing
    InsertObject = False
    CloseHandle hFile
    Err.Raise Err.Number, "SQLFileObject::InsertObject", Err.Description

End Function

[Jonney]

Why am I trying to re-seed the AutoNumber field? I want to automatically re-seed the AutoNumber Column after records are being deleted or CancelUpdate. In my above code,I re-seed twice.

[si_the_geek]

Error report:

Run-time error '3704'
Operation is not allowed when the object is closed.

But I don't think the error is accurate.

I have never seen that error be inaccurate - but at the moment I can't see why it is occurring.

OK,let me explain in details:
a. MS Access open the database file (Access.mdb) and Open the table. (double click "tblFile")
b.Run my VB application
c.When codes executing into "oCat.Tables("tblFile").Columns("ID").Properties("Seed") = NewID + 1",VB reports error.
d. Close tblFile table window in Access
e.Run the code,there's no more error.
f. I try to run another copy of the same VB application
g.Run 2rd copy again,I got the same error.

That is all entirely expected, and I explained why before (the error is forcing you to act properly).

Conclusion: Does my code open connection exclusively?

I have no idea, because you haven't shown how you open the connection.

If you are going to alter table design you will have to - but it is almost certainly a very bad idea to alter the table design via code.

Why am I trying to re-seed the AutoNumber field? I want to automatically re-seed the AutoNumber Column after records are being deleted or CancelUpdate. In my above code,I re-seed twice.

That does not answer "Why", only "When" (which is not what I asked).

If you haven't read the article I linked to yet, I strongly suggest you do it now.

[Jonney]

Quote:
Why am I trying to re-seed the AutoNumber field? I want to automatically re-seed the AutoNumber Column after records are being deleted or CancelUpdate. In my above code,I re-seed twice.
That does not answer "Why", only "When" (which is not what I asked).
Reason: My colleague request. Re-seed after records being deleted or oRS.CancelUpdate command.

I have no idea, because you haven't shown how you open the connection.
Answer:
oCnn.Open msconnect

[si_the_geek]

Reason: My colleague request. Re-seed after records being deleted or oRS.CancelUpdate command.

Then I suggest you get your colleague to read the article - because doing that is a really bad idea.

I have no idea, because you haven't shown how you open the connection.
Answer:
oCnn.Open msconnect

I could have guessed that much... but clearly I have no idea what msconnect contains, or what other things you have done with oCnn in addition to that line.

[Jonney]

Then I suggest you get your colleague to read the article - because doing that is a really bad idea.

I could have guessed that much... but clearly I have no idea what msconnect contains, or what other things you have done with oCnn in addition to that line.

Public Function InitDB(oCnn As Object, DatabaseName As String, _
                       Optional ByVal User As String = "admin", _
                       Optional ByVal Password As String = "", _
                       Optional ByVal JetVersion As eJetVersion = ejvJet4) As Boolean

Dim msconnect As String

    On Error GoTo ErrorHandler

    '-- Initialise module-level objects
    Set oCnn = CreateObject("ADODB.Connection") ' The same as New ADODB.Connection
                                                '- we use late binding to use any installed version of ADO

    Select Case JetVersion
    Case ejvJet3
        msconnect = "Driver={Microsoft Access Driver (*.mdb)};DBQ=" + _
                    Trim$(DatabaseName) + ";UID=" + _
                    Trim$(User) + ";PWD=;" + Trim$(Password)
    Case ejvJet4
        msconnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & Trim$(DatabaseName) & ";" & _
                    "Jet OLEDB:Database Password=;" & _
                    "Jet OLEDB:Engine Type=5;"
    End Select

    oCnn.Open msconnect
    InitDB = True

Exit Function

ErrorHandler:
    If Err.Number = 429 Then
      MsgBox "Could not initialize MS ActiveX Data Objects Library (MS ADO)"
   Else
      MsgBox Err.Source + " ERROR " + CStr(Err.Number) + " - " + Err.Description
   End If

End Function

[si_the_geek]

In that case the answer to your question ("Does my code open connection exclusively?") is No.

[Jonney]

In that case the answer to your question ("Does my code open connection exclusively?") is No.

oCat.Tables("tblFile").Columns("ID").Properties("Seed") = NewID + 1
That means we can't alert table/Feild's system properties if there're more than 2 connections?

My colleague want Feild "ID" number (AutoNumber Feild) is continuous and in sequence after records are being deleted.

[si_the_geek]

Correct... and as I said before that is the way it should be, and also you should almost certainly not be attempting to do it anyway.

[Jonney]

Correct... and as I said before that is the way it should be, and also you should almost certainly not be attempting to do it anyway.

Thanks.
It works only for standalone application.
Still Pending for more input and research.

[si_the_geek]

The most important research you can do is understand the article I linked to earlier - because re-seeding is a dangerous thing to do (even more so when it is automatic), and generally gives no worthwhile benefits.

[Jonney]

The most important research you can do is understand the article I linked to earlier - because re-seeding is a dangerous thing to do (even more so when it is automatic), and generally gives no worthwhile benefits.

Thanks. I keep in mind.

[Jonney]

I have tried to put set seed into another place (after a record being deleted,I re-seed the AutoNumber Field),I got the same problem.But the error is different:

Run-time error '-2147217856 (80040e40)

You tried to lock table " while opening it,but the table cannot be locked because it is currently in use,Waiting a moment,and then try the operation again.

[si_the_geek]

That is the kind of error I would have expected before, and there is no guaranteed way to work around it - the best you could do is pause for a while and re-try, but depending on the situation it might never finish (thus making the program unusable, or making the re-seeding fail).

As yet you haven't even hinted at an actual reason for attempting to do this, which I find rather worrying - you are doing something that is dangerous (and therefore frowned upon by people who know what they are doing with databases), presumably just for the sake of making numbers 'look nice'.

[Jonney]

That is the kind of error I would have expected before, and there is no guaranteed way to work around it - the best you could do is pause for a while and re-try, but depending on the situation it might never finish (thus making the program unusable, or making the re-seeding fail).

As yet you haven't even hinted at an actual reason for attempting to do this, which I find rather worrying - you are doing something that is dangerous (and therefore frowned upon by people who know what they are doing with databases), presumably just for the sake of making numbers 'look nice'.

I didn't see any document from MS said changing the seed will cause problem;Based on my testing,the codes MS provided work well for standalone database application but fails with multi connections since ADO Jet reports "table is currently be in use". Don't know who else ever met the same problem.

[leinad31]

If number is controlled (audited), is natural (captured from or synced with real world values rather than system generated), and needs to be serialized (you need control over increment/reset/decrement) then you shouldn't be using autonumber column.

You can store last sequence values in a table and lock rows accordingly to serialize transactions.

As to concurrency problem that will definitely arise, use surrogate keys to establish parent-child table relationships (surrogate keys are used internally, don't have to reflect real world values, and simply have to be unique so they can definitely use autonumber) then generate your control number as the LAST step to shorten period of row locking at sequence table. All records without control numbers can be reprocessed later (reassign control number), disregarded (e.g. a sales quotation that did not materialize) or deleted.

[si_the_geek]

I didn't see any document from MS said changing the seed will cause problem;Based on my testing,the codes MS provided work well for standalone database application but fails with multi connections since ADO Jet reports "table is currently be in use". Don't know who else ever met the same problem.

This is not something specific to MS products (tho I am sure they will have some articles on the subject, if you can pick the right keywords to search for), it is a general database design issue.

The issue has been encountered by many thousands of people, including hundreds who have posted about it on VBForums - which is why I was prompted to write the article (and why it has over 500 views in just 3 weeks - which is more than some FAQ articles from nearly a year ago).


What leinad31 posted is a good summary of the way you should be designing Primary Keys, and you can find many longer versions on the web (such as this) and in books that discuss database design.