Update sql INSERT statement with WHERE caluse from access to make an UPDATE statement

access 2010 vba

I'm executing an sql statement from access to update a sql server table.
The query below is an older query, but I need to be specific about which row i will be updating.
So i need a where clause added please.

The field i'm using for criteria is "proposal_id"
So i need to pass a parameter for this query in the WHERE clause.?

Can i do this within the statement below, just by adding the parameter via Access.
 I need to make this an UPDATE statement ?


sQuery = "EXECUTE INSERT INTO [WORKFLOW].[dbo].[survey_results] ([RACF],[QueryDate],[ProcRun],[SearchTerm]) Values('" & racfid & "',getdate(), 2,'" & Nf1 & "');"


Thanks
fordraiders
LVL 3
FordraidersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

omgangIT ManagerCommented:
INSERT INTO is an insert/append query not an update.  So, Yes, you need to make this an update query.

UPDATE [WORKFLOW].[dbo].[survey_results]
SET {the fields you wish to update}
WHERE [proposal_id] = TheIDValue

OM Gang
0
Brendt HessSenior DBACommented:
Your new query should look more like this:

sQuery = "EXECUTE UPDATE [WORKFLOW].[dbo].[survey_results] SET RACF = '" & racfid & "', QueryDate = '" & getdate() & "', ProcRun = " & 2 & ", SearchTerm = '" & Nf1 & "' WHERE proposal_id = '" & propID & "'"

Of course, the propID name is just a guess at what you might call the variable used for sending that value in.

EDIT: If you make your code a bit more complex, you could only pass in values that are changed - although that may not save you much time. For example, if you only changed the SearchTerm value, you could omit RACF, QueryDate (unless this must be updated every time), and ProcRun (unless this must be updated every time) when you put sQuery together.

Also, do you really need to pass in the GetDate value? Unless the two systems are working on different time zones, you should be able to replace
, QueryDate = '" & getdate() & "',
With
, QueryDate = getdate(),
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FordraidersAuthor Commented:
Thanks !
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.