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

Guillem skrev:

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.

Thanks, which lead me to this nice guide, which I will try after finalising this mail.

http://delphi.about.com/od/database/l/aa072401a.htm

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

Having ADOX in place, I guess compacting and repairing the DB is also possible so I guess the risk is smaller than it could be. My only concern would be that this is the first step to actually locking myself to Access/JET rather than the previous approach where I could have any back-end DB...

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.

We're talking going 2.3 to 2.4 - I would agree in the scenario going to 3.0, i.e. a major release but for minor ones the dead fields are a very small nuance.

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.

OK, Still can't understand the need to drop a method anytime as most would be handy for backward compatobility from here to kingdom come anyway ... Well, what do I know ;-)

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/

Nope - if I do, that's when the error occurs. If the field defs in the TADODataSet are not matching the disk file, then you can't make it active. (please correct me if I'm wrong here).

Second problem;

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

I'll be most happy to forget SQL ASAP. Select * from table is basically as far as I care to go if I get options ;-)

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

Again, launching V3.0 I will do this - I promise!

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

Hrm.. Correct. I need iterating to find all of them - thanks for noticing!

/Pontus
.

Relevant Pages

  • 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: Synching between Access and MSSQL
    ... The clients will only be disconnected a matter of a few days in most cases ... The challenge is that the information has to be tracked in a main database ... Working out the details of how to keep the data synchronized and accomodate ... I found code to allow an ADO connection to a local Access database but now I ...
    (microsoft.public.access.externaldata)
  • Re: Postgres PL/Python
    ... > I wonder if anyone on this list is using Python as Postgres ... It sounds like I have the whole Python ... code between database and clients when the clients are written in Python ...
    (comp.lang.python)
  • Re: Delphi6. ADO <->SQLExpress - best practice?
    ... Unlike BDE and Paradox, dont use tADOTable .. ... Use tADODataset to read only the data needed for the ... can then be committed to the database "optimistically", ... helpful when you have multiple reords being created and dont want to commit ...
    (borland.public.delphi.database.ado)