|
|
You're in the
sql server odbc
group:IDENTITY Data Type Gaps
sql server odbc:
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
I 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" [quoted text, click to view] <anonymous@discussions.microsoft.com> wrote:
>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
[quoted text, click to view]
"Paul" <anonymous@discussions.microsoft.com> wrote in message use an Access MDB as the front end (more mods need to be done to migrate thenews:68D7662C-5B5A-4F27-87FA-C2ED1DC43BFE@microsoft.com... > Hi: > > We recently upgraded an Access XP database to SQL Server, but continue to 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.) [quoted text, click to view] > data type. With a new record, SQL adds to the highest value for a new> When we converted to SQL, the AutoNumber fields were converted to IDENTITY 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). [quoted text, click to view] > generated by Access during its "replicated" phase are fairly close to the> One of the things we've discovered is that some of the random numbers 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. [quoted text, click to view] > records in it? (It would be OK if we were creating a new table, but we've> Is there any way to force SQL Identity values on a table that already has 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.) [quoted text, click to view] > See if DBCC CHECKIDENT will help you. You can find the syntax using a> 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 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 coming up to speed on SQL Server, 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 much to be done Thanks again
Mary
Your idea is an approach that I didn't think of. I'm going to look into this closely. I think it's a great idea and could help us get thru this difficult period. Thanks very much for the input! I'm learning SQL the hard way. I've been doing Access databases and Office customizations for several years, but when our company took 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
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" [quoted text, click to view] <anonymous@discussions.microsoft.com> wrote:
>Your idea is an approach that I didn't think of. I'm going to look into this closely. I think it's a great idea and could help us get thru this difficult period. Thanks very much for the input! I'm learning SQL the hard way. I've been doing Access databases and Office customizations for several years, but when our company took over a competing group, I had to dive into the deep end of the SQL Server pool without my Donald Duck life preserver.
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |