Can attributes of tables/relationships, etc. be changed programatically ?

[Ron]

Hi All,

Development stage of setting up a new solution here.

I CONSTANTLY have to unhook the relationships I've built via the
Relationships tab on Tools, then change the Required element of a few
controls on a table from yes to no, make some other adjustments to the
attributes of various controls, then copy just the structure (so that the
primary key resets to 1) to another name, delete the first table, rename the
new table to what it should be, import some data (from another system I'm
converting from), run some queries that find duplicates, missing data,
deletes some garbage, etc. I then have to redo the Required elements I
need, rehook the relationships and all the other stuff I had to cancel just
to get the data over. Now, don't get me wrong...I'm amazed I can even DO
all of this without coding anything. But...

The terrible part is, my boss is going to eventually want to take this
existing mdb and make one identical too it but with only certain of the
tables populated with some data and defaults and things, leaving the rest of
the tables empty (ready for new data), and make a new mdb with it. NO WAY
can I allow HIM to have access to all this stuff. Talk about screwing
things up...

Can I do any of this in code rather than manually through all these
wizards/screens, etc? And if I can do it by code, can anyone point me
towards some examples I can study?

Thanks,
ron

[Ron]

Sorry, forgot to mention: Access2000/WinXP

"Ron" <ro************ ********@verizo n.netwrote in message
news:_OnIh.124$ Bi2.109@trnddc0 1...

Hi All,

Development stage of setting up a new solution here.

