IDENTITY Data Type Gaps

Hi:
We recently upgraded an Access XP database to SQL Server, but continue to us
e an Access MDB as the front end (more mods need to be done to migrate the f
ront end to an Access Project). In the original Access database, several ta
bles use an Access AutoNumb
er data type as a primary key. At one point in time, the Access database wa
s converted to a replicated database. Following the conversion, the AutoNum
ber fields no longer create new values by adding 1 to the previous value; th
ey now use a random number
generator. (We converted the database back to a non-replicated format sever
al months prior to converting to SQL Server.)
When we converted to SQL, the AutoNumber fields were converted to IDENTITY d
ata type. With a new record, SQL adds to the highest value for a new Identi
ty field value. We've been having a number of "ODBC Time Out", and "Failed
insert on linked table" err
ors when adding records, (the "link fail" errors occur even when the Access
form being used to add the new record is based on a single table).
One of the things we've discovered is that some of the random numbers genera
ted by Access during its "replicated" phase are fairly close to the maximum
values allowed for the Access AutoNumber data type. (FYI, the Access AutoNu
mber is a "long integer" da
ta type; more or less equivalent to a SQL Server "int" data type.) One of t
he tables with frequent problems only has about 25,000 records, but some of
its records have primary key values that hover near the limit of the "long i
nt" data type. This has re
sulted in huge gaps in the numeric values in the new Identity field values.
I've tried everything I could find to see if I could force SQL Server to st
art new Identity field values in some of the lower value gaps, i.e., I tried
seeding the Identity field
at 27,000, and adding 1 for new records. However, it doesn't work.
Is there any way to force SQL Identity values on a table that already has re
cords in it? (It would be OK if we were creating a new table, but we've got
thousands of related records to these problematic tables so it'd be a bit t
ricky to create new Identit
y field values...although we will probably do this at some point in time.)
Any help would be really appreciated. If what I wrote makes no sense, I'm n
ot surprised. I've been working for 16 hours and I'm burnt.
THANKS!
PaulI don't think you're going to have any joy with this app until you get
rid of the identity property on the columns. However, I think you can
do this without messing up existing data.
Since you're working with linked tables with a Jet front-end, this
gives you some flexibility in coding a workaround. One solution would
be to remove the Identity property from the PK columns and write code
to generatenew unique int PK values yourself. One way you could do
this is to base new records on a local Jet table that has the
identical structure as the SQL Server table. All of this data could
get fed to a parameterized stored procedure that created a new record
and generated the new ID, which would be returned to the application
as an output parameter. Code in the stored procedure would create the
pk value based on querying the table to see if a particular value in a
particular range has been used. Wrap all this in an explicit
transaction that only commits once a valid PK in the numeric range
you're looking to fill is created. The new record could then be
dynamically displayed on a form based on the output parameter value in
a WHERE clause in the form's recordsource.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Sun, 29 Feb 2004 16:51:06 -0800, "Paul"
<anonymous@.discussions.microsoft.com> wrote:

