[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 381
  • Last Modified:

Access SQL autonumber update problem

I have a SQL 2000 database with an autonumber as the key field (intNewIntkey), I restored the database to a test system and ran this code: -
    Do Until rst.BOF Or rst.EOF = True
        With rsItem
            .AddNew
            ![ContractNumber] = rst!ContractNumber
            ![Description] = rst!Description
            .update
            .Bookmark = .LastModified
            intNewIntkey = ![InternalKeyNumber]
        End With

I ran this on the test and imported 300 items no problem, on the live it fails as the InternalKeyNumber is null (error 3022), can anyone help?
0
HKFuey
Asked:
HKFuey
1 Solution
 
mbizupCommented:
Double check the column Identity Specification property for InternalKeyNumber in your SQL back end.

Is Identity should be set to YES.
0
 
sachinpatil10dCommented:
0
 
HKFueyAuthor Commented:
Hi mbizup,
Identity is set to yes. I did a restore anyway as I suspected corruption. Users only lost 3/4 hours work.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
dqmqCommented:
Assuming your identity is incrementing, do this from the SQL prompt:

SELECT MAX([InternalKeyNumber]) FROM yourtable

DBCC CHECKIDENT ('yourtable', RESEED, #######)


For ###### substitute the number returned from the first select.
0
 
HKFueyAuthor Commented:
Hi dqmg,
The internal key number links to other tables, will this break the link?
0
 
dqmqCommented:
It doesn't update existing rows... just affects rows that are subsequently added.  If the rows being inserted are linked to other tables, then you have a serious matter.
0
 
HKFueyAuthor Commented:
Thanks dqmq, I got it to work, but I also changed my data so not sure what did it!!
Happy anyway : )
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now