ADODB - Syntax error when trying to open a recordset when using the adCmdTable parameter

Hi

I am trying to update a record in a sql server database table and when i try and open up a recordset using adCmdTable parameter i get the following syntax error.

Err.number: -2147217900

Err.Description: Incorrect syntax near the keyword 'SET'

The interesting part is this error occurs when trying to process the open method and appears to be related to the sql variable content

adoRS.Open sql, OpenResConn, adOpenKeyset, adLockOptimistic, adCmdTable

If i remove the adCmdTable parameter then the statement will process but the adOpenKeyset and adLockOptimistic settings will be default and not as specified. As i am trying to update a value in the table, the default settings are read only so i get an error relating to be unable to update due to lock state.

Additional infomation:

DB Version: SQL Server 12

Table:  the blob table has the ID column set as the primary key

BLOB field type is image

the adCmdTable parameter is correctly being returned as int 2.

Here is the code i am trying to run.

Dim OpenResConn, sql, sFileName, adoRS, iDriverID

iDriverID = 4

sFileName = "C:\Testing\Errors\TestUncertain.png"

Set OpenResConn = Sys.OleObject("ADODB.Connection")

OpenResConn.ConnectionString = "Provider=SQLNCLI11;" _

& "Server=auto-test-res;" _

& "Database=Results;" _

& "user id ='Results';" _

& "password='Password';" _

& "DataTypeCompatibility=80;"

OpenResConn.Open

sql = "SET NOCOUNT ON; "

sql = sql & "insert into BLOBCopy (DriverID, Filename)"

sql = sql & "values (" & iDriverID & ", '" & sFileName & "');"

sql = sql & "SELECT SCOPE_IDENTITY() as ID;"

Set adoRS = Sys.OleObject("ADODB.RecordSet")

Call adoRs.Open(sql, OpenResConn)

blobID= adoRs("ID")

adoRs.Close

'Open a recordset of just the BLOB column for the row we just created

sql = "SET NOCOUNT ON; "

sql = sql & "select BLOB from BLOBCopy where ID = " & blobID

adoRS.Open sql, OpenResConn, adOpenKeyset, adLockOptimistic, adCmdTable

'create a stream and populate it with the specified file

Dim adoSt

Set adoSt = Sys.OleObject("ADODB.Stream")

adoSt.Type = 1'adTypeBinary

adoSt.Open

adoSt.LoadFromFile sFileName

'Write the stream to the recordset and update it

adoRs.Fields("BLOB").Value = adoSt.Read

adoRs.Update

adoSt.Close

adoRs.Close

Does anyone have any ideas as to why this isnt working as intended?