Run-Time Error 2391

[Readem]

Hi,

I am receiving the above error when trying to transfer spreadsheets from excel to my access database. It says that Field "F1" does not exist in the destination table. I am using Microsoft Office 2010.0

Would someone mind explaining this error and perhaps a means to its resolution? Or is there a better way to do this? I've already checked to confirm that columns to values matches 1:1 between my spreadsheet and the table in my DB. I've posted my code below:

Sub Export_to_Database()
'Export all Temp_DB tables using TransferTable method
    Dim sTempDBPath As String: sTempDBPath = ThisWorkbook.Sheets("Workbook_Settings").Range("WBSettings_TempDBPath")
    Dim wb_SourceFile As Workbook: Set wb_SourceFile = ThisWorkbook
    Dim wb_DestinationFile As Workbook: Set wb_DestinationFile = Workbooks.Open(sTempDBPath)
    ''Create access object
    Dim objAccess As Object
    Set objAccess = CreateObject("Access.Application")
    
    objAccess.OpenCurrentDatabase sDBPath
    
    Dim ws As Worksheet
    Dim sWS_Name As String
    
        For Each ws In wb_DestinationFile.Worksheets
            
            ws.Visible = xlSheetVisible
            sWS_Name = ws.Name
                
            Select Case True
                Case InStr(sWS_Name, "Cover")
                Case Else
                
                    Dim sDBTable As String: sDBTable = "tbl_" & sWS_Name
                    Dim lngLastRow As Long: lngLastRow = ws.Range("A1").CurrentRegion.Rows.Count
                    Dim lngLastCol As Long: lngLastCol = ws.Range("A1").CurrentRegion.Columns.Count
                    Dim sTableRange As String: sTableRange = sWS_Name & "!" & "A2:" & Col_Letter(lngLastCol) & lngLastRow
                    
                    objAccess.DoCmd.TransferSpreadsheet _
                        TransferType:=acImport, _
                        SpreadsheetType:=5, _
                        Tablename:=sDBTable, _
                        fileName:=sTempDBPath, _
                        Hasfieldnames:=False, _
                        Range:=sTableRange
                  
           
            End Select
        Next ws
    objAccess.CloseCurrentDatabase
    objAccess.Quit
    
    ''Empty Objects
    Set objAccess = Nothing
End Sub

Function Col_Letter(lngCol As Long) As String
Dim vArr
vArr = Split(Cells(1, lngCol).Address(True, False), "$")
Col_Letter = vArr(0)
End Function

Thank you for your help; if you have any additional, follow-up questions that need to be addressed, I will be sure to follow-up quickly. Again, thanks!

 

[ParamRay]

Not sure?!
You could see what happens if you use DAO to do the transfer. (Would need to make some adjustments before use.)

Public Sub ExportValues()
  ' Referenced object libraries:
  ' (1) Microsoft Access
  ' (2) Microsoft Office Access Database Engine
  
  Const strDB_PATH As String = "C:\Users\gpeacock\Desktop\Database1.accdb"
  Dim objAccess As Access.Application
  Dim wks As Excel.Worksheet
  Dim strTableName As String
  Dim intNumCols As Integer
  Dim rst As DAO.Recordset
  Dim dbs As DAO.Database
  Dim lngNumRows As Long
  Dim i As Integer
  Dim j As Long
  
  Set objAccess = New Access.Application
  objAccess.Visible = False
  Set dbs = objAccess.DBEngine.OpenDatabase(strDB_PATH)
  
  For Each wks In ThisWorkbook.Worksheets
    strTableName = "tbl_" & wks.Name
    lngNumRows = wks.Range("A1").CurrentRegion.Rows.Count
    intNumCols = wks.Range("A1").CurrentRegion.Columns.Count
    
    Set rst = dbs.OpenRecordset(strTableName, dbOpenDynaset)
    For j = 2 To lngNumRows
      rst.AddNew
      For i = 1 To intNumCols
        rst.Fields(wks.Cells(1, i).Value).Value = wks.Cells(j, i).Value
      Next i
      rst.Update
    Next j
    rst.Close
  Next wks
  
  MsgBox "Export completed.", vbInformation, "Export Values"
  dbs.Close
  objAccess.Quit
  Set objAccess = Nothing
  Set dbs = Nothing
  Set rst = Nothing
End Sub

 

[xenou]

I think if you transfer without field names (hasfieldnames:=false) using transfer spreadsheet, then Access assigns the field names by default (F1, F2, F3, ...).