Thank you for your post.
Yes, this is possible, and I'll assume your next question is "How?". There is a technical brief available from our web site about External SQL Sources, and I think it would be easier if you read this information first, as I think it will answer most, if not all, of your questions.
I have been using FM pro for the FE for a while, so all of my DSNs are set up, etc. My problem is this. I use a DSN with a R/O user from the SQL database. If any changes are made, it throws an error. As a side note, I would love to trap that error and suppress it. But back to the task at hand, I would love to be able to dynamically make the form r/o or w/r. This way they can browse and not get any error since they can't inadvertantly change data, but then hit an edit button, auth as a R/W user, get a W/R form to edit with, then force them to save that record. After that, I would like to force them back to R/O automatically and lock the browse form from a script controlling this. It is easy for a user to alter data without much effort (hence why I want to have them browse R/O). I want the user to have to push a button to authenticate for that record only, then allow them to make a change, save it and then go back to R/O.
That edit button also allows me to deal with the inherent concurrency issues this setup brings.
How do I, from a script, allow them to reauth to the database for that brief time? That and the form locking (which is admittedly less of a big deal) the part that I am struggling with.
BTW: Thanks for the link to the paper.
Thanks for the additional information.
The function "Get (LastODBCError)" returns a string that shows the error state published by "ODBC standards". Also, "Get (LastError) will return 1408 if there is an error while performing a SQL query, and 1409 if there is no information about the error. You can trap for these errors if you have "Set Error Capture [On]" in your scripts.
You may also want to look at Chapter 8 of the "ODBC and JDBC Guide" - "ODBC and JDBC Error Messages":
Let me know if you need additional information.