SQL update query to add Excel Data to Access

Question

I've been searching for a direct solution, but haven't found anything quite like what I'm attempting to do on this board. I have an Excel worksheet that has several non-contiguous lists of bonds for different companies (think 5 bonds for one company, 3 fully empty rows, then another list of 6 bonds for another company, 5 fully empty rows, etc. etc.).

I'm trying to write an SQL update query that will directly update an Access table in .accdb format. I have fields that have the same name as the column headers in Excel, with the same data.

I need to perform this logic: where range A1 & B1 & C1 are not blank, add a new record to the table that shows A1 & B1 & C1 as fields [Ticker], [Coupon], [Maturity]. Where those cells ARE blank, move to the next row.

Can someone help evaluate my code? I'm getting an error 3343 at the point where I specify "Set db".

My preliminary code is below (scraped together from what I could find online as far as interfacing with Excel and SQL commands):

Sub UpdateDatabase()

    Dim x As Integer
    Dim strSQL As String
    Dim db As Database
    Dim dbLocation As String
    Dim objConnection As Object

        Worksheets("Bonds Clean").Activate
        Range("A6").Select

    dbLocation = "c:\Folders\Workflow Tables.accdb"

    Set objConnection = CreateObject("DAO.DBEngine.36")
    Set db = objConnection.OpenDatabase(dbLocation)


        For x = 1 To Range(Selection, Selection.End(xlDown)).Rows.Count

            If Not (Selection.Value = "") Then

                strSQL = "UPDATE tblBonds_Temp SET"
                strSQL = strSQL & "Ticker =" & Chr(34) & Selection.Offset(0, 1).Value & Chr(34) & ","
                strSQL = strSQL & "Coupon =" & Chr(34) & Selection.Offset(0, 2).Value & Chr(34) & ","
                strSQL = strSQL & "Maturity =" & Chr(34) & Selection.Offset(0, 3).Value & Chr(34) & ";"

                db.Execute strSQL

            Else
            End If

        Selection.Offset(1, 0).Select
        Next


End Sub

Yes, a quick Google suggests that you might need to update your DAO driver. — Andy G
i think the problem raises when u use space in defining path plz try changing the file name without space dbLocation = "c:\Folders\Workflow Tables.accdb" //here Workflow Tables — Mani Deep
I've switched between different libraries including the most recent "Microsoft Office 14.0 Access database engine Object Library" for 2010, but to no avail. — nsjkd

HansUp HansUp · Accepted Answer · 2013-09-20T18:56:59

DAO.DBEngine.36 is for DAO 3.6 which is suitable for MDB format database files. However, your database is ACCDB format which means that DAO 3.6 won't work. You must use the newer DAO instead.

'Set objConnection = CreateObject("DAO.DBEngine.36")
Set objConnection = CreateObject("DAO.DBEngine.120")

SQL update query to add Excel Data to Access

1 Answers