Exporting data from excel to access-VBA

Question

I am trying to upload a dataset from excel to an access database using the following code.

However i keep getting the Run time error 3078- Application defined or object defined error. i am using the dao for this and have added the follwing references:

Microsoft office 16.0 access database engine object library, Microsoft access 16.0 library.

i am unable to add the Microsoft DAO 3.6 object library (Name conflicts with existing module, project or object libary

here is the code:

Sub access_upload()

Dim strMyPath As String, strDBName As String, strDB As String
Dim i As Long, n As Long, lLastRow As Long, lFieldCount As Long
Dim daoDB As DAO.Database
Dim recSet As DAO.Recordset

strDBName = "Database8.accdb"
'presume to be in the same location as the host workbook:

 strMyPath = ThisWorkbook.Path

 strDB = strMyPath & "\" & strDBName
 Set daoDB = DBEngine.Workspaces(0).OpenDatabase(strDB)
 Dim ws As Worksheet
 Set ws = ActiveWorkbook.Sheets("Sheet2")
 Set recSet = daoDB.OpenRecordset("Database8")

 lFieldCount = recSet.Fields.Count
 lLastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row

 For i = 2 To lLastRow
 recSet.AddNew
 For n = 0 To lFieldCount - 1
 recSet.Fields(n).Value = ws.Cells(i, n + 1)
 Next n
 recSet.Update
 Next i

 recSet.Close
 daoDB.Close

 Set daoDB = Nothing
 Set recSet = Nothing

 End Sub

Please let me know what i am missing here. Thanks!

Yup, but in your OpenRecordset, you need to enter the table name you want to insert the data into, not your database name — Erik A

ASH ASH · Accepted Answer · 2017-09-25T02:37:08

From Excel to Access . . .

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean

' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = False

' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
strPath = "C:\Documents\"

' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "tablename"

strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
      strPathFile = strPath & strFile
      DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
            strTable, strPathFile, blnHasFieldNames

' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
'       Kill strPathFile

      strFile = Dir()
Loop

Exporting data from excel to access-VBA

2 Answers