Access wont close using vba

[ScanGuard]

Hi there,

I hope someone can help me.

I have an Access 2010 database that contains 1 table (listing a number of Excel files).

This Access file is opened by a Windows Scheduled Task set to run daily at a given time.

My problem is that everything works - until it is time to close the Access application (as it has finished doing what it needs to do).

Here is the code (I have converted the AutoExec macro to VBA for ease of conveying the code)

Option Compare Database
Function AutoExec()
On Error GoTo AutoExec_Err
UpdateROS ' refer function below
DoCmd.Quit acExit

AutoExec_Exit:
Exit Function
AutoExec_Err:
MsgBox Error$
Resume AutoExec_Exit
End Function


Option Compare Database
Option Explicit
Public Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function UpdateROS()
'This function is used to create a recordset of Excel files stored in table tblROS.
'Each record has a file name (of the Excel file) plus a flag (to process the file or not)
'The code works as expected - opens the selected Excel file and runs the macro "AutoUpdateROS", then moves to the next record.
'Until the DoCmd.Quit. The application just hangs. I use Windows Task manager to kill the application, or wait until the Schedule Task time limits kick in (set at 4 hours)

Dim objXL As Object
Dim objWB As Object
Dim strfilename As String
Dim strMacro As String
Dim ssql As String
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Set db = CurrentDb

ssql = ""
ssql = ssql + "SELECT tblROS.ID, tblROS.Filename, tblROS.Process "
ssql = ssql + "FROM tblROS "
ssql = ssql + "WHERE tblROS.Process <> 0 "
ssql = ssql + "ORDER BY tblROS.ID"

Set rs1 = db.OpenRecordset(ssql)
Do While Not rs1.EOF
strfilename = "some path\" & rs1!FileName

If rs1!Process <> 0 Then
strMacro = "AutoUpdateROS"
If rs1!ID >= 1 Then
On Error Resume Next
Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
Set objWB = objXL.Workbooks.Open(strfilename)
objXL.Run strMacro


If Err.Number = 0 Or Err.Number = 440 Then
End If
Err.Number = 0
Set objWB = Nothing
End If
End If
rs1.MoveNext
Loop

Set objXL = Nothing
rs1.Close
Set db = Nothing
DoCmd.SetWarnings False
DoCmd.Quit acQuitSaveNone
End Function

Any suggestions ?

If I set all Excel files to NOT be processed and run the file manually, it opens, it creates the recordset, it loops through the recordset, then Exits the Access application. No instance of Access remains.

Kind regards in advance

[June7]

So you have Access open Excel and run code in the Excel. Might need to also close Excel, not just set the object variables to Nothing.

[JeroenMioch]

Im trying to export some data to excel and i read something about this, i think June is right (and he usually is )

[ScanGuard]

Thanks for the suggestion. I shall give that a go.

[ItsMe]

Is it possible to better describe the symptom?

My problem is that everything works - until it is time to close the Access application (as it has finished doing what it needs to do).

What is it that Access or your computer is doing that is not desirable?

I see that the VBA you use is closing the database for you. I suspect this line of code is creating an issue. However, you mentioned that you are not using VBA and you are executing a Macro Object.

[John_G]

You have this line:

Set objXL = CreateObject("Excel.Application")

inside the Do While loop; it is being executed multiple times, but Set objXL = Nothing is only being done once, after the loop has completed.

Try putting Set objXL = CreateObject("Excel.Application") above the Do While...

[ScanGuard]

Hi Itsme,

Not sure if I can better describe the symptom.
The code works insofar as Excel files are opened, and the vba within the excel file runs, saving the excel file and quitting Excel.

After the last excel file is updated, saved and closed, the Access application remains visible. I am unable to do anything within the Access application, and Windows Task manager reports CPU usage at approx 25%. It looks as if the Access application is in an infinite loop.

[ScanGuard]

Hi John G
You are correct, I set the Excel Application inside the loop, because the instance of Excel is closed by the Excel application.

When I am next in the office, I shall give your suggestion a go, but I suspect it will have an issue due the reason I mention above - Excel is closed by the Excel application.

[ItsMe]

...Task manager reports CPU usage at approx 25%. It looks as if the Access application is in an infinite loop.

Considering this description and the fact that the Macro conversion includes the following line ...

DoCmd.Quit acQuitSaveNone

I will assume the macro is still executing because it is hung on the acQuitSaveNone argument. The default is acQuitSaveAll. Design Time changes are not always available. Even though the code is asking to not save anything, this is still being interpreted as a design time change. Long story short, debugging a Macro is difficult if not impossible.

I suggest moving the entire operation over to VBA. I use code behind a form and make that form open at startup. You can select a form to open at startup via Access' Options.

Change DoCmd.Quit acQuitSaveNone to DoCmd.Quit. Remove any error trapping within the VBA. Then, debug your app by double clicking the Task within Task Scheduler (I believe I recall you mentioning you are automating this via Task Scheduler). If there are any exceptions within the process, click the Debug option and make a note of the line causing the exception.