Copying Fields using Code

[John]

I am trying to edit a tables' structure using VBA. I can
create a basic field using .CreateField()
and .Fields.Append.

I want to create a field in one table that exists in
another table (different DB). I wish to maintain the
lookup RowSource from the source table. This doesn't seem
to be something I can create on-the-fly.

I have tried many methods to copy the field within
tabledef and cannot do it.

Does anyone have any ideas, am I wasting my time?

Thanks,

John

 

[Tim Ferguson]

I am trying to edit a tables' structure using VBA. I can
create a basic field using .CreateField()
and .Fields.Append.

(I always wonder why people think it's cool to alter a database's schema
programmatically -- IMO it suggests a design flaw.)

And in any case it is much simpler to use the ALTER TABLE command in SQL.

I want to create a field in one table that exists in
another table (different DB). I wish to maintain the
lookup RowSource from the source table. This doesn't seem
to be something I can create on-the-fly.

Just don't use the LookUp features of Access. If you are up to progamming a
schema, you certainly have to skills to create the thing properly and make
the joins in the query. Note that you cannot make a Relationship between
tables in separate mdb files.

Hope that helps


Tim F

 

[John Vinson]

Does anyone have any ideas, am I wasting my time?

In my opinion, ANY use of Microsoft's misdesigned, misleading, and all
but useless so-called Lookup feature is a waste of time. Table
datasheets should be used only for debugging; you can use a Form to
display data using combo boxes without ever using the Lookup feature.

One question - why all the MakeTables? I find that they are very
rarely really needed, given that you can create Queries and use them
as the recordsource for forms, reports, exports, new queries, etc.

 

[Bas Cost Budde]

(I always wonder why people think it's cool to alter a database's schema
programmatically -- IMO it suggests a design flaw.)

I do that as part of the design process. Only. I like to store the names
of the fields, tables, combination of them in my "system" tables, and to
create the structure from code.

I guess that is not a flaw ;-)

 

[Tim Ferguson]

I like to store the names
of the fields, tables, combination of them in my "system" tables, and to
create the structure from code.

I guess that is not a flaw ;-)

Perhaps: but I still don't understand why it's cool. We get so many
messages here asking about appending to field collections -- and remember
most of them never do anything useful like specifying foreign key or unique
constraints or default values and so on -- that I just want to scream at
them, "set the database up once in the GUI and then just use the effing
thing!"

My interpretation is that most requests of this nature are related to the
supposed need to create the SalesFigures2004 table as a copy of the
SalesFigures2003 one; and they think that spending six hours trying to get
the programming right is worth the fifteen seconds it takes to Copy and
Paste Structure Only in the database window. Quite aside from the fact that
this kind of planning reveals a level of db design ignorance so profound
that they ought to have their Access User's License revoked.

Sorry for giving this more importance than it probably warrants, but I get
genuinely perplexed by people who go out of their way to find a needless
thing to do, and then cast around for the hardest and least rewarding way
to do it.

All the best


Tim F

 

[TC]

(snip)

(I always wonder why people think it's cool to alter a database's schema
programmatically -- IMO it suggests a design flaw.)

I don't disagree with that at all, in most cases. But here is one legitimate
use.

Once I've released the first version of an application, I write code to do
all further changes to the BE schema. (Well, I might do some changes
manually first, but I will write the equivalent code eventually.) I then
include that code in the next production version of the FE. Whenever any
version of the FE is run, it automatically checks the schema version of the
linked-to BE. If the BE version is less than the version required for that
particular version of the FE, the FE automatically & seamlessly upgrades the
BE schema to the required new version!

That is, every version of the FE, contains the code required to upgrade any
earlier version of the BE, to the version required for that FE.

Voila: goodby code/data schema mismatches!

Cheers,
TC

 

[Tim Ferguson]

That is, every version of the FE contains the code required to
upgrade any earlier version of the BE, to the version required for
that FE.

.... in other words to correct the flaws in the previous version of the
BE..! <duck />

That, of course, is not neccessarily a Bad Thing -- databases change as
their needs alter. But then again, I am just not convinced that that is
what the posters are after.

Look: I know there are times when it's neccessary to alter a database
schema, and even to do it silently in code. But do you really believe in
most of the posts asking about it?

All the best


Tim F

 

[TC]

... in other words to correct the flaws in the previous version of the
BE..! <duck />

That, of course, is not neccessarily a Bad Thing -- databases change as
their needs alter. But then again, I am just not convinced that that is
what the posters are after.

Look: I know there are times when it's neccessary to alter a database
schema, and even to do it silently in code. But do you really believe in
most of the posts asking about it?

No, certainly not - as I said in my post! I'm not trying to argue that
point, at all. Just to give an example of where it is, indeed, a legitimate
need

Cheers,
TC

 

[John]

Thanks, for the replies.

I was attempting to remotely add the odd field, as the
schema changes a little over time, with as little
interaction as possible.

You answers have;

a) guided me with wisdom
b) helped me realise that I needn't bother making the
BE too clever in the first place (i.e. having combo's)

Thanks for your time guys!

John