Insert into table using VB

[chris-uk-lad]

Hi all,

Im trying to insert a vlue into a table where the corresponding cell in another column = 'LIVE'

ObjConn.Execute "INSERT INTO tblRESULTS (REF) VALUES '" & strREF & "' WHERE (ACTION) = 'LIVE'"

Whats wrong with my syntax? Thanks

 

[pbaldy]

I'm confused by "where the corresponding cell in another column = 'LIVE'". Since you're inserting a new record, existing records are irrelevant. Do you really want to modify an existing record? Your syntax is wrong because a VALUES clause wouldn't have a WHERE clause. You could use the SELECT clause which would allow it.

 

[chris-uk-lad]

I'm confused by "where the corresponding cell in another column = 'LIVE'". Since you're inserting a new record, existing records are irrelevant. Do you really want to modify an existing record? Your syntax is wrong because a VALUES clause wouldn't have a WHERE clause. You could use the SELECT clause which would allow it.

I currently have

ObjConn.Execute "INSERT INTO tblRESULTS (REF) VALUES (" & strREF & ")"

Which works fine for adding a value to a single column on a blank table.

What i need is to update my table in a cell in column REF where column ACTION has a cell that shows its value as LIVE

So, in sql:

update tblRESULTS set REF = '001' where ACTION = 'LIVE'

But i need this in VB for an Access Table. The REF is a unique ID

 

[pbaldy]

Well, you basically did it correctly but used the wrong query. You want the UPDATE query, not the INSERT query.

 

[chris-uk-lad]

Could you show me the basic layout of the update statement? ive tried it with the SQL sequence but doesnt work for me

 

[pbaldy]

You already have the layout:

update tblRESULTS set REF = '001' where ACTION = 'LIVE'

so

ObjConn.Execute "update tblRESULTS set REF = '" & strREF & "' where ACTION = 'LIVE'"