Linked Table with Open Source File

[Reto Walker]

Hi everyone

I have an Access (2003) database with a linked table to an Excel
spreadsheet. The Excel spreadsheet contains live data (e.g. is continously
updated via DDE) and for that reason must remain open at all times.

The problem is that while the Excel sheet is open, all queries, forms access
etc based on that Excel sheet take forever (30+ seconds) to execute. With
the Excel sheet closed, the same queries, forms access etc is lighting fast
but obvisouly only contains the information since the Excel sheet was
closed.

Do you have any tips or suggestions on how to get around these limitations
(e.g. have Excel open as well as fast execution of Access commands) also
considering that I would like to have a multi-user environment (but only
"read-only" on the linked Excel table).

Thanks in advance for any suggestions

Regards,

Reto

 

['69 Camaro]

Hi, Reto.

Do you have any tips or suggestions on how to get around these limitations
(e.g. have Excel open as well as fast execution of Access commands) also
considering that I would like to have a multi-user environment (but only
"read-only" on the linked Excel table).

Use a database file, not an Excel file, for the data input in a shared
environment. Microsoft Access was designed for multiuser environments,
won't lock the table when others are inputing or viewing the data
simultaneously, and has a small footprint.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Reto Walker]

Hi Gunny

Thanks for your input

Use a database file, not an Excel file, for the data input in a shared
environment.

1. The live data can not be inputed into an Access database - only Excel.
The live data is a real-time DDE feed from a stock price software
(Quotespeed 5.0 from Tenfore).

2. I need to do some calcs on the raw (Quotespeed) data first (in Excel)
before I want to run queries etc on that calculated data

also

Microsoft Access was designed for multiuser environments, won't lock the
table when others are inputing or viewing the data simultaneously, and has
a small footprint.

Multiuser environment is strictly in Access - nobody (other than me in
Excel) has do alter that spreadsheet in any way. I just want to read the
values from the spreadsheet via linked table into Access where multiple
users can run queries etc on that linked table simultaniously.

Thanks again for your feedback.

Regards,

Reto

 

['69 Camaro]

Hi, Reto.

1. The live data can not be inputed into an Access database - only Excel.
The live data is a real-time DDE feed from a stock price software
(Quotespeed 5.0 from Tenfore).

2. I need to do some calcs on the raw (Quotespeed) data first (in Excel)
before I want to run queries etc on that calculated data

After running your Excel calculations, import the data into an Access table,
then run the queries on the Access table, not the Excel spreadsheet. Delete
the data when finished and import the data again the next time you need to
run your query. There will be a slight delay to import the data, but not
nearly as long as it takes to run queries on the spreadsheet. And before
you think "The Excel spreadsheet is data in real time, but the Access table
is always going to be static." The query you run on the linked Excel
spreadsheet takes a snapshot of the data for the query, and doesn't
continuously update the query results as the data changes in the
spreadsheet, so it shouldn't matter that the spreadsheet is real-time data,
and the Access table is a snapshot of the data 10 seconds ago.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.