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
– Andy G, Commented Sep 20, 2013 at 18:36
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
– Mani Deep, Commented Sep 20, 2013 at 18:44
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
– nsjkd, Commented Sep 20, 2013 at 18:44

HansUp · Accepted Answer · 2013-09-20 18:56:59Z

3

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")

answered Sep 20, 2013 at 18:56

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

nsjkd Over a year ago

Excellent! That clears that error. Appreciate the help. My problem now is that my UPDATE statement seems to be off. I'm not that experienced with SQL but I essentially scraped this syntax from others online. Is it not appropriate for what I'm aiming to do? (Error 3144 SYNTAX Error in UPDATE statement). I'm essentially trying to add a new record for each row in Excel where there is data according to the above conditions.

HansUp Over a year ago

Add Debug.Print strSQL to your code before db.Execute strSQL. Then run the code and look at the failing UPDATE statement in the Immediate window. You can copy the statement text and paste it into SQL View of a new Access query for testing. If you need further help, submit a question with the UPDATE SQL and the full text of any error message.

HansUp Over a year ago

It just now sunk in that you said "add a new record". In that case, UPDATE is inappropriate because it can only change existing records. You need an INSERT to add a new row.

nsjkd Over a year ago

Beautiful. Thanks so much for the direction; I got it to work using INSERT INTO... VALUES... The query now works and has added all the relevant items from Excel to Access. Best answer!

HansUp Over a year ago

You're welcome. Sorry I was so late to notice UPDATE vs. INSERT. Um ... I was focused on the DAO error. :-)

Collectives™ on Stack Overflow

SQL update query to add Excel Data to Access

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related