Q: Updating a table

9 Dec 2004 6:26 PM

Geoff Jones

Hi

I'm updating a table but trying to do it afresh every time i.e. erase any
records in it and start from the start. The only problem is that the primary
key doesn't re-set to its initial value i.e. it merely starts off where its
last maximum value was.

I'm trying to update an Access database if that is of any use.

Can anybody help?

Thanks in advance

Geoff

9 Dec 2004 6:56 PM

Marina

I assume you are talking about an incrementing integer key? In that case, I
am not sure if there is anything you can do. Set the key explicitly if you
care about it resetting - but why do you? Does it matter if it just picks up
where it left off? It's just a key.

Show quote

"Geoff Jones" <nodamnspam@email.com> wrote in message
news:41b898d5$0$3120$cc9e4d1f@news.dial.pipex.com...
> Hi
>
> I'm updating a table but trying to do it afresh every time i.e. erase any
> records in it and start from the start. The only problem is that the
primary
> key doesn't re-set to its initial value i.e. it merely starts off where
its
> last maximum value was.
>
> I'm trying to update an Access database if that is of any use.
>
> Can anybody help?
>
> Thanks in advance
>
> Geoff
>
>

10 Dec 2004 2:14 AM

Chris Anderson

Geoff,

When you wipe out all records, this does not change any of the metadata.
The increment and the current value are not reset based on deletion (in most
RDBMSs)

In many databases, you can manipulate this
i.e. SQL Server - DBCC CHECKIDENT ({tablename}, RESEED, 1)

No way to easily do this in Access, though.
You will find a variety of workarounds that basically involve copying the
table structure, removing the original one, and then renaming the new one.

Good luck,

Chris

Show quote

"Geoff Jones" <nodamnspam@email.com> wrote in message
news:41b898d5$0$3120$cc9e4d1f@news.dial.pipex.com...
> Hi
>
> I'm updating a table but trying to do it afresh every time i.e. erase any
> records in it and start from the start. The only problem is that the
> primary key doesn't re-set to its initial value i.e. it merely starts off
> where its last maximum value was.
>
> I'm trying to update an Access database if that is of any use.
>
> Can anybody help?
>
> Thanks in advance
>
> Geoff
>

10 Dec 2004 10:10 AM

Geoff Jones

HI Chris

I'm not sure I follow: removing the table itself or the structure? Could you
give me some example code?

Geoff

Show quote

"Chris Anderson" <can***@realworldis.com> wrote in message
news:coudnXrQy7IXmyTcRVn-tw@inreach.com...
> Geoff,
>
> When you wipe out all records, this does not change any of the metadata.
> The increment and the current value are not reset based on deletion (in
> most RDBMSs)
>
> In many databases, you can manipulate this
> i.e. SQL Server - DBCC CHECKIDENT ({tablename}, RESEED, 1)
>
> No way to easily do this in Access, though.
> You will find a variety of workarounds that basically involve copying the
> table structure, removing the original one, and then renaming the new one.
>
> Good luck,
>
> Chris
>
> "Geoff Jones" <nodamnspam@email.com> wrote in message
> news:41b898d5$0$3120$cc9e4d1f@news.dial.pipex.com...
>> Hi
>>
>> I'm updating a table but trying to do it afresh every time i.e. erase any
>> records in it and start from the start. The only problem is that the
>> primary key doesn't re-set to its initial value i.e. it merely starts off
>> where its last maximum value was.
>>
>> I'm trying to update an Access database if that is of any use.
>>
>> Can anybody help?
>>
>> Thanks in advance
>>
>> Geoff
>>
>
>