I'm importing an Excel workbook into a table. Some records are removed, which works fine. But I stumble upon error number 3273 when I try to delete a field. Giving the name of the field leads to the same error as letting Access grab the name via .Fields(XX).Name.
What am I overlooking?
Overview
Some question the deletion of fields, so here's an overview to explain why I need to do this.
1. Use information system X to generate a list
2. Import the list in Access
3. Tidy up the list by removing unwanted rows and columns (voila!)
4. Do some calculating
5. Share list and calculating results with colleagues
What am I overlooking?
Code:
Public Function Appraisal(ApprSource As String, ApprDate As String) As Boolean
On Error GoTo Err_Appraisal
Dim rstTable As Recordset
DoCmd.Hourglass True
Application.Echo False
DoCmd.RunSQL "DROP TABLE tblAppraisal" & ApprDate
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblAppraisal" & ApprDate, ApprSource, True
Set rstTable = CurrentDb.OpenRecordset("tblAppraisal" & ApprDate, dbOpenDynaset)
With rstTable
Do While Not .NoMatch
.FindFirst "[" & .Fields(10).Name & "] = 'Contract' Or [" & .Fields(10).Name & "] LIKE 'Class*'"
If Not .NoMatch Then .Delete
Loop
.MoveFirst
Do While Not .NoMatch
.FindFirst "[" & .Fields(13).Name & "] <> #1-1-2012# And ([" & .Fields(15).Name & "] < [" & .Fields(13).Name & "])"
If Not .NoMatch Then .Delete
Loop
[B][COLOR=Red].Fields.Delete "BU"[/COLOR][/B]
.Close
End With
Appraisal = True
Exit_Appraisal:
Set rstTabel = Nothing
Application.Echo True
DoCmd.Hourglass False
Exit Function
Err_Appraisal:
If Err.Number = 3376 Then 'Table non-nexistent
Resume Next
Else
Appraisal = False
Resume Exit_Appraisal
End If
End Function
Overview
Some question the deletion of fields, so here's an overview to explain why I need to do this.
1. Use information system X to generate a list
2. Import the list in Access
3. Tidy up the list by removing unwanted rows and columns (voila!)
4. Do some calculating
5. Share list and calculating results with colleagues
Last edited: