CODEWITHSUNDEEP
sqlexcelvbams-accessms-access-2010
nsjkd
nsjkd

Reputation: 43

SQL update query to add Excel Data to Access

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

Upvotes: 1

Views: 3870

Answers (1)

HansUp
HansUp

Reputation: 97101

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

Upvotes: 3

Related Questions