importerrors table locking problem

[ghalewood]

Hi everyone,

I am running the following code to import multiple files into a table. I am getting an error, which I am expecting, but for some reason the Importerrors table is being locked and therefore the delete portion of the code is not working. The only way I can manually delete the tables is to close the project and reopen it. Can anyone see where I am going wrong with the code.

I am running Access97.

Sub import_sales()

Dim myDB As Database
Dim rsParameters As Recordset
Dim rsAddress As Recordset
Dim rsNotpolled As Recordset
Dim intstoreno As Integer
Dim strMydate As String
Dim strStore As String
Dim strData As String
Dim strImporterrors As String
Dim strPolllocation As String
Dim strtemp As String
Dim strSql As String
Dim dteNopoll As Date
Dim dteToprocess As Date

Set myDB = CurrentDb()


dteToprocess = InputBox("Please Enter Date to Process", "Process Date", Format(DateAdd("d", -1, Date)))


'import Catalist file using todays date. Parameter DB "parameters" is used
'for path to file
strMydate = Format(dteToprocess, "YYMMDD&quot
dteNopoll = Format(dteToprocess, "dd/mm/yyyy&quot
Set rsParameters = myDB.OpenRecordset("parameters&quot
rsParameters.MoveFirst
strPolllocation = rsParameters!Sales_location

Set rsAddress = myDB.OpenRecordset("SELECT * FROM [store_Details] _ where [do_not_poll] = FALSE ORDER by [store]&quot
rsAddress.MoveFirst

On Error GoTo ErrorHandler
' Enable error-handling routine.

Do Until rsAddress.EOF
strStore = rsAddress!STORE
intstoreno = rsAddress!store_store_no

strData = strPolllocation & rsAddress!Poll_location & "\" & "DE" & strMydate & ".CSV"

DoCmd.TransferText , "sales_import_specification", "sales_data", strData

strImporterrors = "DE" & strMydate & "_ImportErrors"
strtemp = "" & strImporterrors & ""

DoCmd.Close acTable, strtemp
myDB.TableDefs.Delete strtemp
rsAddress.MoveNext
Loop

Set rsNotpolled = myDB.OpenRecordset("select * from [not_polled]&quot

If rsNotpolled.RecordCount <> 0 Then
rsNotpolled.MoveLast

If MsgBox(&quot;There were &quot; & rsNotpolled.RecordCount & &quot; stores not &quot; _
& &quot;polled. See report for details&quot;, vbOKOnly, &quot;Error&quot = vbOK Then
End If

End If

GoTo endofproc

ErrorHandler:
' Error-handling routine.

Select Case Err.Number
' Evaluate error number.
Case 0
' nothing
Case 3044
' &quot;Directory Does not Exist&quot; error. i.e. not polled

If MsgBox(&quot;Directory &quot; & strData & &quot; Does not Exist&quot;, vbOKOnly, _
&quot;Import Data&quot = vbOK Then
End If

Set rsNotpolled = myDB.OpenRecordset(&quot;not_polled&quot
rsNotpolled.AddNew
rsNotpolled!Store_Number = intstoreno
rsNotpolled!Store_Name = strStore
rsNotpolled!date_not_polled = dteNopoll
rsNotpolled!type = &quot;Not Polled&quot;
rsNotpolled!reason = &quot;Directory does not exist&quot;
rsNotpolled.Update
Case 3011
' file not in directory
Set rsNotpolled = myDB.OpenRecordset(&quot;not_polled&quot
rsNotpolled.AddNew
rsNotpolled!Store_Number = intstoreno
rsNotpolled!Store_Name = strStore
rsNotpolled!date_not_polled = dteNopoll
rsNotpolled!type = &quot;Not Polled&quot;
rsNotpolled!reason = &quot;No file in Directory&quot;
rsNotpolled.Update
Case 13

GoTo endofproc
Case 3265
Case 3211
DoCmd.Close acTable, strtemp
Case 3274
DoCmd.Close acTable, strtemp
Case Else

If MsgBox(&quot;Error occurred &quot; & Err.Number & &quot; for store &quot; & strData & _
&quot; Please note details for investigation&quot;, vbOKOnly, &quot;Error&quot = vbOK Then
End If

End Select
Resume Next
' Resume execution at line after the one that caused the error.

endofproc:
rsParameters.Close
rsNotpolled.Close
rsAddress.Close
myDB.Close

End Sub