strange 3211 run time error

[datacontrol]

I have a huge Access GUI form set up for users to manipulate a database. On command button in particular is giving me problems. I get a run time error 3211 when executing this command.
"File is already in use by another process" . I made certain no instances of it are open.

Below is my entire code, please note the command131 private sub, as that is the problem area. I suspect there is another section of the code causing this error.

Thanks in advance!


Private Sub Command117_Click()
On Error GoTo report_Err

'Create a Recordset from all the data in the INVWC7 table
Dim sRecon As String
Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset

sRecon = "N:\LOG PRO-ADV RECON\reconciliation.mdb"
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sRecon & ";"
conn.CursorLocation = adUseClient
Set rs = conn.Execute("INVWC7 Query", , adCmdQuery)

'Create a new workbook in Excel
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)

'Transfer the data to Excel
xlSheet.Range("A1").CopyFromRecordset rs

'Save the Workbook and Quit Excel
xlBook.SaveAs "N:\LOG PRO-ADV RECON\advantage\INV280-B.xls"
xlApp.Quit

export_Exit:

MsgBox "Your INV280-B report has been saved to: N:\LOG PRO-ADV RECON\advantage\INV280-B.xls"

Exit Sub


report_Err:
MsgBox "Your report could not be exported. Be certain that you have no other instances of this file open."
Exit Sub

'Close the connection
rs.Close
conn.Close

End Sub





Private Sub Command131_Click()


DoCmd.DeleteObject acTable, "INVWC7"


invwc7_Exit:
MsgBox "INVWC7 data has been deleted"
Exit Sub


End Sub

Private Sub Command28_Click()

On Error GoTo delete_invwc6_Err

DoCmd.DeleteObject acTable, "INVWC6"


delete_invwc6_Exit:
MsgBox "The INVWC6 table has been deleted"
Exit Sub

delete_invwc6_Err:
MsgBox "Error...the data was NOT deleted"




End Sub


Private Sub Command30_Click()
On Error GoTo delete_invwc6_import_errors_Err

DoCmd.DeleteObject acTable, "invwc6_ImportErrors"


delete_invwc6_import_errors_Exit:
MsgBox "INVWC6 import errors have been deleted"
Exit Sub

delete_invwc6_import_errors_Err:
MsgBox "There were no errors to delete!"
Exit Sub
End Sub

Private Sub Command31_Click()
On Error GoTo delete_invwc7_import_errors_Err

DoCmd.DeleteObject acTable, "invwc7_ImportErrors"


delete_invwc7_import_errors_Exit:
MsgBox "INVWC7 import errors have been deleted"
Exit Sub

delete_invwc7_import_errors_Err:
MsgBox "There were no errors to delete!"
Exit Sub
End Sub

Private Sub Command32_Click()
DoCmd.OpenQuery "INVWC6 DIFFERENCE >0"
End Sub

Private Sub Command33_Click()
DoCmd.OpenQuery "INVWC7 POSITIVE SIGN"
End Sub

Private Sub Command34_Click()
DoCmd.OpenQuery "INVWC7 NEGATIVE SIGN"
End Sub

Private Sub Command88_Click()

On Error GoTo Err_view

DoCmd.OpenQuery "invwc7_ImportErrors Query"

Err_view:
MsgBox "There are no errors in the INVWC7 data"

End Sub

Private Sub Command91_Click()
Dim objWord As Object

Set objWord = CreateObject("word.application")

With objWord
.Documents.Open ("N:\LOG PRO-ADV RECON\reconciliation.doc")
.Visible = True
.Activate
End With

Set objWord = Nothing

End Sub

Private Sub Command93_Click()

On Error GoTo do_output_Err

DoCmd.OutputTo acTable, "INVWC6", "Microsoft Excel (*.xls)", "N:\LOG PRO-ADV RECON\EXCEL\invwc6.xls", True, ""

do_export_Exit:
MsgBox "The INVWC6 data has been exported to an excel file."
Exit Sub

do_output_Err:
MsgBox "The data could not be exported. Please make sure you do not have an instance of the file open."


End Sub





Private Sub Command94_Click()
On Error GoTo err_output


