Data type mismatch in criteria expression

[Madiya122]

I am trying to insert records in a SQL table from Excel sheet with VBA code.
Data contains of a range with all the rows to be inserted.
I am using below code for the same.

Insert INTO [odbc;Driver={SQL Server};Server=myserver;Database=mydb;Uid=abc;PWD=abc].DPR Select * FROM [TempRange]

I am getting error as above.
Various columns in data range is exactly as per the sql table col names.

I am using Excel 2007 with SQL2012.

Kindly help to resolve this error.

Regards,
Madiya

[Ancient Dragon]

Looks like you are attempting to create a connection, nsert some data, and select somore other data all in one statement. Can't do that. You need to std a tutorial that teaches you the correct method of doing those things. Here is a very good one to get you started (see Chapter 13)

[Madiya122]

Well, Not all in one.
I have given onlt insert statement above.
I am opening a connection successfully.
Selecting a range for the data required to insert in the sql table.
Then trying to execute the action with above data and connection opened earlier.
This done with select data from temprange in excel and insert data in select statement into sql table.
This used to work earlier and even now on a test table but not on this perticuler table. Nothing is changed in table structure.

Hope I have clarified properly.

Kindly help.

[Ancient Dragon]

Then you need to post exact code. The code you posted is wothless.

[Madiya122]

Pl find below the exact code I am using.

Thanks for your time and help.

[Madiya122]

Sorry, Missed the code.

Sub test()
    Dim cn As Object
    Dim strSQL As String
    Dim lngRecsAff As Long
    Set cn = New ADODB.Connection
    Dim WB As Workbook
    Set WB = ActiveWorkbook
    strFileName = WB.FullName
    Set cn = CreateObject("ADODB.Connection")
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFileName & ";Extended Properties=""Excel 12.0 Xml;HDR=Yes"";"

    Dim LR As Integer
    Dim CR As Integer
    LR = Range("A1").End(xlDown).Row
    Debug.Print "LR is " & LR
    CR = Range("A1").End(xlToRight).Column
    Debug.Print "CR is " & CR
    Dim rngName As Range
    ActiveSheet.Select
    Set rngName = Range(Cells(1, 1), Cells(LR, CR))
    rngName.Select
    rngName.Name = "TempRange"

    strSQL = "Insert INTO [odbc;Driver={SQL Server};" & _
        "Server=myserver;Database=mydb;" & _
        "Uid=abc;PWD=abc].mytable" & _
        " Select * FROM [TempRange]"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff ', adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff
        Dim RECUPDATED As Integer
        RECUPDATED = lngRecsAff

    Debug.Print strSQL
        cn.Execute strSQL, lngRecsAff
        Debug.Print "Records count: " & lngRecsAff

    cn.Close
    Set cn = Nothing
   MsgBox "Records Updated = " & RECUPDATED & " , Thank you"
End Sub

Pl help.
Thanks for your time invested in my problem.

Regards,
Madiya

[Ancient Dragon]

line 24 is all wrong. the insert command does not include the sql connection stuff. Please read the tutorial I posted earlier.