[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
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)
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
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.
Re: Problem : Set the Seed of an AutoNumber using ADOX
Quote:
Originally Posted by
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.
I have never seen that error be inaccurate - but at the moment I can't see why it is occurring.
Quote:
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).
Quote:
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.
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).
If you haven't read the article I linked to yet, I strongly suggest you do it now.
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
Re: Problem : Set the Seed of an AutoNumber using ADOX
Quote:
Originally Posted by
Jonney
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.
Quote:
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.
Re: Problem : Set the Seed of an AutoNumber using ADOX
Quote:
Originally Posted by
si_the_geek
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
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.
Re: Problem : Set the Seed of an AutoNumber using ADOX
Quote:
Originally Posted by
si_the_geek
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.:mad:
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.
Re: Problem : Set the Seed of an AutoNumber using ADOX
Quote:
Originally Posted by
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.
Thanks.
It works only for standalone application.:(
Still Pending for more input and research.
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.
Re: Problem : Set the Seed of an AutoNumber using ADOX
Quote:
Originally Posted by
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.
Thanks. I keep in mind.
1 Attachment(s)
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.
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'.
Re: Problem : Set the Seed of an AutoNumber using ADOX
Quote:
Originally Posted by
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'.
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.
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.
Re: Problem : Set the Seed of an AutoNumber using ADOX
Quote:
Originally Posted by
Jonney
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.
