On Dec 17, 9:50*pm, Tony Toews <tto... (AT) telusplanet (DOT) net> wrote:
Hi Tony,

You are right. The meta data is in the tables. The rest is meta
information: the way how you organize the process flow.
Alas, I'm not so puristic in my English.

Imb.

On Dec 17, 9:50*pm, Tony Toews <tto... (AT) telusplanet (DOT) net> wrote:
Hi Tony,

After re-reading your post I concluded that you did not understand the
clue.
Sure, meta data is stored in tables, but putting one or more tables
with meta data in an application does not do anything. You need
software, and sometimes lots of software, in order to let your
database run, defined by the data in the meta data tables.

Imb.

Tony Toews <ttoews (AT) telusplanet (DOT) net> wrote in
news:bs6gg6pelk6bctkg4r43bei9gj8si4mp3v (AT) 4ax (DOT) com:

I would argue against it. I have a very old app that has different
record types, where different fields mean different things, and it's
been a disaster to maintain for a whole host of reasons. While I
think you're suggesting dedicated fields for each record type (as
opposed to re-using a single field in multiple record types that
would then mean something different for each record type) and that
avoids some of the problems I've encountered, it still presents a
lot of issues for user interface in Access.

This is a version of the supertype/subtype problem and after long
experience experimenting with different ways to to implement it,
I've decided it's better to just make different tables when there's
lots of non-overlapping attributes, and forego the benefit of having
everything in a single table.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

On Dec 17, 4:58*am, imb <im... (AT) onsmail (DOT) nl> wrote:

ibm :-),

I once tried using a table as a data map. Each field on the form was
of the type <ctl><FieldName> (e.g. cbxFirstName) for a field called
FirstName. The input could come from any table/field. The schema was
something like:

ID AutoNumber
ControlName Text
NewData Y/N
NewTable Text
NewDataType Text
FillFormExistingData Y/N
SubmitData Y/N
SourceDestinationTable Text
SourceDestinationField Text
SourceDestinationDataType Text

Each ControlName in the table would be read when opening, editing or
submitting data (note: this was for an unbound form). The table
controlled the default data to be used for new records, where the data
came from for old records, and where edited data would be saved. The
form interacted with about six tables at the same time, so the
flexibility of having a data map table was helpful. I have tried many
kinds of generalizations with Access, with varying degrees of success.

James A. Fortune
CDMAPoster (AT) FortuneJames (DOT) com

Hi James,

Nice to call me ibm, but alas, I am not a shareholder.

As far as I can recall, you are almost the first who reports on his
generalization efforts, though my feelings are that everyone who is
more or less experienced in Access heads for that direction. Is there
a taboo or is it a matter of “intellectual property”? Or simply too
difficult?

I tried to understand the model that you build, but it is hard to
understand it in depth. There is a couple of similarities, there is a
couple of differences.
The most important difference is that I use bound forms, that means
that I do not need to distinguish between ControlName and
SourceDestinationField.
I use a DataType, but do not differentiate to NewDataType and
SourceDestinationDataType. This DataType is a little more subtle than
the Access datatype.
This table - as the CollectionOfControls - contains also all other
information that I need to define this control in its specific
context, such as dimensions, edit and delete authorization, default
sorting, duplicate record checks, actions after modification, and
more.

I have four important forms: one for adding new records, one for
inspecting/editing single records, one for Overviews (continuous form)
and one form as a “Selection form”. This latter form is a special case
of an Overview form, and replaces all Comboboxes anywhere in the
application.

Your most interesting remark was, that you tried many kinds of
generalization, with varying degrees of success. I like to hear those
attempts that did not result in the wished success, because from these
attempts you can learn the most.

Imb.

On Dec 20, 3:50*pm, imb <im... (AT) onsmail (DOT) nl> wrote:

Access certainly creates an environment that facilitates
experimentation. There has to be a critical mass before
generalization is justified. For some, day to day pressures don't
allow them to set up a properly abstracted database for a given task,
even if they know that's what they should do. For some things, it
takes a substantial familiarity with Access even to know what's
possible. Code that automatically writes code has been largely
abandoned based on painful lessons shared in the Access newsgroups.
But when I determine that a more abstract way of programming in Access
will provide benefit and implement it, I am happy to share the fruits
of that labor with others, along with warning signs for the gotchas.

The basic idea of using metadata is sound. The idea about many
elegant solutions implementing another level of abstraction is fine as
long as the cost of implementing that level of abstraction doesn't get
too high.

A few come to mind immediately:

Flexible Reports:
http://groups.google.com/group/comp....f9ed5253e232c1

The object of the flexible report was to be able to select up to ten
fields from a table to include on a report and have the report code
automatically size the data to make use of the available space on the
form. I had some properly normalized tables with over 70 fields each
(the data was related to aerospace manufacturing). If the maximum
data length got too large, it would shift to a different report to
show the data, first to landscape, and then to legal, if necessary.
The users really liked having that much flexibility, but the setup was
lengthy and it was not particularly easy to add another new field
possibility to the reports. After performing a search, a new form
with all the table fields with checkboxes by each one allowed the user
to add or delete fields shown in a listbox. The previous list came up
as the default and users could also save or retrieve a given list of
table fields. The report would include only those fields with only
the records that conformed to the search results.

Creating form from code:
http://groups.google.com/group/micro...d179f25848911f

I was trying to create search forms automatically from Access tables.
This had a lot of promise because users often want such a useful form
created and they really love using them. The code had two main
problems. The first was that it was "blue-collar" code that wasn't as
elegant as what some others started to post about their initial
efforts into doing something similar. The second was that given
enough fields, simply putting each new control on the form vertically
reached the 22" limit on the form height. It's not that hard to add
logic to handle that, but I never got around to dealing with it.
Also, there was the problem of putting in, via VBA, all the correct
VBA code to dynamically generate the SQL required for the search
behind the command button. That seemed straightforward, but time
consuming. The bottom line was that the search forms I had already
created kept getting more features and there wasn't enough demand to
create totally new search forms to justify finishing that effort.

Is it advantageous to use .NET Framework DLL's in Access?
http://groups.google.com/group/comp....476f5e582e43fb

There's a host of .NET enabled additions to Access that I would've
liked to investigate. With Access being cut back where I work I have
little justification to make those investigations. The new owners
still give me fairly wide latitude and rely on my discretion when it
comes to investigating new technology, but I try to keep my forays
focused. Maybe it would help to give out the CEO's email address and
ask people who have benefited from my posts to send a thank-you
note :-). I am spending a significant amount of time getting up to
speed with C# and WPF, since those are the most likely avenues, if
any, for modifications to SAP. I have read through many books about
either C#, WPF, Concurrent Programming, .NET or LINQ during the past
year. I'm not sure how much information will make it back into
creating enhancements to Access. I like what I've been able to do
with Access so far, but I also need to develop at least similar
capabilities with other tools. I have a clearer picture right now
about what technologies I will need to use later. That is helping me
focus my efforts.

James A. Fortune
CDMAPoster (AT) FortuneJames (DOT) com

The art of simplicity is a puzzle of complexity. -- Douglas Horton

On Fri, 17 Dec 2010 13:26:31 -0800 (PST), imb <imb4u (AT) onsmail (DOT) nl>
wrote:

Yup. I was thinking later that my reply was quite wrong in that, as
you point out, I hadn't thought things through completely.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

re: I'd sure be tempted to have lots of empty fields in the Item table.

oh.. great.. it's obvious that you don't understand anything about normalization!