Reset Autonumber to 1 after delete before append records

[Guest]

Function DeleteAllAndResetAutoNum(strTable As String) As Boolean
'Return: True if sucessful.
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column
Dim strSql As String

'Delete all records.
strSql = "DELETE FROM [" & strTable & "];"
CurrentProject.Connection.Execute strSql

'Find and reset the AutoNum field.
cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables(strTable)
For Each col In tbl.Columns
If col.Properties("Autoincrement") Then
col.Properties("Seed") = 1
DeleteAllAndResetAutoNum = True
End If
Next
End Function
Have tried the code above taken from this website but always stalls on
col.Properties("Seed") = 1 - Invalid argument? Compiles ok.
Many thanks
William

 

[Allen Browne]

What error number and message do you see?

What version of the ADOX library are you using?

Is the code identifying the correct AutoNumber column before it fails?

If you open the table in design view, what are the properties of the
AutoNumber field? Normally you would expect Field Size to be "Long Integer",
and New Values to be "Increment".

 

[Guest]

What error number and message do you see?
Run-time error '-2147467259(80004005)':
Invalid argument

What version of the ADOX library are you using?

microsoft ADO Ext. 2.8 for DDL and security

Is the code identifying the correct AutoNumber column before it fails?

Yes, it gives the first number if you run the mouse over it.
col.properties("seed")=31080

If you open the table in design view, what are the properties of the
AutoNumber field?

Field Size "Long Integer" and New Values "Increment".
Appreciate the help
Many thanks
William

What error number and message do you see?

Run-time error '-2147467259(80004005)':
Invalid argument

What version of the ADOX library are you using?

microsoft ADO Ext. 2.8 for DDL and security

Is the code identifying the correct AutoNumber column before it fails?

Yes, it gives the first number if you run the mouse over it.
col.properties("seed")=31080

If you open the table in design view, what are the properties of the
AutoNumber field? Normally you would expect Field Size to be "Long Integer",
and New Values to be "Increment".

Field Size "Long Integer" and New Values "Increment".

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Function DeleteAllAndResetAutoNum(strTable As String) As Boolean
'Return: True if sucessful.
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column
Dim strSql As String

'Delete all records.
strSql = "DELETE FROM [" & strTable & "];"
CurrentProject.Connection.Execute strSql

'Find and reset the AutoNum field.
cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables(strTable)
For Each col In tbl.Columns
If col.Properties("Autoincrement") Then
col.Properties("Seed") = 1
DeleteAllAndResetAutoNum = True
End If
Next
End Function
Have tried the code above taken from this website but always stalls on
col.Properties("Seed") = 1 - Invalid argument? Compiles ok.
Many thanks
William

 

[Allen Browne]

William, this is odd. The only obvious thing would be if the code did not
actually delete all the records from the table.

ADOX is pretty buggy, but Ver 2.8 should be fine for this.

The code has correctly identified the column, and it does have a Seed
property if you can view the current value.

You are assigning an integer to it, which VBA should convert to a long
without problem, so it is the correct type for the property.

The "invalid argument" message might make sense if there are records in the
table using a higher value, and so ADOX recognised that 1 was too small a
number to let you assign.

Can you verify that the records have all gone at this point? There are none
with related records elsewhere that prevent them being deleted? No pending
transactions?

That's the only thing I can think of. Hope it leads you in a useful
direction.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

What error number and message do you see?

Run-time error '-2147467259(80004005)':
Invalid argument

What version of the ADOX library are you using?

microsoft ADO Ext. 2.8 for DDL and security

Is the code identifying the correct AutoNumber column before it fails?

Yes, it gives the first number if you run the mouse over it.
col.properties("seed")=31080

If you open the table in design view, what are the properties of the
AutoNumber field?

Field Size "Long Integer" and New Values "Increment".
Appreciate the help
Many thanks
William

What error number and message do you see?

Run-time error '-2147467259(80004005)':
Invalid argument

What version of the ADOX library are you using?

microsoft ADO Ext. 2.8 for DDL and security

Is the code identifying the correct AutoNumber column before it fails?

Yes, it gives the first number if you run the mouse over it.
col.properties("seed")=31080

If you open the table in design view, what are the properties of the
AutoNumber field? Normally you would expect Field Size to be "Long
Integer",
and New Values to be "Increment".

Field Size "Long Integer" and New Values "Increment".

Function DeleteAllAndResetAutoNum(strTable As String) As Boolean
'Return: True if sucessful.
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column
Dim strSql As String

'Delete all records.
strSql = "DELETE FROM [" & strTable & "];"
CurrentProject.Connection.Execute strSql

'Find and reset the AutoNum field.
cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables(strTable)
For Each col In tbl.Columns
If col.Properties("Autoincrement") Then
col.Properties("Seed") = 1
DeleteAllAndResetAutoNum = True
End If
Next
End Function
Have tried the code above taken from this website but always stalls on
col.Properties("Seed") = 1 - Invalid argument? Compiles ok.
Many thanks
William

 

[Guest]

Thank you for your help. I think i have been particularly stupid in the fact
i was trying to get it to work on a linked table! It works on a local table
perfectly. Is there any way that i can make it work on a linked table.
Many thanks
William Rice

William, this is odd. The only obvious thing would be if the code did not
actually delete all the records from the table.

