Hi,

I'm trying to insert data from a worksheets into a sql database over an ADODB connection. I can get the connection to work but I keep getting a run time error 438 Object doesn't support this property or method. I thin kit is the sql query that is the problem but I can't figure it out. I've attached a screen shot of the datasheet I'm using with some test data and I've removed the connection details from the code. Any ideas?Capture.PNG

Sub InsertJT()
Application.ScreenUpdating = False:   Application.DisplayAlerts = False
Dim wb As Workbook, Source As Worksheet

Dim cnn As ADODB.Connection
Dim uSQL As String
Dim rngName As Range
Set wb = ActiveWorkbook
Set Source = wb.Worksheets("DUAL")

uSQL = "INSERT INTO dbo.JT_SampleResults(ESG_ReportID, ESG_ID, ESG_Site, ESG_Description,SampleDate, SampleMethod,SampleUnits, " & _
        "SampleType, STS_SampleType, SampleReading, DetectionLimit, FileName, DateAdded)" & _
        " SELECT a.[column10], a.[column11], a.[column12], a.[column13], a.[column8], a.[column14], a.[column15], " & _
        "a.[column16], a.[column17], a.[column18], a.[column19], a.[column20], a.[column2] FROM '" & Source & "' AS a " & _
        "LEFT JOIN dbo.JT_SampleResults AS b on a.ESG_ID = b.ESG_ID WHERE b.ESG_ID IS NULL"

Set cnn = New ADODB.Connection

cnn.Open cnnstr



cnn.Execute uSQL
cnn.Close
Set cnn = Nothing


End Sub