access / msmsql update query fails
Hello,
I have this sql update that does not work. The dbo table is ms sql, and tblMembers is local access.
Error: Syntax Error (missing operator)
UPDATE dbo_Customer
SET dbo_Customer.secFirstName = tblMembers.secondaryfirstn
INNER JOIN tblMembers
ON dbo_Customer.MembershipID = cstr([tblMembers].[Member_
Where dbo_Customer.customerID=59
i figure it has to be simple, but i have fiddled with this for too long.
I have this sql update that does not work. The dbo table is ms sql, and tblMembers is local access.
Error: Syntax Error (missing operator)
UPDATE dbo_Customer
SET dbo_Customer.secFirstName = tblMembers.secondaryfirstn
INNER JOIN tblMembers
ON dbo_Customer.MembershipID = cstr([tblMembers].[Member_
Where dbo_Customer.customerID=59
i figure it has to be simple, but i have fiddled with this for too long.
ASKER
It still complains of syntax error.
Press
Press
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Works great. Did not occur to me to fore go the join all together.
Press
Press
ASKER
Worked great
Fwiw, I think that Access joins need to be directly on the fields, not functions. I don't have a database handy to verify that, though.
ASKER
more fwiw, the select statement with that join does work.
Thanks for checking that.
UPDATE dbo_Customer
SET dbo_Customer.secFirstName = tblMembers.secondaryfirstn
FROM dbo.Customers
INNER JOIN tblMembers
ON dbo_Customer.MembershipID = cstr([tblMembers].[Member_
Where dbo_Customer.customerID=59
Kelvin