Linked table question

On Thu, 14 Aug 2008 13:40:05 -0700, Golfinray
wrote:

I have the Excel files linked. Can I directly write queries, forms, and
reports off the Excel data? What I did was take that data and put it into
Access tables so that I could normalize it, break it up into the right
tables, and then build my queries, forms, and reports, use code and so
forth. I wouldn't be able to do that directly off the spreadsheets would I?

Only if the structure of the spreadsheets is the same as that of the
normalized tables. If the spreadsheets are typically "spreadsheety" you would
need to rerun the import and normalization process to keep in synch.
--

John W. Vinson [MVP]

Changing the subject slightly, the lawsuit required MS to
*PAY* for the ability to update a linked Excel spreadsheet.
MS *REMOVED* the ability because that was cheaper.

(david)

"John W. Vinson" wrote in message
...
On Thu, 14 Aug 2008 16:37:33 -0400, "Steve" wrote:

Jeff,

I believe Microsoft lost a lawsuit a few years ago and as a result if you
installed the latest service pack, you can no longer link to Excel data.

Your understanding is only partially correct. The lawsuit required MS to
remove the ability to *UPDATE* a linked Excel spreadsheet. You can still
link
to the spreadsheet, it's just read-only.
--

John W. Vinson [MVP]

Not sure if anyone will see this request for clarification because the topic
is quite old. If you do however see it, can you just confirm that what I'm
hearing is that currently there is no way to have the Access DB linked SS be
refreshed upon opening?

I have a linked SS and I have to go into the link manager to have it
refresh. I'm wondering if there's not a preference item somewhere that would
tell it "upon opening the Access DB , go grab all the new data in the SS.
(There is no edits, deletions etc on the DB side. It simply uses the SS for
the data)

Thanks if you can verify.

--
Rocky''''''''s Curious Coding


"John W. Vinson" wrote:

On Thu, 14 Aug 2008 10:19:00 -0700, Golfinray
wrote:

I have a split database, BE on a LAN, each user has FE on machine. Some of my
tables need to be periodically updated from Excel spreadsheets. Some need to
be updated often. What is the best way to do that? I understand that now
Excel can no longer be directly linked to Access files? Is it possible to
have "live" tables that automatically update when a spreadsheet is changed?
Or what is the best solution? Thanks a bunch!!!!

Just to clarify a possible misconception... what's been lost is the ability to
*UPDATE* a linked Excel spreadsheet from Access. You can still use File... Get
External Data... Link to create a link to a spreadsheet, which will work (in
most respects) as if it were an Access table; you just can't update the data.

You also cannot Index the linked table, or enforce referential integrity
between it and any other table - so this may not meet your needs.
--

John W. Vinson [MVP]