Jamie, thank you so much for your support

I want to get the big picture right.

Currently I have a hierarchical grouping of files, workbooks,
worksheets, columns and a mirror Array which is indexed to this
structure. I can easily call a specific column belonging to a pecific
worksheet belonging to a spefic workbook....using the structure
spArray(fileName, wbName,shName).Table(rwNumber,colNumber) = cell value,
and I can easily sweep through the database using this approach. I can
also easily copy the entire table in the worksheet into the array with
Table= Range.Value. In this arrangement then I can also have the same
shName if they belong to a different wbName etc

If I do all of this in an Access database can I manintain the same
structure. I'll have hundreds of TableName(s) (assuming equivalent of
shName(s)). Can I structure as follows Shelf (Book1,Book2,....Bookn),
Book1(Table1,Table2...Tablem), Table1(field1,field2...). where Book2
may have the same TableName (with different contents) as one in Book1.

Something like DBengine.Workspaces(x).Databases(y) where x ponts to
"fileName" and y to "wbName" from above?? And if so this means I have my
database spread over several .mdb files. Does this make sense, and can I
then create relationships with the user interface across .mdb files??

Thanks for any suggestions

Kurb


Jamie Collins wrote:

kurb wrote...



I am rethinking my overall appraoch to solving my problem.
I'm wondering whetgher it would make more sense to store all of this in
Access database



Yes, this sounds like the way to go.



for the added flexibility of defining relationships and
sorting data based on these relationships.



Plus you get strong data typing, default values, constraints, data
validation, indexes, etc.



I will also have to draw
the data from Access to display/chart them in Excel

Can this be done within Excel using ADO.



Indeed it can, using a recordset based on a SELECT query.



If I creat the basic tables
from within Access, can I modify Table attributes, Table contents, and
make queries from within Excel



Table contents: of course, using SQL DML e.g. UPDATE, INSERT INTO,
DELETE. Alternatively, by using an updateable recordset.

Table attributes and queries: yes, you can do this from Excel using
ADO (with restrictions e.g. connection requires permissions, all other
connections closed, etc). It is not usual for a client application to
modify table/column definitions or create stored procedures/queries on
the fly but it can be done: say you have a database admin type program
in Excel. You can use ADOX objects (Tables, Columns, etc) but I prefer
SQL DDL e.g. ALTER TABLE, CREATE PROCEDURE, etc.

Jamie.

--