Deleting field leads to error 3273 (1 Viewer)

Devon

New member
Local time
Today, 18:34
Joined
Apr 12, 2012
Messages
9
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?

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:

Taruz

Registered User.
Local time
Today, 17:34
Joined
Apr 10, 2009
Messages
168
Hi.. ;)

Delete field in this way.. put below in code ..:

Currentdb.tabledefs("tblAppraisal").Fields.Delete "BU"

.
 

Devon

New member
Local time
Today, 18:34
Joined
Apr 12, 2012
Messages
9
Thank you for your input, that did the trick. All that's left now is converting "BU" to .Fields(XX).Name so I can start using a matrix and loop through all the fields that need to be deleted. Will post the final result later on.
 
Last edited:

Devon

New member
Local time
Today, 18:34
Joined
Apr 12, 2012
Messages
9
It took some time to get things right due to problems with indexes, but I found a way around that. The code could be cleaner here and there, but I'm glad I finally got things to work and have the desired outcome.

Code:
Public Function Appraisal(ApprSource As String, ApprDate As String) As Boolean
On Error GoTo Err_Appraisal

    Dim rstTable As Recordset
    Dim strTable As String
    Dim varField As Variant
    Dim varFieldnr As Variant
   
    DoCmd.Hourglass True
    Application.Echo False
   
    strTable = "tblAppraisal" & ApprDate
    varField = Array(24, 23, 22, 21, 20, 19, 18, 17, 16, 14, 13, 12, 11, 10, 8, 5, 4, 3, 1)
   
    DoCmd.RunSQL "DROP TABLE tblAppraisal" & ApprDate
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, strTable, ApprSource, True
   
    Set rstTable = CurrentDb.OpenRecordset(strTable, dbOpenDynaset)
   
    With rstTable
        'Delete employees with certain contracts or from certain classes
        Do While Not .NoMatch
            .FindFirst "[" & .Fields(10).Name & "] = 'Contract A*' Or [" & .Fields(10).Name & "] LIKE 'Class N*'"
            If Not .NoMatch Then .Delete
        Loop
       
        .MoveFirst
       
        'Delete when appraisal date sooner than expected, unless 1-1-2012
        Do While Not .NoMatch
            .FindFirst "[" & .Fields(13).Name & "] <> #1-1-2012# And ([" & .Fields(15).Name & "] < [" & .Fields(13).Name & "])"
            If Not .NoMatch Then .Delete
        Loop
       
        .Close
    End With
   
    'Delete field when unnecessary, also delete index
    For Each varFieldnr In varField
        CurrentDb.TableDefs(strTable).Indexes.Delete CurrentDb.TableDefs(strTable).Fields(varFieldnr).Name 'Must assume any field can be auto-indexed when importing
        CurrentDb.TableDefs(strTable).Fields.Delete CurrentDb.TableDefs(strTable).Fields(varFieldnr).Name
    Next varFieldnr
   
    Appraisal = True

Exit_Appraisal:
    Set rstTable = Nothing
    Application.Echo True
    DoCmd.Hourglass False
    Exit Function

Err_Appraisal:
    If Err.Number = 3265 Or Err.Number = 3376 Then 'Index or table non-existent
        Resume Next
    Else
        Appraisal = False
        Resume Exit_Appraisal
    End If
End Function
 
Last edited:

Users who are viewing this thread

Top Bottom