Re: Programatically adding Fields to an existing Database (using SQL)

Pontus Berg wrote:

Howdy!

In my ambition to have a client that automatically updates the Access
database to new clients (where the clients assume additional fields),
have I previously asked for advice on how to best do this. It seems
the recommendation was to make this in Access itself, where Dominiq
was kind to share experience on how to do this. A converter that had
patterns per DB version release and migrating data to the new
structure (enabled renaming of filed as well addition and deletion of
fields).

I haven't used Access too much but IIRC ADOX can help you in this. See
the reply from Brian Bushay to Jhoe Logz in the thread "Remove required
fields in Access" on the 3rd October.

Still, be very careful about manipulating fields once in production.
Even adding new fields could make your database inconsistent or
unnormalized.

I make a few very basic
assumptions; no fields are ever deleted nor renamed; orphan fields
are left in there and the only point at hand would be adding new
fields.

I would reconsider the assumption about the orphan fields. Just leaving
them there is a waste of space and speed. Maybe at the beginning your
clients won't notice it, but as you leave more and more orphan fields
performance will decrease.


First; If I call the TADOConnection method "GetFieldNames" I get the
list that's on disk. No problems here! Secondly; If I call the
TADODataSet method "GetFieldNames" in order to find the fields the
client expects, I firstly get a compilation warning that the symbol
is "deprecated" (whatever that means - googling didn't enlighten me
either) but I get a returned list that is correct. So I guess this is
OK as well.

deprecated means that the function or procedure will be dropped in
future releases and should not be used for any new app. As a matter of
fact, it is kept just for legacy purposes.

In this case, it seems that GetFieldNames will not be used in the
future within the TADODataset. Sounds strange to me that it is so,
though.


First problem;

ADO_Progress.FieldByName(DBFieldsBox2.Items[Count1]).DataType

This piece of code is expected to return the datatype of the field
missing from disk. It doesn't! The fieldname by the reference is
correct, so but mind that the database is not active as I captured an
exception to initiate adding in the first place. Don't know if this
is relevant, but anyway. Why doesn't it where the GetFieldNames
worked?

have you tried with the database being active? is your OLE DB provider
the correct one? have you checked your connection string?

http://www.connectionstrings.com/



Second problem;

Finding the datatype and size, where applicable, is not too hard if
it's accessible. If the above command ("first problem") would work,
then the size is obtainable using:

ADO_Progress.FieldByName(DBFieldsBox2.Items[Count1]).DataSize

Now, I found SQL to be the only way to add the field on the fly but I
run into all sorts of problems as DataTypes are so different and
converting between them is a mess. Any suggestions here?

using SQL against a desktop database is usually not a good idea. It's
better to do it the "native" way. Can't really help here. Maybe the
ADOX can help you



Third problem:

To be honest I am not even sure it can be done. If possible I would
of course like not to be forced into adding the fields to the Field
Definitions, create a new database file and then migrate the data
from the old to the new.

Maybe it's not that bad of an idea. Doing it this way you can get
elegantly get rid of orphan fields



Please find enclosed an extract from my code in the end of this post,
where I have a subroutine to build an SQL command to add a field
using 'ALTER TABLE table_name ADD column_name datatype'.

<code snippet>

just as a comment, your code assumes you are adding only 1 new field
each time.

OTOH, as I wrote before, using SQL against a desktop database is not
that good an idea.

Just my 2 cents :)
--
Best regards :)

Guillem Vicens Meier
Dep. Informática Green Service S.A.
www.clubgreenoasis.com
--
Contribute to the Indy Docs project: http://docs.indyproject.org
--
In order to contact me remove the -nospam

.

Relevant Pages

  • Re: Access vs SQL
    ... > to SQL. ... Are you using the database primarily as a storage place, ... If you have many updates then you need to constantly compact it. ... No additional cost to your clients. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Supporting multiple oracle versions in a trigger
    ... This is a very unscalable solution. ... Why would you do that in a database? ... > - Clients can add/modify their data collection requirements in minutes ... > - Clients can connect to the database with 3rd party sql tools and access ...
    (comp.databases.oracle.server)
  • Re: Poly Couples
    ... Essentially - different versions for different clients. ... insist that you use their existing database which is from a different ... vendor, and the structure is different than the one you use originally. ... characters would mark variable insertion place-holders in the SQL. ...
    (comp.object)
  • Re: Programatically adding Fields to an existing Database (using SQL)
    ... database to new clients, ... TADODataSet method "GetFieldNames" in order to find the fields the ...
    (borland.public.delphi.database.ado)
  • Re: Synching between Access and MSSQL
    ... Look at SQL Server replication. ... How long will the clients be disconnected, i.e. max number of days without ... > SQL database and the clients would work offline with this data and later ...
    (microsoft.public.access.externaldata)