DoCmd.OutputTo acTable, "INVWC7", "Microsoft Excel (*.xls)", "N:\LOG PRO-ADV RECON\EXCEL\invwc7.xls", True, ""


export_invwc7_Exit:

MsgBox "The INVWC7 data has been exported as: invwc7.xls."

Exit Sub

err_output:
MsgBox "The data could not be exported. Please make sure you do not have an instance of the file open."
Exit Sub

End Sub

Private Sub Command95_Click()
On Error GoTo err_output

DoCmd.OutputTo acQuery, "UNMATCHED RECORDS", "Microsoft Excel (*.xls)", "N:\LOG PRO-ADV RECON\EXCEL\unmatched records.xls", True, ""
delete_Exit:
MsgBox "The unmatched records data has been exported to an excel file."
Exit Sub
err_output:
MsgBox "The data could not be exported. Please make sure you do not have an instance of the file open."
End Sub

Private Sub Command96_Click()
On Error GoTo output_Err

DoCmd.OutputTo acQuery, "UNMATCHED RECORDS WITH DIFF >0", "Microsoft Excel (*.xls)", "N:\LOG PRO-ADV RECON\EXCEL\unmatched records with difference greater than zero.xls", True, ""
export_Exit:
MsgBox "The unmatched records with difference greater than zero data has been exported to an excel file."
Exit Sub
output_Err:
MsgBox "The data could not be exported. Please make sure you do not have an instance of the file open."
End Sub

Private Sub Command97_Click()
On Error GoTo output_Err
DoCmd.OutputTo acQuery, "INVWC7 POSITIVE SIGN", "Microsoft Excel (*.xls)", "N:\LOG PRO-ADV RECON\EXCEL\invwc7 records with positive sign.xls", True, ""
export_Exit:
MsgBox "The INVWC7 positive sign data has been exported to an excel file."
Exit Sub
output_Err:
MsgBox "The data could not be exported. Please make sure you do not have an instance of the file open."
End Sub

Private Sub Command98_Click()
On Error GoTo output_Err

DoCmd.OutputTo acQuery, "INVWC7 NEGATIVE SIGN", "Microsoft Excel (*.xls)", "N:\LOG PRO-ADV RECON\EXCEL\invwc7 records with negative sign.xls", True, ""
export_Exit:
MsgBox "The INVWC7 negative sign data has been exported to an excel file."
Exit Sub
output_Err:
MsgBox "The data could not be exported. Please make sure you do not have an instance of the file open."
End Sub

Private Sub COUNT_INVWC6_Click()
DoCmd.OpenQuery "COUNT INVWC6"
End Sub

Private Sub COUNT_INVWC7_Click()
DoCmd.OpenQuery "COUNT INVWC7"
End Sub






Private Sub DUPLICATE_RECORDS_INVWC6_ONLY__Click()
DoCmd.OpenQuery "DUPLICATE RECORDS(INVWC6 ONLY)"
End Sub

Private Sub IMPORT_INVWC6_Click()
On Error GoTo import_delimited_Err

DoCmd.TransferText acImportDelim, "Invwc6_20030711050215 Import Specification", "INVWC6", "n:\LOG PRO-ADV RECON\TEXT FILES\invwc6.txt", False, ""


import_delimited_Exit:
MsgBox "The INVWC6 data has been imported. Please check for errors."
Exit Sub

import_delimited_Err:
MsgBox "Error. The INVWC6 data has not been imported."
Resume import_delimited_Exit
End Sub

Private Sub IMPORT_INVWC7_DATA_Click()
On Error GoTo import_delimited_2_Err

DoCmd.TransferText acImportFixed, "Invwc7_20030711053142 Import Specification", "INVWC7", "n:\LOG PRO-ADV RECON\TEXT FILES\invwc7.txt", False, ""


import_delimited_2_Exit:
MsgBox "The INVWC7 data has been imported. Please check for errors."
Exit Sub

import_delimited_2_Err:
MsgBox "The INVWC7 data has NOT been imported"
Resume import_delimited_2_Exit

End Sub

