Post reply

update query fails in access database with SQL Server backend

  • November 8, 2006 at 1:45 am

    #174841

    Hi All,

    I have recently upgraded an access database to SQL Server and there is one query that used to run OK in Access but in the SQL Server version I get the message ''Run Time error 3146 ODBC Failed Driver Manager Invalid Argument value (#0)''

    Here is the query:

    UPDATE tblProject LEFT JOIN tblRecruitmentStage ON tblProject.StudyCodeID = tblRecruitmentStage.StudyCodeID SET tblRecruitmentStage.StudyCodeID = forms!frmProject!StudyCodeID, tblRecruitmentStage.RecruitmentStage = "Other"

    WHERE (((tblProject.StudyCode)=[forms]![frmProject]![StudyCode]));

    Could anyone help??

    thanks

    Steve

  • November 8, 2006 at 2:14 am

    #670494

    Hi,

    In sql server the sentax is slightly different... the query should be like this

    UPDATE tblRecruitmentStage SET tblRecruitmentStage.StudyCodeID = forms!frmProject!StudyCodeID, tblRecruitmentStage.RecruitmentStage = "Other"

    FROM tblProject LEFT JOIN tblRecruitmentStage ON tblProject.StudyCodeID = tblRecruitmentStage.StudyCodeID

    WHERE tblProject.StudyCode=[forms]![frmProject]![StudyCode];

    I think this would slove your problem

    cheers

  • November 8, 2006 at 2:41 am

    #670505

    HI - Thanks for youe reply -

    HOw would this work? - do I just copy and paste the SQL into a new query in Access and replace the old query with this one

    I would like to keep this query as an update query in Access as it is part of a chain of update & append queries that run together when a user clicks a button.

     

    thanks

  • November 8, 2006 at 6:31 am

    #670545

    It would be a best thing for you that you can used a pass through query and make the stored procedure on the SQL Server and used it as a pass through query...

    For this you need to look how to use the pass through query or mail me on my private mail address if you are not able to use that....

    Currently I am not able to have much time due to the job

    thanks

    cheers

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply