Linked table question

[Golfinray]

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

 

[Steve]

It depends on what you mean by updated. Do you need to replace all existing
records with the records in Excel or do you need to add new records in Excel
to the existing records in your database.

Steve

 

[Golfinray]

If a new record is added to the spreadsheet, I need to add just that record
to the table. All other existing table data would stay the same. No records
might be changed one month, some months there might be 15 or 20 records
changed on 3 different spreadsheets and that would affect 7 or 8 different
tables in my database.

 

[Cathy]

Everyone handles this scenario differently... but the way I've done it in the
past is:

First I create a form to act as my control panel... this panel has button to
access action queries, reports and graphs... so I can monitor everything
that's going on.
My process then is:
I've created temp tables and import the data to the temp tables.
Check for errors in the data.
Check for any additional data by comparing the temp table to the production
tables.
Append additional data.
Empty temp tables.

Cathy

 

[Jeff Boyce]

I'm not aware of any "triggers" built into Excel that would "push" a new
record (or changes to existing records) over into an Access table.

But if the data is being kept in Excel, I believe you can still "link" to
that data. So why would you need to keep a copy in Access? Is there
another business requirement you didn't mention.

And if you do have a business need to store a copy of the same data in both
Excel and in Access, and are looking for a mechanism that can help you keep
these in sync, consider writing a routine that you include as part of the
start-up for the Access application. While this won't be "real-time" and
won't accommodate a change during normal business hours (unless someone
exits the Access app and restarts it), it would refresh the data each time
the app is launched.

.... not that I'm suggesting that! Again, if the data is being maintained in
Excel, why not just "link" to it?


Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Steve]

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. You
still can import Excel data. If you did not install the service pack, you
can still link to Excel as you said.

Steve

 

[Golfinray]

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

 

[John W. Vinson]

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.

 

[Jeff Boyce]

Once the data looks like a 'table' (linked or otherwise), you can create
queries against it, create forms against ... (create forms and reports
against queries you create against the tables ... more flexibility), write
code referring to the "table" and its "fields", etc.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

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]

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.

 

[Guest]

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)

 

[Rocky]

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.