ADOX is pretty buggy, but Ver 2.8 should be fine for this.

The code has correctly identified the column, and it does have a Seed
property if you can view the current value.

You are assigning an integer to it, which VBA should convert to a long
without problem, so it is the correct type for the property.

The "invalid argument" message might make sense if there are records in the
table using a higher value, and so ADOX recognised that 1 was too small a
number to let you assign.

Can you verify that the records have all gone at this point? There are none
with related records elsewhere that prevent them being deleted? No pending
transactions?

That's the only thing I can think of. Hope it leads you in a useful
direction.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

What error number and message do you see?

Run-time error '-2147467259(80004005)':
Invalid argument

What version of the ADOX library are you using?

microsoft ADO Ext. 2.8 for DDL and security

Is the code identifying the correct AutoNumber column before it fails?

Yes, it gives the first number if you run the mouse over it.
col.properties("seed")=31080

If you open the table in design view, what are the properties of the
AutoNumber field?

Field Size "Long Integer" and New Values "Increment".
Appreciate the help
Many thanks
William

What error number and message do you see?

Run-time error '-2147467259(80004005)':
Invalid argument

What version of the ADOX library are you using?

microsoft ADO Ext. 2.8 for DDL and security

Is the code identifying the correct AutoNumber column before it fails?

Yes, it gives the first number if you run the mouse over it.
col.properties("seed")=31080

If you open the table in design view, what are the properties of the
AutoNumber field? Normally you would expect Field Size to be "Long
Integer",
and New Values to be "Increment".

Field Size "Long Integer" and New Values "Increment".

Function DeleteAllAndResetAutoNum(strTable As String) As Boolean
'Return: True if sucessful.
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column
Dim strSql As String

'Delete all records.
strSql = "DELETE FROM [" & strTable & "];"
CurrentProject.Connection.Execute strSql

'Find and reset the AutoNum field.
cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables(strTable)
For Each col In tbl.Columns
If col.Properties("Autoincrement") Then
col.Properties("Seed") = 1
DeleteAllAndResetAutoNum = True
End If
Next
End Function
Have tried the code above taken from this website but always stalls on
col.Properties("Seed") = 1 - Invalid argument? Compiles ok.
Many thanks
William

 

[Allen Browne]

How about opening the back end mdb, and running the code there?

 

[David C. Holley]

What is the specific NEED that you have to reset the Autonumber?

 

[Guest]

I will try that, just worried it might take too long. i have worked round
the problem for the moment by appending the records locally and copying the
table to the back end. It might be that there is an easier way to sort the
whole problem. I am trying to import an excel spreadsheet which has new
records added to it all the time but cannot sort/delete the old ones. this
means importing the whole worksheet each time which has about a thousand
records a week generated by another programme. unfortunately the records are
not always in the same place or the same named worksheet! I have automated
it to the point that they can select any worksheet or range in any location
to import, to link it would mean this would always change. Any ideas would be
great! The reason for changing to access from excel is that the spreadsheet
has got so large that it takes 40 mins to calculate. Access will do the same
in about 5 secs. It works well how i have now done it but need to find out
how to compact and repair front and back end from a button on a form, twas
easy in access97 but seems to be more complicated in Access2003. many thanks
for your help, very much appreciated.

 

[Guest]

The number gets so large so quickly, perhaps this does not matter. At the
moment it would increase by 15000 each time an import is done but this is
growing at a very fast rate. When does an autonumber become too large?

 

[Rick Brandt]

The number gets so large so quickly, perhaps this does not matter. At the
moment it would increase by 15000 each time an import is done but this is
growing at a very fast rate. When does an autonumber become too large?

Not likely in your lifetime. fugedebodit

 

[David C. Holley]

Questions...

How does the data get into the Excel workbook? Do users input the data
directly? Is the Excel workbook created by a third-application as
output? Is it a combination of both?

Can the data in the Excel workbook be deleted after the import (Can you
start with a fresh/empty workbook?)

And most importantly, WHY is the data in an Excel workbook to begin with?

Often there's a tendency to try to make something work that
fundamentally could be improved which in the end would make life easier
all around.

David H

 

[Marshall Barton]

I will try that, just worried it might take too long. i have worked round
the problem for the moment by appending the records locally and copying the
table to the back end. It might be that there is an easier way to sort the
whole problem. I am trying to import an excel spreadsheet which has new
records added to it all the time but cannot sort/delete the old ones. this
means importing the whole worksheet each time which has about a thousand
records a week generated by another programme. unfortunately the records are
not always in the same place or the same named worksheet! I have automated
it to the point that they can select any worksheet or range in any location
to import, to link it would mean this would always change. Any ideas would be
great! The reason for changing to access from excel is that the spreadsheet
has got so large that it takes 40 mins to calculate. Access will do the same
in about 5 secs. It works well how i have now done it but need to find out
how to compact and repair front and back end from a button on a form, twas
easy in access97 but seems to be more complicated in Access2003. many thanks
for your help, very much appreciated.

I agree with David that you should investigate the core
requirement for the Excel file.

However, if it is essential to go through this import
process, then one way to avoid the autonumber growth issue
AND eliminate the need for Compact is to put the temp table
in a temp MDB file. See
http://www.granite.ab.ca/access/temptables.htm

OTOH, to reiterate Rick's comment, autonumber values can be
in the trillions so it doesn't really matter if it grows.