I CONSTANTLY have to unhook the relationships I've built via the
Relationships tab on Tools, then change the Required element of a few
controls on a table from yes to no, make some other adjustments to the
attributes of various controls, then copy just the structure (so that the
primary key resets to 1) to another name, delete the first table, rename
the new table to what it should be, import some data (from another system
I'm converting from), run some queries that find duplicates, missing data,
deletes some garbage, etc. I then have to redo the Required elements I
need, rehook the relationships and all the other stuff I had to cancel
just to get the data over. Now, don't get me wrong...I'm amazed I can
even DO all of this without coding anything. But...

The terrible part is, my boss is going to eventually want to take this
existing mdb and make one identical too it but with only certain of the
tables populated with some data and defaults and things, leaving the rest
of the tables empty (ready for new data), and make a new mdb with it. NO
WAY can I allow HIM to have access to all this stuff. Talk about screwing
things up...

Can I do any of this in code rather than manually through all these
wizards/screens, etc? And if I can do it by code, can anyone point me
towards some examples I can study?

Thanks,
ron

[Allen Browne]

In general, you cannot merely modify the properties in code if the relation
exists. You can programmaticall y delete a relation, set properties,and
create the relation again, but it is rarely worth the trouble since it is a
one-off operation.

During the design phase, you might want to leave creating the relations
until you have created the bulk of the tables. That helps avoid having to
create and destroy relations constantly. (Of course, this assumes you do
have a complete written specification of what the database needs to do, so
that you can create all the tables and verify that every design aspect is
met, and then create the relations.)

If you want an example of looping through the Relations and deleting them,
see:
http://allenbrowne.com/DelRel.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ron" <ro************ ********@verizo n.netwrote in message
news:_OnIh.124$ Bi2.109@trnddc0 1...

Hi All,

Development stage of setting up a new solution here.

I CONSTANTLY have to unhook the relationships I've built via the
Relationships tab on Tools, then change the Required element of a few
controls on a table from yes to no, make some other adjustments to the
attributes of various controls, then copy just the structure (so that the
primary key resets to 1) to another name, delete the first table, rename
the new table to what it should be, import some data (from another system
I'm converting from), run some queries that find duplicates, missing data,
deletes some garbage, etc. I then have to redo the Required elements I
need, rehook the relationships and all the other stuff I had to cancel
just to get the data over. Now, don't get me wrong...I'm amazed I can
even DO all of this without coding anything. But...

The terrible part is, my boss is going to eventually want to take this
existing mdb and make one identical too it but with only certain of the
tables populated with some data and defaults and things, leaving the rest
of the tables empty (ready for new data), and make a new mdb with it. NO
WAY can I allow HIM to have access to all this stuff. Talk about screwing
things up...

Can I do any of this in code rather than manually through all these
wizards/screens, etc? And if I can do it by code, can anyone point me
towards some examples I can study?

Thanks,
ron

[Ron]

Second paragraph of my post suggests a reason I may not be doing this just
one time. I may have to give over a system of creating a partially blank
database (derived from a combination of existing tables/blank tables) so
they can begin record keeping for separate offices as they open.

But, anyway...

I went to your website and checked out some utilities you have. Thanks for
putting all that together for everyone. I did try the Relationship Report
with extended field info under Application/Utilities but it won't compile.
Yes, I have MS DAO 3.6 library referenced. Get an error message that says
it's a compile error: Method or data member not found and it sits on the "If
fld.Required then" part of DescribeFieldSu b. Works down thru all of them,
and then my report (if I run it uncompiled) has my data files shown, with
lines to all the related tables, but no control names, just "Unbound" for
all the control names. Anyone give me a heads up as to why?

ron

"Allen Browne" <Al*********@Se eSig.Invalidwro te in message
news:45******** **************@ per-qv1-newsreader-01.iinet.net.au ...

In general, you cannot merely modify the properties in code if the
relation exists. You can programmaticall y delete a relation, set
properties,and create the relation again, but it is rarely worth the
trouble since it is a one-off operation.

During the design phase, you might want to leave creating the relations
until you have created the bulk of the tables. That helps avoid having to
create and destroy relations constantly. (Of course, this assumes you do
have a complete written specification of what the database needs to do, so
that you can create all the tables and verify that every design aspect is
met, and then create the relations.)

If you want an example of looping through the Relations and deleting them,
see:
http://allenbrowne.com/DelRel.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ron" <ro************ ********@verizo n.netwrote in message
news:_OnIh.124$ Bi2.109@trnddc0 1...

>Hi All,

Development stage of setting up a new solution here.

I CONSTANTLY have to unhook the relationships I've built via the
Relationship s tab on Tools, then change the Required element of a few
controls on a table from yes to no, make some other adjustments to the
attributes of various controls, then copy just the structure (so that the
primary key resets to 1) to another name, delete the first table, rename
the new table to what it should be, import some data (from another system
I'm converting from), run some queries that find duplicates, missing
data, deletes some garbage, etc. I then have to redo the Required
elements I need, rehook the relationships and all the other stuff I had
to cancel just to get the data over. Now, don't get me wrong...I'm
amazed I can even DO all of this without coding anything. But...

The terrible part is, my boss is going to eventually want to take this
existing mdb and make one identical too it but with only certain of the
tables populated with some data and defaults and things, leaving the rest
of the tables empty (ready for new data), and make a new mdb with it. NO
WAY can I allow HIM to have access to all this stuff. Talk about
screwing things up...

Can I do any of this in code rather than manually through all these
wizards/screens, etc? And if I can do it by code, can anyone point me
towards some examples I can study?

Thanks,
ron

[Allen Browne]

Try changing the declaration line:
Private Function DescribeFieldSu b(tdf As TableDef, fld As Field, ...
to:
Private Function DescribeFieldSu b(tdf As TableDef, fld As DAO.Field, ...

That disambiguation should solve the problem if the issue is that you have
another library that exposes a Field object.

Let me know if that is the solution, and I will change the utility so no one
else gets caught with it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ron" <ro************ ********@verizo n.netwrote in message
news:T1WIh.234$ Eg4.199@trnddc0 3...
[snip]

>
I went to your website and checked out some utilities you have. Thanks
for putting all that together for everyone. I did try the Relationship
Report with extended field info under Application/Utilities but it won't
compile. Yes, I have MS DAO 3.6 library referenced. Get an error message
that says it's a compile error: Method or data member not found and it
sits on the "If fld.Required then" part of DescribeFieldSu b. Works down
thru all of them, and then my report (if I run it uncompiled) has my data
files shown, with lines to all the related tables, but no control names,
just "Unbound" for all the control names. Anyone give me a heads up as to
why?

ron

[Ron]

That did the trick. Amazing what 3 little letters and a dot will
accomplish, eh?

Thanks--that'll definitely make things easier to compare databases.
"Allen Browne" <Al*********@Se eSig.Invalidwro te in message
news:45******** **************@ per-qv1-newsreader-01.iinet.net.au ...

Try changing the declaration line:
Private Function DescribeFieldSu b(tdf As TableDef, fld As Field, ...
to:
Private Function DescribeFieldSu b(tdf As TableDef, fld As DAO.Field,
...

That disambiguation should solve the problem if the issue is that you have
another library that exposes a Field object.

Let me know if that is the solution, and I will change the utility so no
one else gets caught with it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ron" <ro************ ********@verizo n.netwrote in message
news:T1WIh.234$ Eg4.199@trnddc0 3...
[snip]

>>
I went to your website and checked out some utilities you have. Thanks
for putting all that together for everyone. I did try the Relationship
Report with extended field info under Application/Utilities but it won't
compile. Yes, I have MS DAO 3.6 library referenced. Get an error message
that says it's a compile error: Method or data member not found and it
sits on the "If fld.Required then" part of DescribeFieldSu b. Works down
thru all of them, and then my report (if I run it uncompiled) has my data
files shown, with lines to all the related tables, but no control names,
just "Unbound" for all the control names. Anyone give me a heads up as
to why?

ron