MS Access 2003 keeps on crashing

route217
route217 used Ask the Experts™
on
Hi Experts

I am using the following code to copy data from an Access Tables and then paste the data into an Excel (2003) worksheet....

But when i run the code and the extraction has completed it crashes MS Access...

Private Sub Export_Daily_Reports_Click()

DoCmd.Echo False, "Exporting, Please Wait.........."

Dim xlPath As String, xlObj As Object
Dim rs As DAO.Recordset, sql As String
Dim RootMIdir As String

xlPath = "\\XXXX\Output Files\Report Templates\"

RootMIdir = "\\XXXXX\\Database\Output Files\"

Set xlObj = CreateObject("excel.application")
     xlObj.Workbooks.Open xlPath & "REPORT_xx_Template.xls"
     
     With xlObj
          .Worksheets("REPORT_xx").Select
          sql = "SELECT *"
          sql = sql & " FROM REPORT_abc"
     
          Set rs = CurrentDb.OpenRecordset(sql)
         
          .Range("a10").CopyFromRecordset rs
     
         .ActiveWorkbook.SaveAs Filename:="\\xxxx\Database\Output Files\REPORT_xx_" & Format(Now(), " dd-mmm-yy") & ".xls"
     
     End With
     xlObj.Quit

Set xlObj = Nothing
Set rs = Nothing
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
I don't really see anything wrong with your code, so I would suspect corruption.

First, make a backup of your Access database, then do this:

1) Compact the database - click Tools - DAtabase Utilities - Compact
2) Compile the database - from the VBA EDitor, click Debug - Compile. Fix any errors, and continue doing this until the Compile option is disabled
3) Compact again

You might also need to Decompile the database. To do that, build a shortcut with this as the target:

"full path to msaccess.exe" "full path to your db" /decompile

Run that, then go through the 3 steps above again

Finally, you can import everything into a new, blank database. Just build a new database and click File - Get External Data, and follow the prompts to move everything from the old db into the new one.
route217Junior

Author

Commented:
Thanks for the feedback LSM...

ok here is where i think the problem lies....when I try and run two macros together....see code:


Private Sub Export_Weekly_Reports_Click()

DoCmd.Echo False, "Exporting, Please Wait.........."

Dim objxlApp As Object
Dim xlPath As String
Dim rs As DAO.Recordset, sql As String
Dim RootMIdir As String

'REPORT_1

xlPath = "\\XXXX\Database\Output Files\Report Templates\"

RootMIdir = "\\XXXX\Output Files\"

Set objxlApp = CreateObject("excel.application")
     objxlApp.Workbooks.Open xlPath & "R1_Template.xls"
     
     objxlApp.Application.Visible = False
     
     With objxlApp
          .Worksheets("R_1").Select
          sql = "SELECT *"
          sql = sql & " FROM R_1"
     
          Set rs = CurrentDb.OpenRecordset(sql)
         
          .Range("a2").CopyFromRecordset rs
                   
       End With
     
      objxlApp.ActiveWorkbook.SaveAs Filename:="\\XXXX\Database\Output Files\R_1_" & Format(Now(), " dd-mmm-yy") & ".xls"
      objxlApp.ActiveWorkbook.Close
             
     objxlApp.Quit
     
Set objxlApp = Nothing
Set rs = Nothing

'RC_W_REPORT_ZEROCHASE

'REPORT_2

xlPath = "\\XXXX\Database\Output Files\Report Templates\"

RootMIdir = "\\XXXX\Output Files\"

Set objxlApp = CreateObject("excel.application")
     objxlApp.Workbooks.Open xlPath & "R2_Template.xls"
     
     objxlApp.Application.Visible = False
     
     With objxlApp
          .Worksheets("R_2").Select
          sql = "SELECT *"
          sql = sql & " FROM R_2"
     
          Set rs = CurrentDb.OpenRecordset(sql)
         
          .Range("a2").CopyFromRecordset rs
                   
       End With
     
      objxlApp.ActiveWorkbook.SaveAs Filename:="\\XXXX\Database\Output Files\R_2_" & Format(Now(), " dd-mmm-yy") & ".xls"
      objxlApp.ActiveWorkbook.Close
             
     objxlApp.Quit
     
Set objxlApp = Nothing
Set rs = Nothing

DoCmd.Close acForm, "Subform - XXX Menu"

Beep
MsgBox "Exporting Daily/XXX", vbInformation, "Export"
route217Junior

Author

Commented:
Ok another problem is when I go to task manager and under processes
Click image Name...

I notice that Excel.EXE is still running and does not close down...
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
Did you try the maintenenace steps suggested?

Excel will continue to run if the code module never completes.
route217Junior

Author

Commented:
Yes I did LSM..

And still the same problem..
route217Junior

Author

Commented:
Hi LSM

how would you kill i.e. make sure the following

 objxlApp.ActiveWorkbook.SaveAs Filename:="\\XXXX\Database\Output Files\R_1_" & Format(Now(), " dd-mmm-yy") & ".xls"
      objxlApp.ActiveWorkbook.Close

was not runing as the objxlAPP.quit kill the object and might leave the saveas still open....??????
define another Dim to close down the activeworkbook.???
Michael CarrilloInformation Systems Manager
Top Expert 2012

Commented:
I would recommend that you run Windows update to ensure that both Windows and Office are fully up to date.  You might have a shared dll issue.

I see that you are using Access and Excel 2003. What version of Windows are you running?
route217Junior

Author

Commented:
Windows xp service pack 3

I believe that the active work is not closing and this is causing the db to hang or crash...
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<<I notice that Excel.EXE is still running and does not close down...>>

 To aid in troubleshooting, I would:

1. Make the Excel app visisble after you create it.

2. Place a STOP at the top of each procedure.  Then step through the code line by line (F8) and watch what happens, especially on the objxlApp.Quit statement.

 That's not a direct answer to why Access is crashing, but it may yield some insight.

Jim.
route217Junior

Author

Commented:
Pressing F8 and unable to step through vba code ????
route217Junior

Author

Commented:
Ok experts

One quick question how would you after the obj app.quit

Open up the template file and saveas with a new name and then close that file down?

Thanks
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<<Pressing F8 and unable to step through vba code ???>>

 You hit the STOP correct?  and could not single step from there with F8?
route217Junior

Author

Commented:
Yes I did...and I am stumped...
route217Junior

Author

Commented:
Hi experts

Give me one hour and I'll uPload a sample file and db...
route217Junior

Author

Commented:
Files uploaded..
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
route217: How did JimD's comment provide you with a solution? He just provided troubleshooting tips, not suggestions on how to fix your issue.
route217Junior

Author

Commented:
Hi LSM

Still stick on the question and I still am looking for a solution.

Thanks
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
Now back with the Expert hat on..

I got busy yesterday and never got back to this.  I'll try to get to it today, but so far the day's not looking much better for having any real amount of free time.

Jim.
route217Junior

Author

Commented:
Dear all experts

That have assists in the question - firstly, many thanks to everyone, for their time and effort
On solving this question

But I have managed to solve the problem...

Well be posting answer shortly...

Many thanks everyone
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<<Well be posting answer shortly...>>

 When you do so, make sure you accept your own comment as answer.

Jim.
Junior
Commented:
apologies the error was on my part and

DoCmd.Echo False, "Exporting, Please Wait.........."

should have been DoCmd.Echo True, "Exporting, Please Wait.........."

thanks
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
If it was "based on feedback from experts", then you should probably accept those comments which provided the feedback as Assisted solutions.
route217Junior

Author

Commented:
managed to amend the vba and stop the mistake i made - based on feedback from experts

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial