Re-Seed in SQL Server

[J E Jensen]

Hello NG

A continue from othe post.

In an access database i have problems with the seed in a autonumber field.
The autonumber field is used as a Primary / Foreign key relationship.
In one of my tables i have the max number for int, and now the database
gives me the error that the recordid already exists therefore data can't be
saved.
The Application that manages the database can have the data on a SQL
Server - but connected to an Access data that is linked to the SQL Server
database. I tryed to import all data to SQL Server and then set my Primary
key to BIGINT but Access can't read that large number - so all
records/fields is displayed as #Deleted in the linked access table.

What i need is a tool (application) that can reorder all my primary keys
starting from 1 (one) - and keeping the relationsships between the tables.

Eks: two tables Table1 and Table2

Table1:
RecordID, TextValue

Table2
RecordID, Table1RecordID, TextValue

The relationship between the tables is Table1.RecordID and
Table2.Table1RecordID
In Table1.RecordID i have the number 20.000, therefore the value in
Table2.Table1RecordID is also 20.000

The tool changes the Table1.RecordID to 1 - and to keep the relationship it
must also change the Table2.Table1RecordID to one.

Does that tool exists or do i have to make it? (the changes can be done on a
SQL Server 2000, og 2005) not nesseraly on Access.

Kind Regards

Johnny E. Jensen

 

[Gina Whipp]

J E Jensen,

A bit confusion in your explanation...

First you say... "and now the database gives me the error that the recordid
already exists therefore data can't be saved." This implies that your
Autonumber field in the Access Database has gotten corrupted. For that
issue see... http://allenbrowne.com/ser-40.html


Then you say... "I tryed to import all data to SQL Server and then set my
Primary key to BIGINT but Access can't read that large number - so all
records/fields is displayed as #Deleted in the linked access table." On the
SQL Server the Autonumber Data Type is INT. Under what circumstances do you
feel the need to change? If it's because of the above error then see the
above link.

And lastly, I do not belive the BIGINT is supported in Access, so if you
plan to link to those tables then stay away from that Data Type.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[J E Jensen]

Hello Steve

No thats not what i am saying.
The table holds just over 500.000 records but the database has been
replicate-able - therefore the autonumber is created as RANDOM. The
replicate-able is now removed (the database has been imported to new
database).
So the table now consists of random numbers.between -2147483648 and
2147483647 (those two numbers exists also in tables autonumber field). The
database has been imported to SQL Server. The application (an rather old
one) must have an Access database as bridge between Application and SQL
Server.

When i try to add a new record the application uses "recordset.addnew())
function and gets the number 2147483648 wich result in an overflow error. or
the function gets an existing number.

Thats why i want a tool to re-build the autonumber field so that i again
begins with 1.

Kind regards

Johnny E. Jensen

 

[J E Jensen]

Hello Gina

Yes it is a bit confusion.

I tryed the link from AllenBrowne - but the table that creating the error
holds the value 2147483647in the autonumber field and adding one to that
number gets a overflow error (its exeeds the limits for int datatype) Why
the number 2147483647 is in the autonumber field see my note to Steve.

Kind Regards

Johnny E. Jensen

 

[Gina Whipp]

Johnny,

Thank you for explanation, that really helped. Now on to your problem...

Reseeding and already filled table is not easy or *fun* and thankfully I
have only had to do it once. I did this before I found an *easier* way...

1. Make a back-up...
2. MAKE A BACK-UP...
3. Write down all the tables your main table is linked to and then *break*
the relationships...
4. Change your Autonumber field to a Long Integer in the Access table and
remove it's Primary Key status...
5. Add a new field to the table that holds the Primary Key. Call it NewID
or something like that and make it an Autonumber field and a Primary Key.
It will reseed...
6. In all the previous linked tables that contained the Foreign Key create
a new field (NewID)...
7. Run an Update query linking on the previous PK:FK relationship but
filling the NewID field...
8. Re-establish all previous relationships...
9. Move tables back to the SQL Server

