by I am converting an Access database and it's MFC C++ application to SQL
Server version 7.0. I used the Enterprise Manager to import the existing
Access database into SQL-Server. Now my SQL SELECT's and INSERT's work
fine, but the UPDATES and DELETES throw the above CDaoException error.
I've written a small demo app to test this problem, and it's consistent
across all tables. The demo SQL is extremely simple: "DELETE FROM
Teams_Time_Card WHERE TaskID=700", for example.
I've noticed that queries like this work fine on the PUBS sample database
that ships with SQL-Server, which makes me suspect a setup/security issue
with my target database. However, the queries work fine when using
ISQLW.exe, which makes me suspect an ODBC problem.
The connection string I'm using is:
"ODBC;UID=sa;PWD=;SERVER=Lear;DATABASE=teams2;driver={SQL Server};". It
makes no difference who I log in as; all users / groups have the same
problem.
Another curious piece: I spent an afternoon severely tweaking the
rights/priviledges and nothing I did seemed to have any effect. I grant
everybody full access to the whole wazoo, and there is no perceivable
change. I pull all priveledges, and for some reason everyone can still
SELECT and INSERT. I suppose I'm doing it wrong, but it seems
straightforward to me.
Also, (I doubt if this is related) date-decorating is the same as in Access.
I was expecting to have to switch to quotes (as in '05/23/1999') but instead
found that I needed Access-style pound-signs (as in #05/23/1999#) -- at
least in the SELECT. INSERT seems happy with either quotes or pound-signs.
I don't know about UPDATE or DELETE. I don't really care what I use just as
long as it's consistant -- I'm only mentioning it here on the off chance
that it indicates some setup problem.
Any help you could throw my way would be appreciated.
Thanks in advance,
Alan Farmer.