How to Reset AutoNumber in Linked Access Table

[lunchroom]

How can I programmatically reset an AutoNumber column in a linked
(stressing LINKED) Access 2003 table?

Yes, I am aware of the whole discussion on why its a bad idea to care
about the value of an AutoNumber column and why an AutoNumber primary
key should carry no meaning, etc. For reasons beyond my control, I am
only interested in the technical solution my question - I don't care
to start yet another discussion on primary keys and their purpose,
etc.

Thanks
lunchroom

 

[Douglas J. Steele]

What do you mean by "reset"? Start the numbering at 1 again? Delete all of
the data from the table, and compact the database.

 

[lunchroom]

What do you mean by "reset"? Start the numbering at 1 again? Delete all of
the data from the table, and compact the database.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)








- Show quoted text -

Thanks for your response, Doug. Sorry for not being clear, but yes my
objective is to reset it back to 1. I realize I can manually delete
the data and manually compact/repair and this will do the trick, but
what I'm trying to do is programmatically achieve this from the front-
end .MDB. Ideally, I'd like to create a VBA function that I can pass
in the table name and column name and do something like this:

CurrentDb.Execute "ALTER TABLE " & TableName & " ALTER COLUMN " &
ColumnName & " COUNTER(1,1)"

This works on a non-linked table. Is there a way to accomplish the
same objective on a linked table?

Thanks for any suggestions.

 

[Douglas J. Steele]

I would think that the preferred way would be to delete the data using a
query, and compact using the CompactDatabase method of the Database object.

You might be able to get away with using DDL if you make a reference to the
back-end database (i.e.: don't use CurrentDb):

Dim dbBackend As DAO.Database

Set dbBackend = OpenDatabase("E:\Folder\File.mdb")
dbBackend.Execute "ALTER TABLE " & TableName & _
" ALTER COLUMN " & ColumnName & " COUNTER(1,1)"



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Thanks for your response, Doug. Sorry for not being clear, but yes my
objective is to reset it back to 1. I realize I can manually delete
the data and manually compact/repair and this will do the trick, but
what I'm trying to do is programmatically achieve this from the front-
end .MDB. Ideally, I'd like to create a VBA function that I can pass
in the table name and column name and do something like this:

CurrentDb.Execute "ALTER TABLE " & TableName & " ALTER COLUMN " &
ColumnName & " COUNTER(1,1)"

This works on a non-linked table. Is there a way to accomplish the
same objective on a linked table?

Thanks for any suggestions.

 

[lunchroom]

I would think that the preferred way would be to delete the data using a
query, and compact using the CompactDatabase method of the Database object..

You might be able to get away with using DDL if you make a reference to the
back-end database (i.e.: don't use CurrentDb):

Dim dbBackend As DAO.Database

  Set dbBackend = OpenDatabase("E:\Folder\File.mdb")
  dbBackend.Execute "ALTER TABLE " & TableName & _
    " ALTER COLUMN " & ColumnName & " COUNTER(1,1)"

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)



Thanks for your response, Doug. Sorry for not being clear, but yes my
objective is to reset it back to 1.  I realize I can manually delete
the data and manually compact/repair and this will do the trick, but
what I'm trying to do is programmatically achieve this from the front-
end .MDB.  Ideally, I'd like to create a VBA function that I can pass
in the table name and column name and do something like this:

CurrentDb.Execute "ALTER TABLE " & TableName & " ALTER COLUMN " &
ColumnName & " COUNTER(1,1)"

This works on a non-linked table.  Is there a way to accomplish the
same objective on a linked table?

Thanks for any suggestions.

Good work, Doug. That's what I needed. For reference, here is the
function I ended up with. The intention was to give the ability reset
(drop all data and reset AutoNumber/Identity column to 1) for either a
SQL Server linked table or an Access linked table. For the SQL Server
option, I've created a stored procedure to handle this (see below).

Access Function:
Function ResetTable(TableType As Integer, TableName As String,
ColumnName As String, DBPath As String)
Dim dbBackend As DAO.Database
DoCmd.SetWarnings False
Select Case TableType
Case Is = 1 'SQL Server
Call ExecuteSPT("EXEC ResetTable '" & TableName & "'", 0)
Case Is = 2 'Native Access
Set dbBackend = OpenDatabase(DBPath)
DoCmd.RunSQL "DELETE * FROM " & TableName
dbBackend.Execute "ALTER TABLE " & TableName & " ALTER
COLUMN " & ColumnName & " COUNTER(1,1)"
End Select
DoCmd.SetWarnings True
End Function

Stored procedure used when the first parm = 1:
CREATE PROCEDURE ResetTable
(
@TableName nvarchar(100)
)
AS
BEGIN
DECLARE @pSQL varchar(8000)

SET @pSQL = 'TRUNCATE TABLE ' + @TableName
EXEC (@pSQL)
END
GO