how to refresh excel sheet which is linked with MS Access

[Saroja sahu]

hi,

i have a database in MS Access which is realtime get updated. i have linked a excel to get report. so i want to refresh the excel sheet automatically in every 5 minute.

please help me.

Saroja

[zmbd]

Do you have Access running too or just the workbook open?

[jimatqsi]

Saroja, what would be the point. You cannot edit the worksheet when it is open in Access. Excel reports the sheet is unavailable or corrupt. It cannot change.

Jim

[zmbd]

jimatqsi
Saroja, what would be the point. You cannot edit the worksheet when it is open in Access. Excel reports the sheet is unavailable or corrupt. It cannot change.

Jim

Depends on how you set the connection, you can enable share read/write from the Excel side; thus the table isn't locked.


@Saroja sahu;

ACC2010: Open the database file with the table of interest.
<this is importaint< The file must be opened.
Open the table (optional, but best)
Leave in idle

XL2010:Ribbon:D ata:From Access
Dialog opens to select the file: Select the database file
[OK]
>>>The following will only happen if the database file is open and the table/query is open

Dialog opens "Data Link Properties"
{Advanced} Tab
By default all linked tables are opened in share and deny write; thus, locking the table in Access.
Change this to: "Share Deny None"
make sure to uncheck "share deny write"

<<<<
"Select Table"
make your selection from the tables/queries
[OK]
"Import Data" dialog
Make your selections here
click on the [Properties] button
{Usage} Tab
There is an option here to set the refresh rates etc...
Usually there is no other need to alter anything in the other tab... usually.
[OK]
[OK]
The table will insert.
(you can now close the ACC2010 database)

Didn't get the connection dialog box?
Forgot to set the refresh rate?
All is not lost...
XL2010:Ribbon:D ata:Connections :Connections
A dialog box with the current workbook connections will open
If you have multiple linked tables these will all show, not sure which one to chose, then select one from the top list and click in the bottom list to see what range/table is using the connection - double click if you want to go there, the dialog will stay open.
Once you have the right connection selected, [Properties]
Here you can change the update information.

YOU CAN ALSO CHANGE THE CONNECTION STRING>>>>
Be careful here, the syntax is exact and case sensitive:
{Definition} tab
The connection string box
Look for this: ;Mode=Share Deny None;
if it is this: ;Mode=Share Deny Write; then your Access database will be locked when you open the Excel File
if it says this: ;Mode=ReadWrite ; you should be here; however, I've occationally had issues with the mode switching back to the "Share Deny Write" mode which will lock your database.

Another thing,
Select a cell within the linked table range
XL2010:Ribbon:T able Tools:Design:Ex ternal Table Data:Properties
OR
XL2010:Ribbon:D ata:Connections :Connections:Pr operties

I like to select the "Overwrite exsiting..." option button.

and there's some of the more advanced information.

there are also means by which to write databack thru the link; however, that's a tad more difficult and (IMHO) best done via VBA automation.

[jimatqsi]

Thanks, zmbd, I'm glad to learn that. But fearful of telling my clients they can do that :)

Jim

[zmbd]

They cannot easily alter the data from the Excel side; thus, no worries about violating your business model in the Access application.