MS Access 2003 keeps on crashing

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.

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"

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...

Did you try the maintenenace steps suggested?

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

Yes I did LSM..

And still the same problem..

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.???

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?

Windows xp service pack 3

I believe that the active work is not closing and this is causing the db to hang or crash...

<<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.

Pressing F8 and unable to step through vba code ????

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

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

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

Yes I did...and I am stumped...

Hi experts

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

Sample-Data-rev.mdb
Sample-Template-1.xls
Sample-Template-2.xls

Files uploaded..

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

Hi LSM

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

Thanks

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.

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

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

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

Jim.

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

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