Problem refreshing data for pivot table

[lgbatdw]

I have an excel 2003 pivot table that is based on an external access 2003 table. From an access form button I open the pivot table. It does not automatically refresh the data or prompt me. However when I try to refresh it manually I get this Microsoft Excel error message: "[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1" OK is my only response and when I click it I get the next Excel error message "Problems obtaining data." I'm not sure why I'm getting this error as when I click on the field list it displays the fields. It seems like the link has been lost or something. This is the first time I've done this (open excel from access and created an excel PT), so I'm not sure what I'm doing wrong. I was able to create the PT in excel and open it directly and refresh without problem, but now can't even do that.

Thanks in advance to anyone who can set me on the right path.

This is my access code to open excel/workbook.

Dim XL As Object, WB As Object

Set XL = CreateObject("Excel.Application")
Set WB = XL.Workbooks.Open("C:\BST-PTTest\BST-PivotTable")

XL.Application.Visible = True

'pass dates selected from access form to excel worksheet
WB.Sheets(1).Range("a2").Value = "Selected Date Range: " & CDate(Me!StartDate) & " through " & CDate(Me!EndDate)

Exit Sub




Linda in MN

 

[lgbatdw]

Ok, I'm not sure how I did it because it seems like I've tried the exact same thing several times, but obviously I did something different because I did reconnect the data.
Now my issue is how can I automatically refresh the data using code on my access form?

Linda in MN

 

[Steve3110]

I'm assuming you want to refresh the pivot table automatically?

To do that you would need to use code similar to:
WB.Sheets(1).pivottables(1).refresh

 

[lgbatdw]

Yes, thanks. Got it going. Appreciate the quick response!

Linda in MN