By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,175 Members | 1,710 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,175 IT Pros & Developers. It's quick & easy.

Automating user changes to database

P: n/a
This post is for senior developers.

I'm an Access contractor. This morning I see a listing for a position
that seems to require development of a self-modifying database. The
application may need to provide an interface for the user to add
fields and possibly tables on the fly, with automated form and report
modification and generation.

I have never done this. While I have worked on projects where this
might have been useful, it has seemed to me that it would be difficult
to automatically generate forms/reports that are good looking and
functionally complete.

I have seen posts that suggest that others have managed to do this. If
anyone has success stories, advice on how to skill up, book
suggestions, and advice on estimating what's doable, I would appreciate
it.

Thanks. I realize this is not a very specific request, but any
responses would be appreciated.

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a

Bruce,

The difficulty depends on how tightly bound the 'custom' fields are
expected to the 'standard' data. If it is acceptable to have the custom

fields treated separately from the standard fields, then I would be
tempted to keep all the custom attributes in a one table, and all the
custom attribute definitions in another, with several support tables.

Sample table layouts:

tblAttributeTypes
-----------------
AttributeType Long ' Primary; List of VBA VarType values
AttrTypeName Text(32)
AttrTypeFormat Text(255) ' Format string for Format() function
AttrTypeNum Boolean ' True=Numeric; False=Text

tblAttrTables
------------------
AttrTableID Autonumber ' Primary
AttrTableName Text(255)

tblAttributeDefs
----------------
AttributeDefID Autonumber ' Primary
AttrTableID Long ' Foriegn Key to tblAttrTables
AttributeType Long ' Foriegn Key to tblAttributeTypes
AttrDefName Text(32) ' Foriegn Key to tblTables
AttrDefFormat Text(255) ' Format string for Format() function
AttrDefActive Boolean ' True=Definition active

tblAttributes
-------------
AttributeID Autonumber ' Primary
AttrRecordKey Long ' Foriegn key to records
AttributeDefID Long ' Foriegn key to tblAttributeDefs
AttrDisplayText Text(255) ' Formatted with AttrDefFormat
AttrValueNum Decimal ' Holds numeric values
AttrValueText Text(255) ' Holds Text values

tblAttrRecordkey
----------------
AttrRecordKey Long ' Single-record global counter

Some explanation: this table layout lets you define a list of
attributes for any attributable table. The user can then add any number
of table-specific attributes for a record in that table. You can define
the default format for the data.

Attributes are typed like variants. Since Access can't store a variant
in a single field, two fields are required: one for text values, and
the other for numeric (boolean, integer, long, floats, date...). This
allows users to define any type of data for their tables.

One special field is AttrRecordKey. This field is tied to a counter
table which is read and incremented for each record that the user adds
custom attributes to, regardless of table. This key is unique across
all records in all tables, not just for a single table, like an
autonumber. This allows a single table, tblAttributes, to be keyed to
any record in the database.

The benefit of this approach is simplicity: you have a single form to
edit/add attribute definitions for any table; a single subform to
display/add/edit attributes for any record; and a single subreport to
print them.

This avoids supporting self-modifying changes to existing forms,
reports, queries and tables. The Variant Attribute Table approach is
far preferable to any self-modifying scheme, which is bound to be
complex and unreliable.

-Ken

Nov 13 '05 #2

P: n/a
Thanks, Ken. This looks like an excellent approach.

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.