I am trying to refresh an excel through a macro in access. I am getting the following error on the refresh table code:
"the database has been placed in a state by user "admin" on machine "XXX" that prevents it from being opened or locked"

The excel refresh link uses the same access dB. Is it not possible to do so? Is there any workaround ?



My access and excel are 2007 version.

The code:
Code:
Dim XL As Object
Dim wb As Object 'Workbooks
Dim ws As Object '  Worksheet
Dim PT As Object '  PivotTable
     


    DoCmd.SetWarnings False
        
    '*********** Format Excel ***********
    
    fileNameSPOC = "G:\BSDT Lease Admins\Daily Productivity Reports\SPOC Productivity.xls"
    'fileNameFLA = "G:\BSDT Lease Admins\Daily Productivity Reports\SPOC Productivity (FLA).xls"
    

    Set XL = CreateObject("Excel.Application")
    Set wb = XL.Workbooks.Open(fileNameSPOC)
     
     
    'XL.DisplayAlerts = False
    
    XL.Visible = True
    For Each ws In wb.Worksheets
        For Each PT In ws.PivotTables
            PT.PivotCache.BackgroundQuery = True
            PT.RefreshTable
        Next PT
    Next ws
    wb.Save
    wb.Close
    XL.Quit
    Set wb = Nothing
    Set XL = Nothing