UPDATING AN OLE PIVOT TABLE- Access 2000

[Sdaddy]

I am using access 2000. Win NT. I am trying to write a sub that will on_click open an OLE(pivot table) and refresh the data without having the user navigate to the pivot table in excel and press the refresh button. I am working with users that have very limited knowledge in access and excel, and i am afraid they will screw up the form. LOL.

I am trying to play off of the Edit Button that is the default on a pivot table form. Here is the code generated

Private Sub btnEdit_Click()
On Error GoTo btnEdit_Err
Me!PivotTable.Verb = acOLEVerbOpen
Me!PivotTable.Action = acOLEActivate
btnEdit_Exit:
Exit Sub
btnEdit_Err:
MsgBox Err.Description
Resume btnEdit_Exit
End Sub


I am a rookie when it comes to writing stored procedures. I am very strong in access itself. If anyone has any other suggestions on how to make the pivot table refresh without converting the database to a project, let me know. I am game for anything at this point. Users generating the reports are colleagues, so it doesn't have to be invisible to the user. Please help.


As always---- Thank you oh wise ones!!!!

 

[vbajock]

If the tables are attached to the Access database, you should only need to insert a requery statement.

Me!PivotTable.Verb = acOLEVerbOpen
Me!PivotTable.Action = acOLEActivate
me.requery

 

[Sdaddy]

Thanks VBA, but that didn't quite work. See the problem is that I still need to click the ! refresh button on the pivot table itself. My users may not grasp this. I also need the excel spreadsheet to close and return to form view.

Is there anyway to create a toolbar and make it bound to the OLE, so when a user is looking in form view, they can click the refresh button and accomplish the goal without the user having to work from the OLE.

 

[vbajock]

Try me.refresh

If that doesn't work, you could consider using the Excel Object model within Access, but that is going to require a lot of coding

 

[Sdaddy]

VBA--- Thanks for your attempt. Unfortunately it didn't work. Guess I'll head back to the old drawing board.

And people think the work we do is easy. LOL. Maybe I can pull the same thing off in a query if i think about it long enough.

If anyone else cares to take a shot.. I'm game. The concept will provide enormous value to the db, so I'm game.

It stinks when you know how to do something... but your employer(one of the largest companies in the world)is still using office 2000 and Windows NT.

This would be simple in an access project, or with Office 2003.. which i have at home, but the place i work for doesn't have a clue about how much a 400 dollar upgrade would bring them. ..


OK That's my emotional outburst for the night.

 

[vbajock]

NT is really out of gas. It might be the source of your problem. Your OLE object should refresh on command. You might ask your Network Superman if he is running Service Pack 6 on the NT box.