[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 386
  • 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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