OR

UNTESTED UNTESTED

If the table is already on the SQL Server you could try the below. But I
have never tested this so PLEASE MAKE A BACK-UP!!!

Dim cnn as ADODB.Connection
Dim StrSQl as string

StrSQL = "ALTER TABLE tblYourTableName ALTER COLUMN [YourAutonumberPKName]
COUNTER(1,1)"
cnn.Execute strSQL

OR

See this link...
http://support.microsoft.com/kb/884185/en-us?spid=2509&sid=106

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[J E Jensen]

Hello Steve

Thanks for your reply. The reason why i wanted a tool, is because there is
108 tables that needs fixing, but thanks anyway for your solution.

Kind regards

Johnny Jensen

Hello Johnny,

Good to meet you!!

Actually your problem is in Table 2. You need different autonumbers in
Table1 and then you need to change the foreign key in Table2 to match the
new autonumbers in Table1. It's pretty easy to fix; you do not need a
specialized tool to fix your problem.

1. Make a backup of your database file in case anything goes wrong.
2. Add a new field named OldRecordID to Table1. Make it Number - Long
Integer.
3. Create a query based on Table1 and only include the fields RecordID
and OldRecordID.
4. Change the query to an Update query.
5. Under OldRecordID where it says Update To, type in [RecordID]. (You
need to use the brackets!)
6. Run the query
7. Close the query and open Table1 in design view.
8. Delete the field RecordID.
9. Add a new field named RecordID and make it autnumber. This will
create new values for RecordID starting at 1.
10. Open Table2 in design view. Add "XX" to the front of
Table1RecordID.
11. Add a new field named Table1RecordID. Make it Number - Long
Integer.
12. Close Table2
13. Create a new query and include Table1 and Table2.
14. Delete any relationships that may be in the query window and Join
OldRecordID in Table1 to XXTable1RecordID in Table2
15. Change the query to an Update query
16. Under Table1RecordID where it says Update To, type in [RecordID].
(You need to use the brackets!)
17. Run the query
18. Close the query and open Table2 in design view
19. Delete the field XXTable1RecordID
20. Close Table2
21. Open Table1 in design view
22. Delete the field OldRecordID
23. Close Table1

You now have just over 500,000 new RecordIDs in Table1. You will now be
able to add about a trillion and a half new records to Table1 before you
run out of autonumbers. Also the foreign keys in Table2 have been changed
to the values corresponding to Table1 so your referential integrity is
still in place.

Steve
(e-mail address removed)

Hello Steve

No thats not what i am saying.
The table holds just over 500.000 records but the database has been
replicate-able - therefore the autonumber is created as RANDOM. The
replicate-able is now removed (the database has been imported to new
database).
So the table now consists of random numbers.between -2147483648 and
2147483647 (those two numbers exists also in tables autonumber field).
The database has been imported to SQL Server. The application (an rather
old one) must have an Access database as bridge between Application and
SQL Server.

When i try to add a new record the application uses "recordset.addnew())
function and gets the number 2147483648 wich result in an overflow error.
or the function gets an existing number.

Thats why i want a tool to re-build the autonumber field so that i again
begins with 1.

Kind regards

Johnny E. Jensen

 

[John... Visio MVP]

Hello Johnny,

It is highly unlikely that you will find a tool to fix your tables. With
108 tables to update, you need to atuomate the solution I gave you. I
could write the code for you if you wanted. I provide help with Access,
Excel and Word applications for a small fee. If you want me to write he
code for you, contact me.

Steve
(e-mail address removed)

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

A few gems gleaned from the Word New User newsgroup over the past year to
show Stevie's "expertise".


Dec 17, 2008 7:47 pm

Word 2007 ..........
In older versions of Word you could highlght some text then go to Format -
Change Case and change the case of the hoghloghted text. Is this still
available in Word 2007? Where?
Thanks! Steve


