Access Database Linked to Excel spreadsheet refreshing too often (1 Viewer)

[chrisjames25]

Hi. I have connected one of my qry from my access database to an excel file via the get data button in excel ribbon.

Data came through fine but it appears to be refreshing the data constantly. About every 3 seconds. SO while i am using the spreadsheet i get the curser change to the refresh curser all the time. It is driving me nuts and making it unworkable.

THe queries and connections sidebar in excel states loading data but the data is already loaded. Is there a way of telling it to only refresh at say every 5 mins?

 

[Isaac]

I think it is more likely that what you are seeing is the Access interface trying to page through records and render them on screen.
Rather than actually, effectively, refreshing the live data.

This is precisely how Access behaves when you open a datasheet in, for example, a query that contains too many or too slow VBA functions in it.
And how it behaves when you open a Sharepoint linked list and start 'moving around'. And in the SP case, it's definitely not because it's updating live data on screen. Whether this is a proper analogy to your case I am not certain.

 

[chrisjames25]

Cheers for feedback.

I think i have found problem. From the access data that was being imported i had created various pivot tables. And to refresh them all when the data changed i had the following code in excel

Private Sub Worksheet_Change(ByVal Target As Range)

'ThisWorkbook.RefreshAll

End Sub

I think this may have been creating a circular problem in that it was constantly trying to refresh the access database. I will amend code to just refresh pivot tables if that is possible

 

[Isaac]

Interesting.
Yes, I think there is a PT.pivotcache.refresh method, or something along those lines. Not sure if changing that will solve the problem or not, but hopefully.
I really don't recommend linking to Excel files at all. Maybe just for a moment to import them. It's fraught with so many issues.

 

[chrisjames25]

HI Issac.

I didnt really want to link if honest. What i really wanted to do was when i click a command button in access it exported my qry data to a already existing worksheet in excel and named that range. Is that possible or not?

 

[Isaac]

Yes, it's possible. I can't remember at this present moment to what extent DoCmd.TransferSpreadsheet or .OutputTo will get you towards what you want (at least partially, I think), but since naming the range is going to have to utilize Excel automation anyway, you might as well just start out that way.
Look into a sequence of:
declare & set an excel application object
declare & set an excel workbook (app.workbooks.open) object
declare and set an excel worksheet object =workbook.worksheets("name")
use access vba's copyfromrecordset

name a range in excel. honestly for this i'd just record a macro, check out the code and tweak to suit for your variable names, etc.

 

[Pat Hartman]

Access doesn't play well with others. I would never share a live spreadsheet with Access. Always export the data from Access to a spreadsheet. Or, if you prefer, have Excel import the data when it opens. Whether you push or pull depends on whether or not the Excel users are on the same LAN as the Access database. Pushing the data from Access to a template would be my choice.

I would keep the Access data as a separate sheet and have the workbook's calculations built around referring to the "data" sheet. Using this method, you would open a template and create a new copy so you always start clean. Then just close the workbook and export using TransferSpreadsheet or use the method suggested by Isaac.

 

[The_Doc_Man]

I have to agree with Pat here. Having a "live" worksheet as a linked table leads to all sorts of bug-a-boos. Particularly since Excel doesn't share very well when multiple users have it open, trying to also use a spreadsheet via Access at the same time that Excel users are involved can be a higher order of nightmare.

 

[Isaac]

@chrisjames25
Sounds like you now have fully 3 people agreeing on the same thing.

Try the code paths I posted and let us know how you get on, if you choose.