Dynamic Data from Excel

  • Thread starter Thread starter Jackson via AccessMonster.com
  • Start date Start date
J

Jackson via AccessMonster.com

Hi,

Currently I'm linking an excel table into Access, subsequently queries are
run on this table and then subsequently a form-subform shows the results of
the query on the Main Page.

If the Main Form (and subsequent subforms) are open (as they normally will be)
this locks up the excel file so it can't be updated. The excel data will be
coming from a 3rd party export and I was planning on updating it several
times a day, but the Access form prevents this file from being opened.

Does anyone have any ideas how to dynamically update such tables during the
day? I wanted to aviod repeatedly importing and deleting csv files and use a
link in.

Cheers,
Jack.
 
Jackson via AccessMonster.com said:
Hi,

Currently I'm linking an excel table into Access, subsequently queries are
run on this table and then subsequently a form-subform shows the results
of
the query on the Main Page.

If the Main Form (and subsequent subforms) are open (as they normally will
be)
this locks up the excel file so it can't be updated. The excel data will
be
coming from a 3rd party export and I was planning on updating it several
times a day, but the Access form prevents this file from being opened.

Does anyone have any ideas how to dynamically update such tables during
the
day? I wanted to aviod repeatedly importing and deleting csv files and use
a
link in.
Click to expand...

I would strongly advise you to look at using Office Automation to update
your Excel file. If you apply SP2 to MS Office then linked tables (to
Excel) will stop working. Automation can/will update the Excel file
silently, although I can't comment on the pros and cons of this except to
say that Excel isn't a multi-user application - I would expect there to be
only one user at a time updating any one file.

Regards,
Keith.
www.keithwilby.com
 
Only one user/computer will be updating the excel file, this is then to feed
updates into Access for all users though....

Any suggestions on where to start with this? Do I need to make my form 'lose'
focus of it's subform based on the query based on the excel file?
 
I think you are overstating the situation. The Linked Tables will not "Stop
Working". The only prohibition will be that you cannot upate the Spreadsheet
from Access.

I don't know whether it would resolve the issue, but the OP might try making
the workbook containing the linked table a shared workbook. Excel files are,
by default, single user.
 
Klatuu said:
I think you are overstating the situation. The Linked Tables will not
"Stop
Working". The only prohibition will be that you cannot upate the
Spreadsheet
from Access.
Click to expand...

Hi Klatuu.

It was my understanding that, as I put it, "linked tables (to Excel) will
stop working", not linked tables in general - have I missed something?

Regards,
Keith.
 
Well, linked tables to Excel would still work in a scenario where you might
not want to update the linked table in Access, but might want to be able to
see, in Access, any changes to the data that might be made elsewhere, e.g.
in Excel. So in that sense one could argue that linked tables to Excel are
not completely broken. Without question the functionality is impaired,
though.

As an alternative to automation, you could update the Excel file via DAO or
ADO.
 
Essentially, they have become read only. There are still at least two
methods to write data to Excel. One is Automation. The other would be to
import the spreadsheet, make changes to the imported tables, and export the
changed tables. Clumsy and annoying, but workable.

Have you hugged a lawyer today?
 
But why was the write functionality to linked Excel tables removed in the
update from SP1 to SP2? This was a nasty surprise for me especially since I
was using automation to utilize the ability to update Excel tables through
Access, and it was working very well too!
 
Back
Top