Access application shuts down after code is run

[MCSGraham]

I have a form where I press a button with code behind it. The code simply imports data from an excel sheet into some different tables using DAO. I do an openrecordset on a table and at the end of the code I close the recordset and set both equal to nothing.

When I press this button usually it pulls the data in from the sheet fine but sometimes when I press it, it runs through the code and puts the data into the tables but then the entire Access application closes.

Has anyone heard of this happening and know what might cause this to happen?

thanks in advance for any help you might be able to give.

 

[zevw]

Do you have an Application.quit command in the code

or does it call a macro that exits.

check all these places.

or lets see your code.

 

[MCSGraham]

I'm not doing an application.quit or running any macros..I'm not quitting the app in any way. Here's some of the code:

Dim db As Database
Dim rst1 As Recordset
Set db = CurrentDb
Set rst1 = db.OpenRecordset("RATES1 - Monthly Costs Summary&quot

Dim dc As Database
Dim rst2 As Recordset
Set dc = CurrentDb
Set rst2 = dc.OpenRecordset("RATES2 - Census Data&quot

Dim dd As Database
Dim rst3 As Recordset
Set dd = CurrentDb
Set rst3 = dd.OpenRecordset("RATES3 - Specific&quot

Dim de As Database
Dim rst4 As Recordset
Set de = CurrentDb
Set rst4 = de.OpenRecordset("RATES4 - Aggregate&quot

Dim df As Database
Dim rst5 As Recordset
Set df = CurrentDb
Set rst5 = df.OpenRecordset("CENSUS&quot

Dim dg As Database
Dim rst6 As Recordset
Set dg = CurrentDb
Set rst6 = dg.OpenRecordset("SPEC&quot

Dim dh As Database
Dim rst7 As Recordset
Set dh = CurrentDb
Set rst7 = dh.OpenRecordset("COMPARE SHEET&quot

Dim di As Database
Dim rst8 As Recordset
Set di = CurrentDb
Set rst8 = di.OpenRecordset("EXP RATE&quot

Dim dj As Database
Dim rst9 As Recordset
Set dj = CurrentDb
Set rst9 = dj.OpenRecordset("QUOTES&quot

Dim dk As Database
Dim rst10 As Recordset
Set dk = CurrentDb
Set rst10 = dk.OpenRecordset("UNDERWRITER CHANGES&quot

Dim dm As Database
Dim rst12 As Recordset
Set dm = CurrentDb
Set rst12 = dm.OpenRecordset("ZIP CODES&quot

Dim dn As Database
Dim rst13 As Recordset
Set dn = CurrentDb
Set rst13 = dn.OpenRecordset("WORKING FEES&quot

Dim dp As Database
Dim rst14 As Recordset
Set dp = CurrentDb
Set rst14 = dp.OpenRecordset("FEES&quot

DoCmd.OpenQuery ("DELETE COMPARE SHEET&quot
DoCmd.OpenQuery ("DELETE SPEC&quot
DoCmd.OpenQuery ("DELETE CENSUS&quot
DoCmd.OpenQuery ("DELETE EXP RATE&quot
DoCmd.OpenQuery ("DELETE QUOTES&quot
DoCmd.OpenQuery ("DELETE UNDERWRITER CHANGES&quot
DoCmd.OpenQuery ("DELETE ZIP CODES&quot
DoCmd.OpenQuery ("DELETE WORKING FEES&quot
DoCmd.OpenQuery ("DELETE FEES&quot

'Some code not shown here moves data in some linked tables to other tables

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "ZIP CODES", "C:\ZipImport.xls", True


This is the jist of it...not doing anything big here at all but I think something in the way the tables need refreshed or requeried, etc. might be the problem...don't know for sure though. I've tried closing the form and reopening it after the code's been ran and it doesn't kill the app. but it gives the messages of: 'Record is deleted' a couple times. I tried doing a requery and refresh after the form opens back up but it gives an error that it can't find the object even though the form has opened. Any help would be greatly appreciated with this one...

 

[zevw]

This seems pretty detailed.

But from your message cant find object. You are doing some Delete Queries at the Data is there Data in the Tables you are querying.

What happens when you debug it step by step do you also have the same problem.

I think you will find the issue, you are doing alot there.

 

[psemianonymous]

1. You only need one reference to the Database, so long as they are all the same database. Replace dc with db, dd with db, etc. and don't .Close the database object. For this reason I would like to see the rest of your code, specifically the recordset/db object closings.

2. You could actually replace all the db, dc, dd, directly with CurrentDB(), which would replace any need to close the database objects/set them to nothing. Either this or #1 will be fine.

2. What do you do with the recordsets once they are opened? If nothing, consider another way to do what you want. You don't need them, I don't think, unless you're doing something in some other code that isn't there.

3. The DoCmd.OpenQuery calls look great. Nothing wrong with that.