Inserting Records into SQL Server

[woodyinoz]

Hi all,

I have the following code which should take the information held in column A and input it into a sql server table:

Sub InsertRecords()
Dim DB As DAO.Database
Dim stList As String
Dim R As Range

Set DB = DBEngine.OpenDatabase("SQLSvr", _
      dbDriverNoPrompt, True, _
      "ODBC;DATABASE=DevLog;DSN=SQLSvr;uid=sa;pwd=password")
' may need other parameters?
Set R = ActiveWorkbook.Sheets("Sheet1").Range("A2") ' first ProdNo

Do Until IsEmpty(R)
If stList <> "" Then stList = stList & ", "
stList = stList & R.Value
Set R = R.Offset(1)
MsgBox stList
Loop
DB.Execute "INSERT INTO Test (TestValue) VALUES (" & "'" & stList & "'" & ")"
DB.Close
Set DB = Nothing
End Sub

However, I am getting an error on the insert into line which says:

'Cannot update 'TestValue'; field not updateable.'

This isn't the case as I can update the field.

Can anyone point me in the right direction?

Thanks

Woody

 

[woodyinoz]

Sorry, ignore this! I've cracked it!