Private Sub UNMATCHED_RECORDS_Click()
DoCmd.OpenQuery "UNMATCHED RECORDS"
End Sub

Private Sub UNMATCHED_RECORDS_W_DIFF__0_Click()
DoCmd.OpenQuery "UNMATCHED RECORDS WITH DIFF >0"
End Sub

Private Sub VIEW_INVWC6_IMPORT_ERRORS_Click()

On Error GoTo Err_view

DoCmd.OpenQuery "invwc6_ImportErrors Query"

Err_view:

MsgBox "There are no errors in the INVWC6 data"

End Sub
Private Sub WORD_DOC_Click()
On Error GoTo Err_WORD_DOC_Click

Dim oApp As Object

Set oApp = CreateObject("Word.Application")
oApp.Visible = True

Exit_WORD_DOC_Click:
Exit Sub

Err_WORD_DOC_Click:
MsgBox Err.Description
Resume Exit_WORD_DOC_Click

End Sub
Private Sub Command90_Click()
On Error GoTo Err_Command90_Click


Screen.PreviousControl.SetFocus
DoCmd.FindNext

Exit_Command90_Click:
Exit Sub

Err_Command90_Click:
MsgBox Err.Description
Resume Exit_Command90_Click

End Sub

 

[Fizzio]

Phew! that took some reading, Should have been home 30 mins ago but just had to have a look

I think you are right in it not being the command click but I think this is the culprit:

export_Exit: 

MsgBox "Your INV280-B report has been saved to: N:\LOG PRO-ADV RECON\advantage\INV280-B.xls" 

Exit Sub 


report_Err: 
MsgBox "Your report could not be exported. Be certain that you have no other instances of this file open." 
Exit Sub 

'Close the connection 
rs.Close 
conn.Close 

End Sub

Unless there is an error, conn is not being closed - correct the code to this and see if it helps.

export_Exit: 

msgBox "Your INV280-B report has been saved to: N:\LOG PRO-ADV RECON\advantage\INV280-B.xls" 

'Close the connection 
rs.Close 
conn.Close 

Exit Sub 


report_Err: 
MsgBox "Your report could not be exported. Be certain that you have no other instances of this file open." 
resume export_Exit

End Sub

 

[datacontrol]

solved

That was the problem. I thought that is what the problem might be, however I didn't want to go destrying things until I was certain...

one last thing....sorry for the coding mess...I am a VB default guru!

Thanks again

 

[datacontrol]

further review

upon further review, I was still getting errors, so I changed your code to this: (notice I closed the connection before export_Exit)

'Close the connection
rs.Close

conn.Close
export_Exit:

MsgBox "Your INV280-B report has been saved to: N:\LOG PRO-ADV RECON\advantage\INV280-B.xls"

Exit Sub


report_Err:
MsgBox "Your report could not be exported. Be certain that you have no other instances of this file open."
Exit Sub



End Sub

 

[fuzzygeek]

"Nothing" keyword

Sometimes just to be sure that you have closed not just the instance but the object also, you need to use "Nothing". The following is from the VBA help file:

The Nothing keyword is used to disassociate an object variable from an actual object. Use the Set statement to assign Nothing to an object variable. For example:

Set MyObject = Nothing

Several object variables can refer to the same actual object. When Nothing is assigned to an object variable, that variable no longer refers to an actual object. When several object variables refer to the same object, memory and system resources associated with the object to which the variables refer are released only after all of them have been set to Nothing, either explicitly using Set, or implicitly after the last object variable set to Nothing goes out of scope.

I use it to close out the excel object, recordset objects, etc. like the following:

objXL.Quit 'Excel
wbXL.Close 'Workbook
Set wsXLData = Nothing 'Worksheet
Set wsXLResults = Nothing 'Worksheet
Set wbXL = Nothing 'Workbook
Set objXL = Nothing 'Workbook

 

[Fizzio]

Your refined code is still incorrect as now, if there is an error, the connection will not be closed. My syntax was correct.

Are you getting the same error message?

If so, this is not the problem. Indicate which line is giving you the trouble when you debug the code and that may give a better idea. Fuzzy geek is also correct with his code to 'tidy up' variables after you have finished with them.