Intermittent Access error 3420 "Object invalid or no longer set."

Question

any one can tell me please where is the Problem with my code Its working 3 times from 10 times , where is the Problem the procces its Import a Excel file and delete the first and the second Rows after insert new row and edit the names in the row and after Inputbox but i think the Problem is here

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "ps", imyDateiname, True

Private Sub Command50_Click()

 DoCmd.SetWarnings False
    Const msoFileDialogFilePicker As Long = 1
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    Dim selectedFilter As String
    Dim imyDateiname As String
    With fDialog
        .AllowMultiSelect = False
        .Filters.Clear
                .Filters.Add "Excel", "*.xlsx", 1
        .Show
        If .SelectedItems.Count = 0 Then
            imyDateiname = ""
        Else
            imyDateiname = Dir(.SelectedItems(1))
        End If
        selectedFilter = .FilterIndex
    End With
    If imyDateiname <> "" Then
        Dim oExc As New Excel.Application

        With oExc    
            .Workbooks.Open "C:\Users\" & Environ("UserName") _ 
                                 & "\Desktop\" & imyDateiname

            .Rows("1:2").Delete
            .Rows("1:2").EntireRow.Insert
            .Worksheets("ps").Columns("B").Replace _
                   What:="-", Replacement:=" ", _
                   SearchOrder:=xlByColumns, MatchCase:=True
        End With

        With oExc
              Cells(1, 1).Value2 = "Ebene"
              Cells(1, 2).Value2 = "OrgEinheit"
              Cells(1, 3).Value2 = "Titel"
              Cells(1, 4).Value2 = "PersNr"
              Cells(1, 5).Value2 = "Geburtsdatum"
              Cells(1, 6).Value2 = "Eintrittsdatum"
              Cells(1, 7).Value2 = "Befristungs"
              Cells(1, 8).Value2 = "Beginnalter"
              Cells(1, 9).Value2 = "Beginnfrei"
              Cells(1, 10).Value2 = "WK2"
              Cells(1, 11).Value2 = "WT"
              Cells(1, 12).Value2 = "Kostenstelle"
              Cells(1, 13).Value2 = "Schlüssel"
              Cells(1, 14).Value2 = "Tätigkeitsbezeichnung"
              Cells(1, 15).Value2 = "IRWAZ"
              Cells(1, 16).Value2 = "IstAK"
              Cells(1, 17).Value2 = "BelGrp"

            .ActiveWorkbook.SaveAs "C:\Users\" & Environ("UserName") _
                      & "\Desktop\" & imyDateiname
            .Quit
        End With   

        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
              "ps", imyDateiname, True

        Dim str As String
        str = InputBox("INSERT THE NUMBER OF THE MONTH PLEASE")
        execQry "upd_TPS_Monat", str
        Me.Refresh
        MsgBox "DONE YOUR DATA ARE READY!"
   End If
End Sub**

re: *"i think the Problem is here"* - The first thing we need to know is precisely which statement is failing. Is the `DoCmd.TransferSpreadsheet` statement highlighted when you click "Debug" after the error occurs? — Gord Thompson, Mar 18 '16 at 15:29
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "ps", imyDateiname, True — Homsimando, Mar 19 '16 at 17:11
Looking at the code again, your variable `imyDateiname` contains only the file name, without path. So your `TransferSpreadsheet` relies on Access having its "current directory" at the path where the file is stored. This could cause problems too. You should supply the full file path. — Andre, Mar 20 '16 at 18:12
ist working better now but i still have new error 462, the remote Server machine doses not exist or is unavailable, and i got too the error 3274 External table is not in the expected Format — Homsimando, Mar 21 '16 at 10:22

score 0 · Accepted Answer · edited May 23 '17 at 12:31

0

You should first Close/Quit the Excel Object, then set it to nothing.

Set oExc = Nothing

Please see this Post by Bob Larson.

The Excel Object may still keep hold of the file, Setting the Object to Nothing enhances garbage collecting, witch lets go of the file, and allows Access import it's contents. Edit: Please see this reference about Garbage collecting in VBA.

If nothing else, this Line of code will add some nano Seconds, and let the import run with no errors.

edited May 23 '17 at 12:31

Community

1
1

answered Mar 20 '16 at 13:52

marlan

1,485
1
12
18

Some advice: If all you want is to manage field names in Access, import the data with _HasFieldNames_ Parameter = False, then use 'TableDefs("ps").Fileds(FiledOrdinalNumber).Name = strFiledName', to manage field names within Access. _FiledOrdinalNumber_ should be Coilumn number in your code. – marlan Mar 20 '16 at 14:36
This should be `Set oExc = Nothing`. And something went wrong with the first link. – Andre Mar 20 '16 at 18:09
Thanks, Andre, I fixed the answer. – marlan Mar 20 '16 at 19:19
ist working better now but i have new error 462, the remote Server machine doses not exist or is unavailable, and i got too the error 3274 External table is not in the expected Format – Homsimando Mar 21 '16 at 10:22
As for error 462, well, this is why I tend to avoid using Excel object... how do you see my advice in the earlier comment? if you want to manage field names in Access, why not manage them from access? – marlan Mar 21 '16 at 12:29
As for Error 3274, see if [this](http://stackoverflow.com/questions/21385438/importing-xls-to-access-mdb-external-table-is-not-in-the-expected-format) helps you. Are you sour you SaveAs to the right format? where do you decide this? at what environment are you running? the file extension does not determine file format. Please see [here](http://www.access-programmers.co.uk/forums/showthread.php?p=1432228#post1432228) some code Iv'e written to manage Excel versions, for importing xlsx files to Acc 2003 mdb file. – marlan Mar 21 '16 at 12:38

Intermittent Access error 3420 "Object invalid or no longer set."

1 Answers1