Dec 22, 2008 8:22 pm

I am designing a series of paystubs for a client. I start in landscape and
draw a table then add columns and rows to setup labels and their
corresponding value. This all works fine. After a landscape version is
completed, I next need to design a portrait version. Rather than strating
from scratch, I'd like to be able to cut and paste from the landscape
version and design the portrait version.
Steve


Dec 24, 2008, 1:12 PM

How do you protect the document for filling in forms?
Steve


One of my favourites:
Dec 30, 2008 8:07 PM - a reply to stevie
(The original poster asked how to sort a list and stevie offered to create
the OP an Access database)

Yes, you are right but a database is the correct tool to use not a
spreadsheet.

Not at all. If it's just a simple list then a spreadsheet is perfectly
adequate...


Sept 10, 2009
(In respose to a perfectly adequate GENERIC solution stevie wrote)

This function is specific to the example but not generic for any amount paid
out.

Steve



Sept 9, 2009

you can then return all the characters in front of it with the Left()
fumction. Would look like:
Left("YourString",Instr("YourString","VbCr" Or "VbLf") - 1)

Steve

No, it would not look like

Left("YourString",Instr("YourString","VbCr" Or "VbLf") - 1)

First of all, the constants are vbCr and vbLf: no quotes around them. With
the quotes, you're looking for the literal strings.

Second, you can't Or together character constants like that. Even if you
could, Or'ing them together in the InStr function like that makes no sense
at all.



John... Visio MVP

 

[Gina Whipp]

OR he could automate the FREE code I provided...

OR ask for FREE help to automate the FREE code I provided...


UNTESTED UNTESTED

If the table is already on the SQL Server you could try the below. But I
have never tested this so PLEASE MAKE A BACK-UP!!!

Dim cnn as ADODB.Connection
Dim StrSQl as string

StrSQL = "ALTER TABLE tblYourTableName ALTER COLUMN [YourAutonumberPKName]
COUNTER(1,1)"
cnn.Execute strSQL

OR

See this link...
http://support.microsoft.com/kb/884185/en-us?spid=2509&sid=106

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Hello Johnny,

It is highly unlikely that you will find a tool to fix your tables. With
108 tables to update, you need to atuomate the solution I gave you. I
could write the code for you if you wanted. I provide help with Access,
Excel and Word applications for a small fee. If you want me to write he
code for you, contact me.

Steve
(e-mail address removed)

Hello Steve

Thanks for your reply. The reason why i wanted a tool, is because there
is 108 tables that needs fixing, but thanks anyway for your solution.

Kind regards

Johnny Jensen

Hello Johnny,

Good to meet you!!

Actually your problem is in Table 2. You need different autonumbers in
Table1 and then you need to change the foreign key in Table2 to match
the new autonumbers in Table1. It's pretty easy to fix; you do not need
a specialized tool to fix your problem.

1. Make a backup of your database file in case anything goes wrong.
2. Add a new field named OldRecordID to Table1. Make it Number - Long
Integer.
3. Create a query based on Table1 and only include the fields
RecordID and OldRecordID.
4. Change the query to an Update query.
5. Under OldRecordID where it says Update To, type in [RecordID].
(You need to use the brackets!)
6. Run the query
7. Close the query and open Table1 in design view.
8. Delete the field RecordID.
9. Add a new field named RecordID and make it autnumber. This will
create new values for RecordID starting at 1.
10. Open Table2 in design view. Add "XX" to the front of
Table1RecordID.
11. Add a new field named Table1RecordID. Make it Number - Long
Integer.
12. Close Table2
13. Create a new query and include Table1 and Table2.
14. Delete any relationships that may be in the query window and Join
OldRecordID in Table1 to XXTable1RecordID in Table2
15. Change the query to an Update query
16. Under Table1RecordID where it says Update To, type in [RecordID].
(You need to use the brackets!)
17. Run the query
18. Close the query and open Table2 in design view
19. Delete the field XXTable1RecordID
20. Close Table2
21. Open Table1 in design view
22. Delete the field OldRecordID
23. Close Table1

