I have several subroutines in which I import an excel file, "copy" pertinent records, "paste" them into a Master file and delete the temp table. Before I can "copy" the records I need to assign properties to the field captions in case the Temp fields are named differently than the Master fields. This is the sub on which the error is triggered when I try to DELETE the temp table. I've tried closing every "linked" object but still no luck.
Code is below. Thanks.
Code:
Sub rename2()
'************************************************************************************************************************************
'Definition and Initilization
'************************************************************************************************************************************
Dim db As DAO.Database
Dim recset As DAO.Recordset
Dim fld As DAO.Field
Dim tdf As DAO.TableDef
Dim pr As DAO.Property
Dim fieldname, newfieldname, strpropname As String
Set db = CurrentDb()
Set recset = db.OpenRecordset("temp")
'************************************************************************************************************************************
'Add captions to field names of temp file in case they are inconsistent with those in Master File
'************************************************************************************************************************************
For Each fld In recset.Fields
fieldname = fld.Name
newfieldname = Replace(fieldname, " ", "")
newfieldname = Trim(fld.Name)
Set fld = Nothing
Set recset = Nothing
Set tdf = Nothing
Set pr = CurrentDb.TableDefs("temp").Fields(fieldname).CreateProperty("Caption", dbText, newfieldname)
CurrentDb.TableDefs("temp").Fields(fieldname).Properties.Append pr
CurrentDb().TableDefs("temp").Fields(fieldname).Properties("Caption").value = newfieldname
Next
CurrentDb().TableDefs.Refresh
db.TableDefs.Refresh
Set db = Nothing
Set recset = Nothing
Set tdf = Nothing
Set pr = Nothing
Set fld = Nothing
fieldname = Null
If Not IsNull(newfieldname) Then
newfieldname = ""
End If
If Not IsNull(strpropname) Then
strpropname = ""
End If
DoCmd.Close acTable, "temp"
DoCmd.Close acForm, "ImprtFrm"
DoCmd.DeleteObject acTable, "temp" <---------------Error triggered
End Sub