dcsimg
Results 1 to 19 of 19

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

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Jan 2010
    Posts
    1,103

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

    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

    Code:
    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
    Last edited by Jonney; Apr 22nd, 2010 at 04:31 AM. Reason: Wrong thread,don't know how to move to database thread

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,025

    Re: Problem : Set the Seed of an AutoNumber using ADOX

    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)

  3. #3

    Thread Starter
    Frenzied Member
    Join Date
    Jan 2010
    Posts
    1,103

    Re: Problem : Set the Seed of an AutoNumber using ADOX

    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.

    Code:
    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
    Last edited by Jonney; Apr 22nd, 2010 at 05:48 AM.

  4. #4

    Thread Starter
    Frenzied Member
    Join Date
    Jan 2010
    Posts
    1,103

    Re: Problem : Set the Seed of an AutoNumber using ADOX

    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.

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,025

    Re: Problem : Set the Seed of an AutoNumber using ADOX

    Quote Originally Posted by Jonney View Post
    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.

  6. #6

    Thread Starter
    Frenzied Member
    Join Date
    Jan 2010
    Posts
    1,103

    Re: Problem : Set the Seed of an AutoNumber using ADOX

    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

  7. #7
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,025

    Re: Problem : Set the Seed of an AutoNumber using ADOX

    Quote Originally Posted by Jonney View Post
    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.

  8. #8

    Thread Starter
    Frenzied Member
    Join Date
    Jan 2010
    Posts
    1,103

    Re: Problem : Set the Seed of an AutoNumber using ADOX

    Quote Originally Posted by si_the_geek View Post
    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.
    Code:
    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

  9. #9
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,025

    Re: Problem : Set the Seed of an AutoNumber using ADOX

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

  10. #10

    Thread Starter
    Frenzied Member
    Join Date
    Jan 2010
    Posts
    1,103

    Re: Problem : Set the Seed of an AutoNumber using ADOX

    Quote Originally Posted by si_the_geek View Post
    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.
    Last edited by Jonney; Apr 22nd, 2010 at 05:55 AM.

  11. #11
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,025

    Re: Problem : Set the Seed of an AutoNumber using ADOX

    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.

  12. #12

    Thread Starter
    Frenzied Member
    Join Date
    Jan 2010
    Posts
    1,103

    Re: Problem : Set the Seed of an AutoNumber using ADOX

    Quote Originally Posted by si_the_geek View Post
    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.

  13. #13
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,025

    Re: Problem : Set the Seed of an AutoNumber using ADOX

    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.

  14. #14

    Thread Starter
    Frenzied Member
    Join Date
    Jan 2010
    Posts
    1,103

    Re: Problem : Set the Seed of an AutoNumber using ADOX

    Quote Originally Posted by si_the_geek View Post
    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.

  15. #15

    Thread Starter
    Frenzied Member
    Join Date
    Jan 2010
    Posts
    1,103

    Re: Problem : Set the Seed of an AutoNumber using ADOX

    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.
    Attached Images Attached Images  

  16. #16
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,025

    Re: Problem : Set the Seed of an AutoNumber using ADOX

    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'.

  17. #17

    Thread Starter
    Frenzied Member
    Join Date
    Jan 2010
    Posts
    1,103

    Re: Problem : Set the Seed of an AutoNumber using ADOX

    Quote Originally Posted by si_the_geek View Post
    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.

  18. #18
    PowerPoster
    Join Date
    Nov 2002
    Location
    Manila
    Posts
    7,629

    Re: Problem : Set the Seed of an AutoNumber using ADOX

    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.
    Last edited by leinad31; Apr 22nd, 2010 at 10:45 PM.

  19. #19
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,025

    Re: Problem : Set the Seed of an AutoNumber using ADOX

    Quote Originally Posted by Jonney View Post
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width