Linked Excel spreadsheet to be used as table

[Stryder09]

So I want to link an Excel spreadsheet to a table in access. Using the link
manager I was able to create the table and I see all the information I need
to see and it is linked to the original spreadsheet. My only problem is,
when the spreadsheet is updated, it is not updating my access table. Is this
even possible to do? How can I make sure this is done everytime the database
opens? Any help would be much appreciated.

 

[Clif McIrvin]

So I want to link an Excel spreadsheet to a table in access. Using
the link
manager I was able to create the table and I see all the information I
need
to see and it is linked to the original spreadsheet. My only problem
is,
when the spreadsheet is updated, it is not updating my access table.
Is this
even possible to do? How can I make sure this is done everytime the
database
opens? Any help would be much appreciated.

Can you use the linked Excel spreadsheet as it appears in Access as a
linked table without creating an Access table? The data in a *linked
table* does not reside in Access, so Access will always see changes to
the spreadsheet ... on the other hand, an *imported table* contains a
*copy* of the data at the time of the import, so will never see future
changes to the spreadsheet.

Try using a linked table; post back if you have further problems.

 

[Stryder09]

Clif, the table is linked not imported but it still will not show the updates
in access.

 

[Clif McIrvin]

I may not be able to help you ... I'm running Office 2003 SP3 on WinXP
SP3.

Here's a couple tests I just ran on my linked spreadsheet (both the
workbook and Access FE are on my local machine):

1. Open linked table from Access (datasheet view of the table)
2. Attempt to open Workbook .... Access denied.
3. Close linked table from Access.
4. Open Workbook
5. Open linked table from Access
6. Modify cell A2 in Excel
7. Modified value appears in open datasheet view

That actually surprised me ... I expected that the modification would
not appear until I saved the workbook.

I established this particular link nearly two years ago ... as best as I
recall I used the link wizard defaults when I did so.

If you are unable to duplicate what I just observed I'd advise you to
post back (reply to this thread) with as much specific detail about your
operating environment and specifically what you have attempted as you
can. I know for a fact there are folks with much more experience in
these matters in this room than I.