You now have just over 500,000 new RecordIDs in Table1. You will now be
able to add about a trillion and a half new records to Table1 before you
run out of autonumbers. Also the foreign keys in Table2 have been
changed to the values corresponding to Table1 so your referential
integrity is still in place.

Steve
(e-mail address removed)




Hello Steve

No thats not what i am saying.
The table holds just over 500.000 records but the database has been
replicate-able - therefore the autonumber is created as RANDOM. The
replicate-able is now removed (the database has been imported to new
database).
So the table now consists of random numbers.between -2147483648 and
2147483647 (those two numbers exists also in tables autonumber field).
The database has been imported to SQL Server. The application (an
rather old one) must have an Access database as bridge between
Application and SQL Server.

When i try to add a new record the application uses
"recordset.addnew()) function and gets the number 2147483648 wich
result in an overflow error. or the function gets an existing number.

Thats why i want a tool to re-build the autonumber field so that i
again begins with 1.

Kind regards

Johnny E. Jensen


Autonumber is a long integer data type. Long Integer can record over
two trillion unique records. Are you saying that your table has over
two trillion records? Of course that is not entirly true. Autonumber
does not repeat so if you delete a record the autonumber is lost
forever. Even so, you're table would have to still have an inordinate
number of records to run out of autonumbers. What is the size of your
database file? Something is wrong!!

Steve
(e-mail address removed)

Hello NG

A continue from othe post.

In an access database i have problems with the seed in a autonumber
field. The autonumber field is used as a Primary / Foreign key
relationship.
In one of my tables i have the max number for int, and now the
database gives me the error that the recordid already exists
therefore data can't be saved.
The Application that manages the database can have the data on a SQL
Server - but connected to an Access data that is linked to the SQL
Server database. I tryed to import all data to SQL Server and then
set my Primary key to BIGINT but Access can't read that large
number - so all records/fields is displayed as #Deleted in the linked
access table.

What i need is a tool (application) that can reorder all my primary
keys starting from 1 (one) - and keeping the relationsships between
the tables.

Eks: two tables Table1 and Table2

Table1:
RecordID, TextValue

Table2
RecordID, Table1RecordID, TextValue

The relationship between the tables is Table1.RecordID and
Table2.Table1RecordID
In Table1.RecordID i have the number 20.000, therefore the value in
Table2.Table1RecordID is also 20.000

The tool changes the Table1.RecordID to 1 - and to keep the
relationship it must also change the Table2.Table1RecordID to one.

Does that tool exists or do i have to make it? (the changes can be
done on a SQL Server 2000, og 2005) not nesseraly on Access.

Kind Regards

Johnny E. Jensen

 

[J E Jensen]

Hello NG

Everyone hold your horses.

I am a developer myself. The only reason way i requested a tool was to skip
over a few steps.... ( lazy me )
I'am not using either of the above codes but only the above directions.

Thanks to Steve and to Gina for taking your time with this problem.

Kind regards

Johnny E. Jensen

Hello Johnny,

It is highly unlikely that you will find a tool to fix your tables. With
108 tables to update, you need to atuomate the solution I gave you. I
could write the code for you if you wanted. I provide help with Access,
Excel and Word applications for a small fee. If you want me to write he
code for you, contact me.

Steve
(e-mail address removed)

Hello Steve

Thanks for your reply. The reason why i wanted a tool, is because there
is 108 tables that needs fixing, but thanks anyway for your solution.

Kind regards

Johnny Jensen

Hello Johnny,

Good to meet you!!

Actually your problem is in Table 2. You need different autonumbers in
Table1 and then you need to change the foreign key in Table2 to match
the new autonumbers in Table1. It's pretty easy to fix; you do not need
a specialized tool to fix your problem.

