Dick,

Re your #1, as follows is in my original msg:
Of course, this could be physically re-constructed on another tab in Excel,
but ...
a) This schema is required for multiple tabs.
b) It would be redundantly using large groups of data.
b) The workbook size would start to become prohibitive.

It may end up though, that I rebuild everything to include the two 1-cell
ranges on every worksheet.
But as stated above, it would merely be using hundreds of rows of redundant
data unnecessarily, on multiple tabs in each workbook.
In attempting to keep an already-very-large file-size in check, am hoping
for a more elegant solution.


Prefer your suggestion #3 as follows.
An example would be sincerely appreciated.

3) Don't link the tables via the Access UI. Create the tables in VBA using
ADO. This will give you maximum flexibility, but it's the most complex of
the options.

Regards and thanx.
- Mike



"Dick Kusleika" wrote:

On Sat, 14 Feb 2009 12:40:01 -0800, MikeF
wrote:

Dick,
Thanx for the reply.

Am merely using the Access "Get External Data", then "from Excel".
It's a straight import, not a link [although at some point in the future
will need to do that].

A few clarifying notes:
- The Access table has 6 fields, the first two being EventID and CityID.

- The Excel range has 4 fields in columns C thru F [although those fields
could be in any 4 contiguous columns]. Those fields exclude EventID and
CityID.

- EventID and CityID are both named, 1-cell ranges on another worksheet in
the same Excel workbook.

- It is imperative for the Access table that the values in these 1-cell
ranges [EventID and CityID] "hit" the import from Excel, in as many rows as
necessary.

*** ALSO NOTE - there are dozens of different workbooks containing these
same
ranges that eventually will need to be imported into the Access table.
The values for each of the three ranges are different in each workbook.

Does the above help??


I assume you mean you want the Access table to have 6 columns, but it
doesn't because you're importing from Excel.

Three options, as I see it:

1) Insert two columns in front of column C, refer to the CityID and EventID
in a formula in those columns, and extend your named range to include those
columns. This is the straightforward and obvious answer. You haven't said
why you haven't done this, but I assume you can't add the columns for some
reason. Would it matter if they were hidden?

2) Make two linked tables in Access; one with your C:F range and one that
contains the CityID and EventID. If those two cells aren't side-by-side,
you'll need to make an area somewhere that gets them side-by-side and create
a range name that covers both of them (Access doesn't like single cell range
names for some reason).

Now create a query in Access with these two tables and NO JOINS. This will
give you a recordset like you want.

3) Don't link the tables via the Access UI. Create the tables in VBA using
ADO. This will give you maximum flexibility, but it's the most complex of
the options.

If any of those options sound good and you need more details or an example,
post back.
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com