Append query to change the value of an autonumber field

[Guest]

Hi,

I tried to change the starting value of an autnumber field (which is also
the key primary key of the table) with an append query (following the
instructions of the help topic) but it doesn't work at all.

The fact is that I want to change the starting value of an Order ID
(autonumber and primary key field), so I created a temporary table with the
value I want less 1. Here is the SQL statement of the query:

INSERT INTO Orders ( [Order ID] )
SELECT [Temporary table].[Order ID]
FROM [Temporary table];

What is wrong with it? Anyone knows?

Remy

 

[Guest]

Try removing the leading and trailing spaces you have around ( [Order ID] )
to look like this --

INSERT INTO Orders ([Order ID])

 

[Gary Walter]

"Remy"wrote:

I tried to change the starting value of an autnumber field (which is also
the key primary key of the table) with an append query (following the
instructions of the help topic) but it doesn't work at all.

The fact is that I want to change the starting value of an Order ID
(autonumber and primary key field), so I created a temporary table with
the
value I want less 1. Here is the SQL statement of the query:

INSERT INTO Orders ( [Order ID] )
SELECT [Temporary table].[Order ID]
FROM [Temporary table];

use Sub SetAutoNumber(sTable As String, ByVal lNum As Long)
at
http://allenbrowne.com/ser-26.html

or, from
http://support.microsoft.com/?kbid=287756

Public Function ChangeSeed(strTbl As String, _
strCol As String, _
lngSeed As Long) As Boolean
'You must pass the following variables to this function.
'strTbl = Table containing autonumber field
'strCol = Name of the autonumber field
'lngSeed = Long integer value you want to use for next AutoNumber.

Dim cnn As ADODB.Connection
Dim cat As New ADOX.Catalog
Dim col As ADOX.Column

'Set connection and catalog to current database.
Set cnn = CurrentProject.Connection
cat.ActiveConnection = cnn

Set col = cat.Tables(strTbl).Columns(strCol)

col.Properties("Seed") = lngSeed
cat.Tables(strTbl).Columns.Refresh
If col.Properties("seed") = lngSeed Then
ChangeSeed = True
Else
ChangeSeed = False
End If
Set col = Nothing
Set cat = Nothing
Set cnn = Nothing

End Function