1. Make a backup of your database file in case anything goes wrong.
2. Add a new field named OldRecordID to Table1. Make it Number - Long
Integer.
3. Create a query based on Table1 and only include the fields
RecordID and OldRecordID.
4. Change the query to an Update query.
5. Under OldRecordID where it says Update To, type in [RecordID].
(You need to use the brackets!)
6. Run the query
7. Close the query and open Table1 in design view.
8. Delete the field RecordID.
9. Add a new field named RecordID and make it autnumber. This will
create new values for RecordID starting at 1.
10. Open Table2 in design view. Add "XX" to the front of
Table1RecordID.
11. Add a new field named Table1RecordID. Make it Number - Long
Integer.
12. Close Table2
13. Create a new query and include Table1 and Table2.
14. Delete any relationships that may be in the query window and Join
OldRecordID in Table1 to XXTable1RecordID in Table2
15. Change the query to an Update query
16. Under Table1RecordID where it says Update To, type in [RecordID].
(You need to use the brackets!)
17. Run the query
18. Close the query and open Table2 in design view
19. Delete the field XXTable1RecordID
20. Close Table2
21. Open Table1 in design view
22. Delete the field OldRecordID
23. Close Table1

You now have just over 500,000 new RecordIDs in Table1. You will now be
able to add about a trillion and a half new records to Table1 before you
run out of autonumbers. Also the foreign keys in Table2 have been
changed to the values corresponding to Table1 so your referential
integrity is still in place.

Steve
(e-mail address removed)




Hello Steve

No thats not what i am saying.
The table holds just over 500.000 records but the database has been
replicate-able - therefore the autonumber is created as RANDOM. The
replicate-able is now removed (the database has been imported to new
database).
So the table now consists of random numbers.between -2147483648 and
2147483647 (those two numbers exists also in tables autonumber field).
The database has been imported to SQL Server. The application (an
rather old one) must have an Access database as bridge between
Application and SQL Server.

When i try to add a new record the application uses
"recordset.addnew()) function and gets the number 2147483648 wich
result in an overflow error. or the function gets an existing number.

Thats why i want a tool to re-build the autonumber field so that i
again begins with 1.

Kind regards

Johnny E. Jensen


Autonumber is a long integer data type. Long Integer can record over
two trillion unique records. Are you saying that your table has over
two trillion records? Of course that is not entirly true. Autonumber
does not repeat so if you delete a record the autonumber is lost
forever. Even so, you're table would have to still have an inordinate
number of records to run out of autonumbers. What is the size of your
database file? Something is wrong!!

Steve
(e-mail address removed)

Hello NG

A continue from othe post.

In an access database i have problems with the seed in a autonumber
field. The autonumber field is used as a Primary / Foreign key
relationship.
In one of my tables i have the max number for int, and now the
database gives me the error that the recordid already exists
therefore data can't be saved.
The Application that manages the database can have the data on a SQL
Server - but connected to an Access data that is linked to the SQL
Server database. I tryed to import all data to SQL Server and then
set my Primary key to BIGINT but Access can't read that large
number - so all records/fields is displayed as #Deleted in the linked
access table.

What i need is a tool (application) that can reorder all my primary
keys starting from 1 (one) - and keeping the relationsships between
the tables.

Eks: two tables Table1 and Table2

Table1:
RecordID, TextValue

Table2
RecordID, Table1RecordID, TextValue

The relationship between the tables is Table1.RecordID and
Table2.Table1RecordID
In Table1.RecordID i have the number 20.000, therefore the value in
Table2.Table1RecordID is also 20.000

The tool changes the Table1.RecordID to 1 - and to keep the
relationship it must also change the Table2.Table1RecordID to one.

Does that tool exists or do i have to make it? (the changes can be
done on a SQL Server 2000, og 2005) not nesseraly on Access.

Kind Regards

Johnny E. Jensen