Thanks (again) for your thoughts regarding the big picture for
integrating my Excel appliction with Access using ADO. Let me explain my
application to give you a better idea of what I am trying to do.

1. The data is stored across multiple folders/workbooks/worksheets.
(Yes I couldn't do it any other way with Excel due to performance
limitation). All the rows are date stamped and the sequential order of
the rows are ciritical. All the rows across every worksheet (belonging
to specified set of workbooks/folders) are date synchronized, ie every
row1 has the same date. Every day I get a new row of data for aall the
worksheets. Except for the date all the other cell values are numbers
that are drawn from an external source or statistically derived in Excel

2. Each Excel colum has five headers (Floor.Room.Shelf.Book.. concept).
These headers are meaningful to the User as she specifies them and uses
them in equations to derive more new statistics down new columns. The
data for the column headers she specifies are dropped onto the worksheet
from spArray. The statistics are calculated by Excel on the worksheet.
I now want to do some of the calculations in VBA for special equations.
Say the User inputs something like X(5) + Y(1). Here I need to
associate the symbol X with the five part column header (so I can use it
to identify the column in the). The number refers to the offset from the
current row .

3. Once I have the base data I want to do a lot of queries. Something
like. Sort the values of paramter X (from Start date to Stop date) to
into ten bins (eg histogram) and store them For all the values of X
that fall in Bin1 find the corresponding values (by date) for parameters
Y and Z. Now lets sort the valus we just found for Y into ten bins. Or,
look at data every 5th day beginning from Start date. Then plot and
chart the results.

I have to retain the Floor.Room.Shelf...with date sequenced rows
structure only from the Users perspective.The User specifies exisiting
collumns (for which data must be extracted from the database), defines
new columns headings and equations to create the new data. The User may
review the results that fall on these worksheets and store them in the
database if desires for later review.

I can see from what you're telling me that all three areas can benefit
from integrating with Access. So the big question: what should the
Access archtecture look like. My first response was to map existing
Excel worksheet into an Access Table. Since the sequencing is critical
to the application and since every day I get a new row of data if lump
the data from different workbooks onto one Table I will not be able to
maintain sequence integrity for all my calculations, and also for
plotting time series data. Also the User relates to each Worksheet for
analysis so a unique worksheet/tablename relationship would be convenient.

Does it make sense and can I implement a hierarchical arrangement of
tables in Access?

Really appreciate any thoughts or suggestions.
Thank you
Kurb


Jamie Collins wrote:

kurb wrote ...



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??



Yes, you could model this structure in multiple .mdb files, say with a
'master' .mdb with tables linked to the others. Or you could do joins
between .mdbs on the fly using Jet's pass through technology. I don't
think you'd be able to write meaningful SQL queries e.g. determine
which .mdb you need using pure SQL, so you'd still have to use
procedural e.g. VBA code. There would be some advantages but it
wouldn't justify a re-write if you plan to maintain the same
structure.

I think to get the big picture right you need to make some big
changes.

I'm not sure how 'file' differs from 'workbook'. so I'll read as
'folder'. Your structure seems to be:



Folder


Workbook
Worksheet
Row

You mention 'shelf' and 'book' so let's say we are modelling a
traditional library of paper copy books. You seem to be proposing a
hierarchical structure like the following (think of an expanded tree
view):



floor


room
shelf
book


floor


room
shelf
book


floor


room
shelf
book

But what if this library had another top level, say it had multiple
buildings? How would you add another physical level to your structu
partition you hard drive, have multiple machines on a network <g?

I think you have the wrong model. The preferred model is the
relational model, hence relational database.

I see an entity 'book' having an attribute 'location'. It could be as
simple as that: the location data item could contain metadata about
the floor/room/shelf e.g. first character represents the floor, next
two characters the room code etc. The other elements could be entities
in their own rights and the book location could be a compound of the
ID for each. It depends on the bounds of what you are modelling. For
example, how dynamic is you filing system: do you file in order of
date received, never throw out any books so they always remain in the
same location (and need to use the index to find the locations of all
the books about Excel)? or do you file all the computer application
books together and have to shift the palaeontology section to a new
room because the librarian just ordered every book John Walkenbach has
written?

OK, so I've extrapolated a lot, went off on a tangent even. But you
see how data modelling involves starting from scratch and looking at
things as entities as attributes. You probably proposed the structure
you did because you *don't* want to have to start again!

Excuse me for being blunt he I think you had to write your spArray
routine (and your routines to operate on the resulting array) because
your structure is wrong. If you had the correct structure you could
use SQL code 'out of the box', even if the data was in Excel. SQL was
invented to retrieve data and has been an indubitable success for
twenty or so years. It is extremely unlikely you have invented a
better system <g. Maybe you used multiple worksheets/workbooks
because you had too much data: again, alarm bells should go off that
you are perhaps using the wrong tool for the job.

There is a sliver lining: because of your rigid structure your data
will be easy to convert when you've come up with a new structure,
which is what I think you need to do if you want change. But it may be
best to stick with the current system, assuming it is working, albeit
a bit slow (processors are getting faster <g). I may be wrong but I'm
guessing those arrays aren't exactly a joy to work with...

Jamie.

--