CODEWITHSUNDEEP
vbaexcelms-accessinsertwhere-clause
TSQL_Newbie
TSQL_Newbie

Reputation: 821

Excel Insert Into Access Table from Excel defined Table with a Where Clause

A little stuck. I have a an excel table with lets say the following columns [Date],[Name],[PostCode],[City]

And an access table with the same column names.

I'm trying to insert the excel data table into the access table but want to apply a WHERE clause so that it ignores dates already in the access table.

I can happily inset all data into the access table but cannot apply a WHERE clause successfully ignore the existing dates. And annoyingly I cant reference the actual table name correct so I am referencing the sheet (but works fine).

This is the code I have so far:

Sub TestInsert()

    Dim cnn As ADODB.Connection

    Dim dbPath As String
    Dim xlPath As String
    Dim uSQL As String

    dbPath = "C:\Test\TestDB.accdb"
    xlPath = "C:\Test\TestXL.xlsm"

    'Set Connection string
    Set cnn = New Connection
    cnnstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath

    'Open connection to db
    cnn.Open cnnstr

        uSQL = "INSERT INTO tbl_ApolloBrochurewareReportTEST " _
            & "SELECT * FROM [Excel 12.0;HDR=YES;DATABASE=" & xlPath & "].[Sheet1$]"


        cnn.Execute uSQL

    cnn.Close

End Sub

Any input or knowledge would be great, thank you.

Upvotes: 0

Views: 2588

Answers (1)

Gustav
Gustav

Reputation: 55816

You should be able to expand the SQL expression:

    uSQL = "INSERT INTO tbl_ApolloBrochurewareReportTEST " _
        & "SELECT * FROM [Excel 12.0;HDR=YES;DATABASE=" & xlPath & "].[Sheet1$] As T " & _
        "WHERE T.Date NOT IN (SELECT [Date] FROM tbl_ApolloBrochurewareReportTEST)"

Upvotes: 1

Related Questions