>Hi:
>We recently upgraded an Access XP database to SQL Server, but continue to use an Ac
cess MDB as the front end (more mods need to be done to migrate the front end to an
Access Project). In the original Access database, several tables use an Access Auto
Num
ber data type as a primary key. At one point in time, the Access database w
as converted to a replicated database. Following the conversion, the AutoNu
mber fields no longer create new values by adding 1 to the previous value; t
hey now use a random number
generator. (We converted the database back to a non-replicated format several months prior
to converting to SQL Server.)
>When we converted to SQL, the AutoNumber fields were converted to IDENTITY data typ
e. With a new record, SQL adds to the highest value for a new Identity field value.
We've been having a number of "ODBC Time Out", and "Failed insert on linked table"
er
rors when adding records, (the "link fail" errors occur even when the Access form being use
d to add the new record is based on a single table).
>One of the things we've discovered is that some of the random numbers generated by
Access during its "replicated" phase are fairly close to the maximum values allowed
for the Access AutoNumber data type. (FYI, the Access AutoNumber is a "long integer
" d
ata type; more or less equivalent to a SQL Server "int" data type.) One of
the tables with frequent problems only has about 25,000 records, but some of
its records have primary key values that hover near the limit of the "long
int" data type. This has r
esulted in huge gaps in the numeric values in the new Identity field values.
I've tried everything I could find to see if I could force SQL Server to s
tart new Identity field values in some of the lower value gaps, i.e., I trie
d seeding the Identity fiel
d at 27,000, and adding 1 for new records. However, it doesn't work.
>Is there any way to force SQL Identity values on a table that already has records i
n it? (It would be OK if we were creating a new table, but we've got thousands of r
elated records to these problematic tables so it'd be a bit tricky to create new Ide
nti
ty field values...although we will probably do this at some point in time.)
>Any help would be really appreciated. If what I wrote makes no sense, I'm
not surprised. I've been working for 16 hours and I'm burnt.
>THANKS!
>Paul|||"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:68D7662C-5B5A-4F27-87FA-C2ED1DC43BFE@.microsoft.com...
> Hi:
> We recently upgraded an Access XP database to SQL Server, but continue to
use an Access MDB as the front end (more mods need to be done to migrate the
front end to an Access Project). In the original Access database, several
tables use an Access AutoNumber data type as a primary key. At one point in
time, the Access database was converted to a replicated database. Following
the conversion, the AutoNumber fields no longer create new values by adding
1 to the previous value; they now use a random number generator. (We
converted the database back to a non-replicated format several months prior
to converting to SQL Server.)
> When we converted to SQL, the AutoNumber fields were converted to IDENTITY
data type. With a new record, SQL adds to the highest value for a new
Identity field value. We've been having a number of "ODBC Time Out", and
"Failed insert on linked table" errors when adding records, (the "link fail"
errors occur even when the Access form being used to add the new record is
based on a single table).
> One of the things we've discovered is that some of the random numbers
generated by Access during its "replicated" phase are fairly close to the
maximum values allowed for the Access AutoNumber data type. (FYI, the
Access AutoNumber is a "long integer" data type; more or less equivalent to
a SQL Server "int" data type.) One of the tables with frequent problems
only has about 25,000 records, but some of its records have primary key
values that hover near the limit of the "long int" data type. This has
resulted in huge gaps in the numeric values in the new Identity field
values. I've tried everything I could find to see if I could force SQL
Server to start new Identity field values in some of the lower value gaps,
i.e., I tried seeding the Identity field at 27,000, and adding 1 for new
records. However, it doesn't work.
> Is there any way to force SQL Identity values on a table that already has
records in it? (It would be OK if we were creating a new table, but we've
got thousands of related records to these problematic tables so it'd be a
bit tricky to create new Identity field values...although we will probably
do this at some point in time.)
> Any help would be really appreciated. If what I wrote makes no sense, I'm
not surprised. I've been working for 16 hours and I'm burnt.
> THANKS!
> Paul
See if DBCC CHECKIDENT will help you. You can find the syntax using a
search engine.
Glenn|||Glenn:
I'll give this a shot. Thanks for your input! I've been developing Access
databases and customizing Office solutions for about 5 or 6 years, but when
our company bought out a competitor, I got thrown into the deep end. I'm co
ming up to speed on SQL Ser
ver, but am running into problems partly due to the situation, partly due to
inexperience w/ SQL. Little by little, we're gaining ground, but there's m
uch to be done.
Thanks again!
Paul|||Mary:
Your idea is an approach that I didn't think of. I'm going to look into thi
s closely. I think it's a great idea and could help us get thru this diffic
ult period. Thanks very much for the input! I'm learning SQL the hard way.
I've been doing Access da
tabases and Office customizations for several years, but when our company to
ok over a competing group, I had to dive into the deep end of the SQL Server
pool without my Donald Duck life preserver.
Thanks much again!
Paul|||Well, the newsgroups are a good place to get ideas and info -- good
luck -- the learning curve is indeed a bit steep when you are
attempting to build something more than the most simplistic app.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Sun, 7 Mar 2004 16:56:04 -0800, "Paul"
<anonymous@.discussions.microsoft.com> wrote:

>Your idea is an approach that I didn't think of. I'm going to look into this close
ly. I think it's a great idea and could help us get thru this difficult period. Th
anks very much for the input! I'm learning SQL the hard way. I've been doing Acces
s d
atabases and Office customizations for several years, but when our company t
ook over a competing group, I had to dive into the deep end of the SQL Serve
r pool without my Donald Duck life preserver.