Designing Access Table in Excel >> Copying to Access Desgign

[Brahio]

Hello,

I have a large database I am palnning designing in Excel (Thousands of
fields, etc). For each table I have all the fields, data types and field
names in excel just as they appear in the access table design view.
Question, does anyone know how I can copy, move or import all the fiels per
table from excel to access desgn view. Is it even possible?

(Creating fields and names in Excel to take advantage to concatenation in
naming complicated field names and field descriptions for thousands of fields)

 

[Jeff Boyce]

Brahio

If your design has "thousands of fields", there might be a chance that your
intended table structure would benefit from additional normalization. If
you'll describe the kind of data that goes into those "thousands of fields",
folks here may be able to offer additional assistance.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

Hello,

I have a large database I am palnning designing in Excel (Thousands of
fields, etc). For each table I have all the fields, data types and field
names in excel just as they appear in the access table design view.
Question, does anyone know how I can copy, move or import all the fiels per
table from excel to access desgn view. Is it even possible?

(Creating fields and names in Excel to take advantage to concatenation in
naming complicated field names and field descriptions for thousands of fields)

Since Access tables are limited to 255 fields, and most rational normalized
table designs have fewer than 30 fields... it sounds like you're REALLY on the
wrong track! Or so you mean "thousands of fields" spanning scores of (properly
normalized) tables?

You can use File... Get External Data... Import to import a "table"
(spreadsheet) from Excel into a new table in Access.

 

[Brahio]

Thanks John,

You're correct, I have already normalized my tables (about 25-30) for the
1000 fields. I'm not trying to import data yet, but trying to find a way to
import the design properties for each table (field/data type/field
description). I did in excel because each is very descriptive and I need to
to concatenate many levels of each field name and description (very complex
business and descriptions). I just want to start building tables in Access
without having to copy/paste each cell in excel one at a time.....just
checking if I am missing something..as I do not have visio professional to
assist in my table planning.

Thanks John

 

[John W. Vinson]

Thanks John,

You're correct, I have already normalized my tables (about 25-30) for the
1000 fields. I'm not trying to import data yet, but trying to find a way to
import the design properties for each table (field/data type/field
description). I did in excel because each is very descriptive and I need to
to concatenate many levels of each field name and description (very complex
business and descriptions). I just want to start building tables in Access
without having to copy/paste each cell in excel one at a time.....just
checking if I am missing something..as I do not have visio professional to
assist in my table planning.

Well, I've never tried it, but if you have a Sheet or a Database in Excel with
the fieldnames and some sample data, you should be able to import it. The
problem I forsee is datatypes; Access has strong datatyping (each field must
be a defined datatype and size) while Excel does not, so it may be a
challenge!

If your spreadsheet has cells for Tablename, Fieldname, data type, (preferably
data size for text fields though that's dispensible), and description then I
can imagine writing VBA code to either construct a CREATE TABLE query, or use
the VBA CreateTable and CreateField method.

Since Access fields don't have "levels" it's not quite clear to me what you
mean though!