Access & OleDb - generating schema changes, problem with identity/counter fields.

[Thomas Tomiczek [MVP]]

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.)

 

[Paul Clement]

¤ 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 ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Thomas Tomiczek [MVP]]

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.)

 

[david epsom dot com dot au]

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.

¤ 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

 

[Thomas Tomiczek [MVP]]

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.

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.

¤ 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 ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Özden Irmak]

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

 

[Paul Clement]

¤ 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 ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[david epsom dot com dot au]

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)

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.

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.

¤ 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 ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Thomas Tomiczek [MVP]]

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.

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.)

 

[Thomas Tomiczek [MVP]]

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.

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

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.