You need

sq = "Insert into marc_temp_excel (Policy_id, Policy_desc) values ('" & _
cellValue1 &"', '" & cellValue2 & "')"


--

HTH

RP
(remove nothere from the email address if mailing direct)


"marcmc" wrote in message
...
I keep getting a runtime error stating a syntax error converting the
varchar
value ' & CellValue1 & ' to a column of datatypr int. The value is 1 and
my
SQL table in dataType Integer. I have also tried with various removal of '
and & from the insert statement. Any ideas of the correct syntax?

Private Sub btnUpdate_Click()

Dim c As ADODB.Connection
Dim r As ADODB.Recordset
Set c = New ADODB.Connection
Dim rw As Integer
Dim cellValue1 As Integer
Dim cellValue2 As String
Dim ws As Worksheet

strCn = "Server=ServerName;Database=dbName;User Id=mm;password=ma"

c.Provider = "sqloledb"
c.Open strCn

rw = 1
Do While Len(Cells(rw, 1)) 0

Set ws = Sheets("Pivot")

cellValue1 = ws.Range("A" & rw).Value
cellValue2 = ws.Range("B" & rw).Value

MsgBox (cellValue1)
MsgBox (cellValue2)

sq = "Insert into marc_temp_excel (Policy_id, Policy_desc) values
('& cellValue1 &', ' & cellValue2 & ')"

Set r = c.Execute(sq)
rw = rw + 1
Loop

c.Close
Set c = Nothing
MsgBox ("marc_temp_excel Table Successfully Updated.")
End Sub