By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,396 Members | 1,429 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,396 IT Pros & Developers. It's quick & easy.

how to refresh excel sheet which is linked with MS Access

P: 1
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
May 14 '14 #1
Share this Question
Share on Google+
5 Replies


zmbd
Expert Mod 5K+
P: 5,397
Do you have Access running too or just the workbook open?
May 14 '14 #2

Expert 100+
P: 1,240
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
May 14 '14 #3

zmbd
Expert Mod 5K+
P: 5,397
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:Data: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:Data: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:Table Tools:Design:External Table Data:Properties
OR
XL2010:Ribbon:Data:Connections:Connections:Propert ies

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.
May 14 '14 #4

Expert 100+
P: 1,240
Thanks, zmbd, I'm glad to learn that. But fearful of telling my clients they can do that :)

Jim
May 14 '14 #5

zmbd
Expert Mod 5K+
P: 5,397
They cannot easily alter the data from the Excel side; thus, no worries about violating your business model in the Access application.
May 14 '14 #6

Post your reply

Sign in to post your reply or Sign up for a free account.