Update Field Structures using ADO

[Lou Civitella]

I was wondering how I can go about using ADO and update the field properties
in one table based upon the field properties in the another.

Can this be done?

The tables will have the same field name but in some cases the properties in
some of the fields may change and I would like to reflect this in the other
table programmatically.

 

[Graham R Seach]

Lou,

You're question is pretty vague. Try to be a tad more specific!

Which properties? What are you really trying to do - and why?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[Lou Civitella]

I have a database program that I created and installed in several different
client locations. While the clients are using the program I am working on
updates. I have made changes to the back-end data files (add fields, changed
data types, etc). I am trying to work on a program that allows me to send to
my clients and have them run it so it would update their data files based on
the changes that I made in my data files. Pretty much what I would do is
send them a copy of my data files and the program would then loop through
all the tables, fields and properties of my data files and compare that to
the clients data files. What ever is missing the program would bring in and
whatever has changed the program will change.

I would like to be able to change all the properties if possible.
Does this sound feasible or is there a better way?

I hope this makes sense and is good information for you.

Thanks Graham for responding. I look forward to your next response.

thanks again,
Lou

 

[Graham R Seach]

Lou,

That's what I was afraid of! The short answer is - yes, it can be done.

The long answer begins by saying, I don't have code to do it, and as far as
I know, no-one else has either! What is usually done in cases like this, is
that the developer will apply versions to every database, and then write
service pack or upgrade software that will make specific changes depending
on (a) the client version, and (b) the new version. Determining the current
schema and modifying another schema to suit is not as easy as you think.

In any case, you will need to use a combination of Data Definition Language
(DDL) and DAO or ADO Extensions (ADOX).

What I suggest you do, is one of the following:

(a) Determine which versions you have out there, and figure out a way to
identify them. Then write code to make specific changes based on version.

(b) Deploy and install the new version, and import the existing data into
it.

We can help you whichever way you choose to move, but you'll need to tell us
specifically what you want to do. There's too much for us to show you
everything!

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[Lou Civitella]

Hi Graham,

Thanks for all your help so far and for your continued help.
I understand point (b) but I would like to know a little more about point
(a).

If I knew what versions the clients had, how can I do I go about creating a
service pack or upgrade software for the new version?

Thanks Again,
Lou

 

[Steve M.]

Regarding (a) identifying current version...

Unless you have been building the version number of your app into the application you will need to determine a characteristic that is unique to each version. Perhaps a new form, report, table, of anything you can think of that changed.

I put the version number in the title of my apps so it is easy to pull.
Looking at the Title in Startup you might see ...
IS Change Management - 20040730
where 20040730 is the version.

You can access this property with ...
strVer = CurrentDb.Properties("AppTitle")

and parse the version number with ...
strVer = Right$(strVer, Len(strVer) - (InStr(strVer, "-") + 1))

Either way you now know what version your client has and apply the schema change(s) as necessary.

I write all of my schema changes as I do developemnt, meaning that I never change an existing data structure manually because it can be difficult which combination of DAO, ADOX and SQL DDL to execute to get the exact results I am looking for.

Like Graham posted ... a generic tool to handle all properties of all data structures is a substantial undertaking and writing code to make specific changes is generally the approach taken.

I have found that DAO is better suited for making structure changes than SQL DDL or ADOX.

-- Steve [MCSD]