Results 1 to 6 of 6
  1. #1
    cfrancisco523 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Jun 2017
    Posts
    3

    Runtime error 3211, delete table, append DAO properties

    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

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Dim fieldname, newfieldname, strpropname As String
    This does not do what you think.
    It actually Dims fieldname, newfieldname as Variants and strpropname as string.

    I don' know that this has anything to do with your error.

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I would not delete the temp table. I would keep it permanent and import records to the existing table then delete the records at end of process. This assumes the import structure is the same every time.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    cfrancisco523 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Jun 2017
    Posts
    3
    Thanks June. Unfortunately the import structure will not be the same. Some of the field names in the temp file have spaces in them. That is why I created the caption property for the fields with trimmed field names.

    When I try to delete the fields in tabledefs instead of deleting the table I still get the 3211 error.

    Any help would be great.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    By same structure, I mean the Excel file is always the same. Fields can be different from field names used in table. The differences can be handled in code, which is what you are attempting, just a different approach from my suggestion.

    If the Excel fields are not the same structure every time, then how do you know which fields in the master table correspond?

    Is the Excel file simple enough structure can set a link to it?

    Why set the fld, recset, tdf variables to nothing within the fld loop? I commented them and still get the same results. Captions are created but the spaces are not removed and I get the 3211 error.

    Okay, have to close the recordset, then no more error and table deletes.

    recset.Close

    Also, why are you trimming fld.Name instead of newfieldname? Not sure Trim is even needed.

    newfieldname = Trim(Replace(fieldname, " ", ""))
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    cfrancisco523 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Jun 2017
    Posts
    3
    Thanks again June. I actually got it to work by deleting the table in the first subroutine, before I import the file.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 11-13-2016, 07:12 PM
  2. Error 3211 - table in use by another
    By D'Anconia in forum Forms
    Replies: 4
    Last Post: 05-19-2014, 05:28 PM
  3. Replies: 16
    Last Post: 06-18-2012, 02:27 PM
  4. Error 3211
    By Phred in forum Programming
    Replies: 1
    Last Post: 01-25-2012, 10:29 PM
  5. Error 3211 Object cannot be locked
    By TheShabz in forum Forms
    Replies: 9
    Last Post: 08-23-2011, 12:05 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums