Import Table Structure from Excel Data Dictionary

[Guest]

I used Excel to define a data dictionary for a database design. It's a
complex design with more than 50 tables (extensive data about all Special
Districts in California). Each table's fields in the design are defined on
separate worksheets. Each worksheet has these columns:

* Field Name
* Key?
* Description
* Data Type
* Data Domain
* Size
* Format
* Lookup? If so, from what Lookup Table?
* Required?
* Notes

Each field's values for each of these columns is defined as rows.

It would be very cool to be able to use this to in some way import the table
structure and field definitions into Access - save a LOT of time.

Does anyone know if this can be done?

Also - for those who design complex databases like this, what "tool" do you
use to define the data dictionary and table structures, and are you able to
more or less automatically create your tables from that structure without
having to do so by hand?

Thanks

 

[Jeff Boyce]

John

I think you'll find that you can use Access to create a data structure, and
either the Documenter to print out the dictionary, or possibly even export
to MS Visio for documentation.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thanks Jeff - just to make sure I understand.

During the database design phase are you suggesting to use Access itself to
create a data model or dictionary - which would almost certainly go through
several iterations?

I can think of 2 ways this might happen: 1) go ahead and create the actual
tables - with table names, fields, attributes, etc., or 2) have a table
titled (something like) "data_dictionary", that would have a composite
Primary Key (maybe) like 1st field: "Table"; 2nd field: auto-increment
number, then the other attributes I described as fields.

Is that the direction you're thinking of?

I assume you Access MVP folks have a method to develop a database design,
and an efficient way of creating the database once the design is agreed on?

Thanks - John D

 

[Jamie Collins]

I used Excel to define a data dictionary for a database design. It's a
complex design with more than 50 tables (extensive data about all Special
Districts in California). Each table's fields in the design are defined on
separate worksheets. Each worksheet has these columns:

* Field Name
* Key?
* Description
* Data Type
* Data Domain
* Size
* Format
* Lookup? If so, from what Lookup Table?
* Required?
* Notes

Each field's values for each of these columns is defined as rows.

It would be very cool to be able to use this to in some way import the table
structure and field definitions into Access - save a LOT of time.

Does anyone know if this can be done?

You could probably generate a basic SQL DDL ('CREATE TABLE...') script
using 'Field Name', 'Data Type', 'Size' and 'Required?' Use VBA to loop
through the worksheets, column headers of known cell address, etc.

'Data Domain' you could incorporate into the SQL DDL script is you were
to implement the validation rules as CHECK constraints.

'Lookup?' may be used to generate a SQL DDL for FOREIGN KEYs (e.g.
ALTER TABLE...ADD CONSTRAINT...REFERENCES...) However, I'm not sure you
will have enough info e.g. if the keys are compound, the name of the
constraint, the update/delete actions (CASCADE, SET NULL, NO ACTION),
etc. Ditto for your 'Key?' i.e. PRIMARY or UNIQUE, name, compound, etc.


'Format' is for the front end, of course! 'Description' and 'Notes'
belong in the data dictionary.

Jamie.

--

 

[Jeff Boyce]

I'm only offering what I do. You may want to ask again, in a new thread, to
get more folks' ideas.

I found that the time it took me to think out (paper/pencil) a design, even
a preliminary structure, then define it better, then ... this process
slowed me down.

If by "efficient" you mean getting on with it, I found that I could create a
table structure (entities, attributes, relationships) in Access fairly
quickly, then step back and refine it.

Adding in a layer of paper, or a layer of "db definition table" slowed me
down.

But that is JOPO - just one person's opinion...

Good luck!

Jeff Boyce
Microsoft Office/Access MVP

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), are two small Access database
samples called "SQLLoader.mdb" and "SQLDAOloader.mdb" which illustrates how
to do this. These samples use either a text file or information stored in a
table to create the SQL Create Table statements, but the process could be
adapted to your needs, I'm sure.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L