If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Access & OleDb - generating schema changes, problem with identity/counter fields.
Hello,
I am trying to auto sync db schemata (in access, among other databases). Means, there is a C# application that generates SQL for generating schema changes in the access database. I am stuck with two problems: ONE: how can I turn an int field into a counter/identity field? The following DDL statement: ALTER TABLE [ShpCountry] ALTER COLUMN [OID] COUNTER CONSTRAINT PK_OID PRIMARY KEY Produces the following error: OleDbException, Invalid field data type. (SQL State 3259). It does work creating a field of this type. adly we DO have some databases out where counter-fields were (erronously) generated as numeric fields, so I need to get this changes. TWO: how do I allow writing manual values into a counter field in SQL? We need to load "start data" into the database. I WOULD be ok with turning them back into a non-counter-field (i.e. numeric) to load the data, but then we end up again on problem ONE - turning them back INTO a counter. Anyone an idea how to handle this? I have all other stuff under control (index creation, drops, everything), just the "numeric to counter" gets on my nerves for weeks now. -- Regards Thomas Tomiczek THONA Software & Consulting Ltd. (Microsoft MVP C#/.NET) (CTO PowerNodes Ltd.) --- Still waiting for ObjectSpaces? Try the EntityBroker today - more versatile, more powerfull. And something in use NOW. for the projects you have to deliver - NOW. |
#2
|
|||
|
|||
On Tue, 2 Nov 2004 09:20:09 +0100, "Thomas Tomiczek [MVP]" wrote:
¤ Hello, ¤ ¤ I am trying to auto sync db schemata (in access, among other databases). ¤ Means, there is a C# application that generates SQL for generating schema ¤ changes in the access database. ¤ ¤ I am stuck with two problems: ¤ ¤ ONE: how can I turn an int field into a counter/identity field? The ¤ following DDL statement: ¤ ¤ ALTER TABLE [ShpCountry] ¤ ALTER COLUMN [OID] COUNTER CONSTRAINT PK_OID PRIMARY KEY ¤ ¤ Produces the following error: OleDbException, Invalid field data type. (SQL ¤ State 3259). ¤ ¤ It does work creating a field of this type. adly we DO have some databases ¤ out where counter-fields were (erronously) generated as numeric fields, so I ¤ need to get this changes. ¤ ¤ TWO: how do I allow writing manual values into a counter field in SQL? We ¤ need to load "start data" into the database. I WOULD be ok with turning them ¤ back into a non-counter-field (i.e. numeric) to load the data, but then we ¤ end up again on problem ONE - turning them back INTO a counter. ¤ ¤ Anyone an idea how to handle this? I have all other stuff under control ¤ (index creation, drops, everything), just the "numeric to counter" gets on ¤ my nerves for weeks now. I don't believe that you can convert an Integer data type into an autoincrement (Counter) using DDL since by definition the values are automatically generated. Nor can you programmatically enter a value into one of these data types. Paul ~~~ Microsoft MVP (Visual Basic) |
#3
|
|||
|
|||
This would be terribly bad news - it would basically mean we have to drop
support for access for more than the most trivial operations. -- Regards Thomas Tomiczek THONA Software & Consulting Ltd. (Microsoft MVP C#/.NET) (CTO PowerNodes Ltd.) --- Still waiting for ObjectSpaces? Try the EntityBroker today - more versatile, more powerfull. And something in use NOW. for the projects you have to deliver - NOW. "Paul Clement" wrote in message ... On Tue, 2 Nov 2004 09:20:09 +0100, "Thomas Tomiczek [MVP]" wrote: ¤ Hello, ¤ ¤ I am trying to auto sync db schemata (in access, among other databases). ¤ Means, there is a C# application that generates SQL for generating schema ¤ changes in the access database. ¤ ¤ I am stuck with two problems: ¤ ¤ ONE: how can I turn an int field into a counter/identity field? The ¤ following DDL statement: ¤ ¤ ALTER TABLE [ShpCountry] ¤ ALTER COLUMN [OID] COUNTER CONSTRAINT PK_OID PRIMARY KEY ¤ ¤ Produces the following error: OleDbException, Invalid field data type. (SQL ¤ State 3259). ¤ ¤ It does work creating a field of this type. adly we DO have some databases ¤ out where counter-fields were (erronously) generated as numeric fields, so I ¤ need to get this changes. ¤ ¤ TWO: how do I allow writing manual values into a counter field in SQL? We ¤ need to load "start data" into the database. I WOULD be ok with turning them ¤ back into a non-counter-field (i.e. numeric) to load the data, but then we ¤ end up again on problem ONE - turning them back INTO a counter. ¤ ¤ Anyone an idea how to handle this? I have all other stuff under control ¤ (index creation, drops, everything), just the "numeric to counter" gets on ¤ my nerves for weeks now. I don't believe that you can convert an Integer data type into an autoincrement (Counter) using DDL since by definition the values are automatically generated. Nor can you programmatically enter a value into one of these data types. Paul ~~~ Microsoft MVP (Visual Basic) |
#4
|
|||
|
|||
I /don't know/ about altering LongInt to Counter.
The OLD way to do it was to add a new field, and delete the old field. If you needed to preserve the data in the old field, you had to create a new table, append all the data from the old table to the new table, add DRI, remove DRI from the old table, and delete the old table. You can't do UPDATES to existing data in a counter field. You can't use the Access UI (datasheet or forms) to type data into a counter field. You /can/ use an insert query to insert into any counter field any data that matches it's basic type and is permitted by it's constraints. For example, you can enter 10 into a LongInt PK Counter field if there is not a previous value of 10. Counter fields are normally indexed with a unique constraint, but if you wish, you can have unindexed counter fields or allow duplicates. (david) Note: you can create a new table from the old one by use of "Select INTO". You may be able to alter counter field types on an empty table: otherwise you will have to add the field and delete the old one. "Paul Clement" wrote in message ... On Tue, 2 Nov 2004 09:20:09 +0100, "Thomas Tomiczek [MVP]" wrote: ¤ Hello, ¤ ¤ I am trying to auto sync db schemata (in access, among other databases). ¤ Means, there is a C# application that generates SQL for generating schema ¤ changes in the access database. ¤ ¤ I am stuck with two problems: ¤ ¤ ONE: how can I turn an int field into a counter/identity field? The ¤ following DDL statement: ¤ ¤ ALTER TABLE [ShpCountry] ¤ ALTER COLUMN [OID] COUNTER CONSTRAINT PK_OID PRIMARY KEY ¤ ¤ Produces the following error: OleDbException, Invalid field data type. (SQL ¤ State 3259). ¤ ¤ It does work creating a field of this type. adly we DO have some databases ¤ out where counter-fields were (erronously) generated as numeric fields, so I ¤ need to get this changes. ¤ ¤ TWO: how do I allow writing manual values into a counter field in SQL? We ¤ need to load "start data" into the database. I WOULD be ok with turning them ¤ back into a non-counter-field (i.e. numeric) to load the data, but then we ¤ end up again on problem ONE - turning them back INTO a counter. ¤ ¤ Anyone an idea how to handle this? I have all other stuff under control ¤ (index creation, drops, everything), just the "numeric to counter" gets on ¤ my nerves for weeks now. I don't believe that you can convert an Integer data type into an autoincrement (Counter) using DDL since by definition the values are automatically generated. Nor can you programmatically enter a value into one of these data types. Paul ~~~ Microsoft MVP (Visual Basic) |
#5
|
|||
|
|||
Ok, so we basically are down to the old "redo the table"-approach. This one
seriously (!) sucks. If the table is non-trivial, this is a severe significant amount of work for the database and blows up the db size. Bad news. THe insert is good news. Get me right on this - edit is not my intention. What I talk about is automatic db management. Means: create the db (from data stored in the application), then - load START data. Most databases have some sort of start dataset that you need to load into the system. Like a table of currencies loading all known currencies, or a user management system loading "default users" (then setting the passwords). Now, The OLD way to do it was to add a new field, and delete the old field. If you needed to preserve the data in the old field, you had to create a new table, append all the data from the old table to the new table, add DRI, remove DRI from the old table, and delete the old table. How do I do this without the possibility to RENAME a table? In SQL Server I dsometimes have to go to this approach too (certain changes are just not possible), but there I can rename a table, so I can move the old table out of the way. I use a sp for this, not "pure" sql, though. If I can not rename an existing table, this will end up with TWO copy operations: * Creete temp table. * Copy data to temp table. * Delete old table * Create new table * Copy data from temp table to new table. * Delete temp table. If the table has 50 or so Megabytes this is a LOT of copying going on. Sure there is no better way? I am willing, by now, to move to even DAO (through interop) if I can just make this better. Any lower lvel API that could be used? -- Regards Thomas Tomiczek THONA Software & Consulting Ltd. (Microsoft MVP C#/.NET) (CTO PowerNodes Ltd.) --- Still waiting for ObjectSpaces? Try the EntityBroker today - more versatile, more powerfull. And something in use NOW. for the projects you have to deliver - NOW. "david epsom dot com dot au" david@epsomdotcomdotau wrote in message ... I /don't know/ about altering LongInt to Counter. The OLD way to do it was to add a new field, and delete the old field. If you needed to preserve the data in the old field, you had to create a new table, append all the data from the old table to the new table, add DRI, remove DRI from the old table, and delete the old table. You can't do UPDATES to existing data in a counter field. You can't use the Access UI (datasheet or forms) to type data into a counter field. You /can/ use an insert query to insert into any counter field any data that matches it's basic type and is permitted by it's constraints. For example, you can enter 10 into a LongInt PK Counter field if there is not a previous value of 10. Counter fields are normally indexed with a unique constraint, but if you wish, you can have unindexed counter fields or allow duplicates. (david) Note: you can create a new table from the old one by use of "Select INTO". You may be able to alter counter field types on an empty table: otherwise you will have to add the field and delete the old one. "Paul Clement" wrote in message ... On Tue, 2 Nov 2004 09:20:09 +0100, "Thomas Tomiczek [MVP]" wrote: ¤ Hello, ¤ ¤ I am trying to auto sync db schemata (in access, among other databases). ¤ Means, there is a C# application that generates SQL for generating schema ¤ changes in the access database. ¤ ¤ I am stuck with two problems: ¤ ¤ ONE: how can I turn an int field into a counter/identity field? The ¤ following DDL statement: ¤ ¤ ALTER TABLE [ShpCountry] ¤ ALTER COLUMN [OID] COUNTER CONSTRAINT PK_OID PRIMARY KEY ¤ ¤ Produces the following error: OleDbException, Invalid field data type. (SQL ¤ State 3259). ¤ ¤ It does work creating a field of this type. adly we DO have some databases ¤ out where counter-fields were (erronously) generated as numeric fields, so I ¤ need to get this changes. ¤ ¤ TWO: how do I allow writing manual values into a counter field in SQL? We ¤ need to load "start data" into the database. I WOULD be ok with turning them ¤ back into a non-counter-field (i.e. numeric) to load the data, but then we ¤ end up again on problem ONE - turning them back INTO a counter. ¤ ¤ Anyone an idea how to handle this? I have all other stuff under control ¤ (index creation, drops, everything), just the "numeric to counter" gets on ¤ my nerves for weeks now. I don't believe that you can convert an Integer data type into an autoincrement (Counter) using DDL since by definition the values are automatically generated. Nor can you programmatically enter a value into one of these data types. Paul ~~~ Microsoft MVP (Visual Basic) |
#6
|
|||
|
|||
Hi,
Although the limitation for the Integer-AutoNumber still applies to them, I would suggest our tools to compare and synchronize structure and data changes in MSAccess : http://www.kliksoft.com/ Klik! CompareLib and Klik! DataCompareLib Regards, Özden Irmak Klik! Software "Thomas Tomiczek [MVP]" wrote in message ... Hello, I am trying to auto sync db schemata (in access, among other databases). Means, there is a C# application that generates SQL for generating schema changes in the access database. I am stuck with two problems: ONE: how can I turn an int field into a counter/identity field? The following DDL statement: ALTER TABLE [ShpCountry] ALTER COLUMN [OID] COUNTER CONSTRAINT PK_OID PRIMARY KEY Produces the following error: OleDbException, Invalid field data type. (SQL State 3259). It does work creating a field of this type. adly we DO have some databases out where counter-fields were (erronously) generated as numeric fields, so I need to get this changes. TWO: how do I allow writing manual values into a counter field in SQL? We need to load "start data" into the database. I WOULD be ok with turning them back into a non-counter-field (i.e. numeric) to load the data, but then we end up again on problem ONE - turning them back INTO a counter. Anyone an idea how to handle this? I have all other stuff under control (index creation, drops, everything), just the "numeric to counter" gets on my nerves for weeks now. -- Regards Thomas Tomiczek THONA Software & Consulting Ltd. (Microsoft MVP C#/.NET) (CTO PowerNodes Ltd.) --- Still waiting for ObjectSpaces? Try the EntityBroker today - more versatile, more powerfull. And something in use NOW. for the projects you have to deliver - NOW. |
#7
|
|||
|
|||
On Tue, 2 Nov 2004 22:31:53 +0100, "Thomas Tomiczek [MVP]" wrote:
¤ This would be terribly bad news - it would basically mean we have to drop ¤ support for access for more than the most trivial operations. Sorry I don't have a solution for you. Some data types have certain constraints that simply don't allow you change to another data type, especially when they contain existing data which may be affected by the change. Paul ~~~ Microsoft MVP (Visual Basic) |
#8
|
|||
|
|||
How do I do this without the possibility to RENAME a table?
Two options: use DAO to rename the table, or create a view using the old table name after deleting the old table. (renaming a table breaks relationships) seriously (!) sucks. If the table is non-trivial, this is a significant amount of work for the database and blows up the If the table has a lot of relationships to other tables, it is not trivial. A make table query won't re-create the relationships. I -personally- would not use autonumber fields where anything like this is contemplated. My belief is that numeric indexes will always bite you sooner or later: sooner, if you have to build your own index generator; later, if you have to deal with autonumber fields. So--- I use autonumber fields if the upfront saving is greater than the risk of later problems. I use code number generators if the risk of later problems is greater than the upfront savings. (david) "Thomas Tomiczek [MVP]" wrote in message ... Ok, so we basically are down to the old "redo the table"-approach. This one seriously (!) sucks. If the table is non-trivial, this is a severe significant amount of work for the database and blows up the db size. Bad news. THe insert is good news. Get me right on this - edit is not my intention. What I talk about is automatic db management. Means: create the db (from data stored in the application), then - load START data. Most databases have some sort of start dataset that you need to load into the system. Like a table of currencies loading all known currencies, or a user management system loading "default users" (then setting the passwords). Now, The OLD way to do it was to add a new field, and delete the old field. If you needed to preserve the data in the old field, you had to create a new table, append all the data from the old table to the new table, add DRI, remove DRI from the old table, and delete the old table. How do I do this without the possibility to RENAME a table? In SQL Server I dsometimes have to go to this approach too (certain changes are just not possible), but there I can rename a table, so I can move the old table out of the way. I use a sp for this, not "pure" sql, though. If I can not rename an existing table, this will end up with TWO copy operations: * Creete temp table. * Copy data to temp table. * Delete old table * Create new table * Copy data from temp table to new table. * Delete temp table. If the table has 50 or so Megabytes this is a LOT of copying going on. Sure there is no better way? I am willing, by now, to move to even DAO (through interop) if I can just make this better. Any lower lvel API that could be used? -- Regards Thomas Tomiczek THONA Software & Consulting Ltd. (Microsoft MVP C#/.NET) (CTO PowerNodes Ltd.) --- Still waiting for ObjectSpaces? Try the EntityBroker today - more versatile, more powerfull. And something in use NOW. for the projects you have to deliver - NOW. "david epsom dot com dot au" david@epsomdotcomdotau wrote in message ... I /don't know/ about altering LongInt to Counter. The OLD way to do it was to add a new field, and delete the old field. If you needed to preserve the data in the old field, you had to create a new table, append all the data from the old table to the new table, add DRI, remove DRI from the old table, and delete the old table. You can't do UPDATES to existing data in a counter field. You can't use the Access UI (datasheet or forms) to type data into a counter field. You /can/ use an insert query to insert into any counter field any data that matches it's basic type and is permitted by it's constraints. For example, you can enter 10 into a LongInt PK Counter field if there is not a previous value of 10. Counter fields are normally indexed with a unique constraint, but if you wish, you can have unindexed counter fields or allow duplicates. (david) Note: you can create a new table from the old one by use of "Select INTO". You may be able to alter counter field types on an empty table: otherwise you will have to add the field and delete the old one. "Paul Clement" wrote in message ... On Tue, 2 Nov 2004 09:20:09 +0100, "Thomas Tomiczek [MVP]" wrote: ¤ Hello, ¤ ¤ I am trying to auto sync db schemata (in access, among other databases). ¤ Means, there is a C# application that generates SQL for generating schema ¤ changes in the access database. ¤ ¤ I am stuck with two problems: ¤ ¤ ONE: how can I turn an int field into a counter/identity field? The ¤ following DDL statement: ¤ ¤ ALTER TABLE [ShpCountry] ¤ ALTER COLUMN [OID] COUNTER CONSTRAINT PK_OID PRIMARY KEY ¤ ¤ Produces the following error: OleDbException, Invalid field data type. (SQL ¤ State 3259). ¤ ¤ It does work creating a field of this type. adly we DO have some databases ¤ out where counter-fields were (erronously) generated as numeric fields, so I ¤ need to get this changes. ¤ ¤ TWO: how do I allow writing manual values into a counter field in SQL? We ¤ need to load "start data" into the database. I WOULD be ok with turning them ¤ back into a non-counter-field (i.e. numeric) to load the data, but then we ¤ end up again on problem ONE - turning them back INTO a counter. ¤ ¤ Anyone an idea how to handle this? I have all other stuff under control ¤ (index creation, drops, everything), just the "numeric to counter" gets on ¤ my nerves for weeks now. I don't believe that you can convert an Integer data type into an autoincrement (Counter) using DDL since by definition the values are automatically generated. Nor can you programmatically enter a value into one of these data types. Paul ~~~ Microsoft MVP (Visual Basic) |
#9
|
|||
|
|||
"david epsom dot com dot au" david@epsomdotcomdotau wrote in message
... How do I do this without the possibility to RENAME a table? Two options: use DAO to rename the table, or create a view using the old table name after deleting the old table. (renaming a table breaks relationships) I will live with two copies for now. seriously (!) sucks. If the table is non-trivial, this is a significant amount of work for the database and blows up the If the table has a lot of relationships to other tables, it is not trivial. A make table query won't re-create the relationships. Total non-issue as the code to tear down and recreate the relationships is already there. It WAS trivial, thanks to the manipulation framework already in place. Both methods are less than one screen full of text. I -personally- would not use autonumber fields where anything like this is contemplated. My belief is that numeric indexes will always bite you sooner or later: sooner, if you have to build your own index generator; later, if you have to deal with autonumber fields. So--- I use autonumber fields if the upfront saving is greater than the risk of later problems. I use code number generators if the risk of later problems is greater than the upfront savings. Personally I do not care what you - or I - personally would like to do. This is for a tool that allows this use, too, and so it just HAS TO WORK. If some dude outside wants counter-fields, he gets counter-fields, and I am not the person to stop him from doing so. I fully agree in terms of "it is wise", but - it is not my decision. We provide developer tools. -- Regards Thomas Tomiczek THONA Software & Consulting Ltd. (Microsoft MVP C#/.NET) (CTO PowerNodes Ltd.) --- Still waiting for ObjectSpaces? Try the EntityBroker today - more versatile, more powerfull. And something in use NOW. for the projects you have to deliver - NOW. |
#10
|
|||
|
|||
Your tools have the tremenouds problem of:
* Being unable to work against an abstract schema. * Being unable to work agaisnt other databases. making them totally unsuitable for the task at hand. The new database exists as schema (a graph of schema objects), and the tool has to work against all databases we care for. -- Regards Thomas Tomiczek THONA Software & Consulting Ltd. (Microsoft MVP C#/.NET) (CTO PowerNodes Ltd.) --- Still waiting for ObjectSpaces? Try the EntityBroker today - more versatile, more powerfull. And something in use NOW. for the projects you have to deliver - NOW. "Özden Irmak" ozdenirmak(at)isnet(dot)net(dot)tr wrote in message ... Hi, Although the limitation for the Integer-AutoNumber still applies to them, I would suggest our tools to compare and synchronize structure and data changes in MSAccess : http://www.kliksoft.com/ Klik! CompareLib and Klik! DataCompareLib Regards, Özden Irmak Klik! Software "Thomas Tomiczek [MVP]" wrote in message ... Hello, I am trying to auto sync db schemata (in access, among other databases). Means, there is a C# application that generates SQL for generating schema changes in the access database. I am stuck with two problems: ONE: how can I turn an int field into a counter/identity field? The following DDL statement: ALTER TABLE [ShpCountry] ALTER COLUMN [OID] COUNTER CONSTRAINT PK_OID PRIMARY KEY Produces the following error: OleDbException, Invalid field data type. (SQL State 3259). It does work creating a field of this type. adly we DO have some databases out where counter-fields were (erronously) generated as numeric fields, so I need to get this changes. TWO: how do I allow writing manual values into a counter field in SQL? We need to load "start data" into the database. I WOULD be ok with turning them back into a non-counter-field (i.e. numeric) to load the data, but then we end up again on problem ONE - turning them back INTO a counter. Anyone an idea how to handle this? I have all other stuff under control (index creation, drops, everything), just the "numeric to counter" gets on my nerves for weeks now. -- Regards Thomas Tomiczek THONA Software & Consulting Ltd. (Microsoft MVP C#/.NET) (CTO PowerNodes Ltd.) --- Still waiting for ObjectSpaces? Try the EntityBroker today - more versatile, more powerfull. And something in use NOW. for the projects you have to deliver - NOW. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Generating reports from two or more Access tables | wheelz | Setting Up & Running Reports | 3 | November 4th, 2004 09:55 PM |
Help, i'm gettiing error: Access has encountered a problem and needs to close... | betsy | General Discussion | 0 | September 14th, 2004 08:20 PM |
Problem with export report in Excel - Problem Microsoft Access | Lucio | Setting Up & Running Reports | 0 | September 12th, 2004 05:57 PM |
Importing Excel spreadsheet into Access - data type problem | Andrew Good | General Discussion | 2 | August 27th, 2004 09:39 AM |
Calculating fields for Access | Galin | New Users | 6 | May 5th, 2004 12:05 AM |