Question Excel Not refreshing Because Access is still open??? (1 Viewer)

[alekkz]

hello everyone.

I need some help figuring this one out.

So one of my forms in my database has a button. when clicked it gathers the information from "query1" and places it on an Excel worksheet, where I then use some small formulas "vlookup" and arrange all that information into a report that I can then print.

My issue is that when that button is my form is pressed it opens Excel with the last information saved onto it, as it should. but when I go into Excel and try to refresh the connection so it can gather all the information it will not refresh.

this is the error am getting.
"You have attempted to open a database that is already opened by user 'XXX' on machine 'XXX'. try again when the database is available."

after doing some research I found that a lot of people get this same error when trying to close their access database. so I don't know if its an Access issue or Excel issue.

please help

thanks in advance

 

[Ranman256]

In access,if you use transferspreadsheet to export the data ,you should not get this error.
(If the excel file is not open)

If you are in excel,and try to run an ADO data connection with access open, you may get the fail error.

 

[boerbende]

what method do you use to export?
Set qdf = CurrentDb.QueryDefs(QueryDf)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, qdf.Name, filenm
qdf.Close
(Maybe you have to close the query ?)

Or
Dim objExcelApp As Excel.Application
Dim objExcelWrkBk1 As Excel.Workbook

Set objExcelApp = CreateObject("Excel.Application")
Set objworkbook = objExcelApp.Workbooks.Open("FileName")

Do your thing here

objworkbook.ActiveWorkbook.Close
objExcelApp.Application.Quit

(maybe you have to close the workbook and application?)

 

[The_Doc_Man]

Ben's advice is on point. The old rule is simple: If you open it, close it. If you take it out, put it back. As RanMan points out, the Transfer Spreadsheet method should include its own Close statement implicit in the call. However, as Ben points out, other methods of opening and loading the spreadsheet will have a different requirement that includes an explicit Close operation.