@sonic8
Although as you said you don't want to engage in this discussion because you've seen too many of them but :
1- I really appreciate your input.
2-There's a lot of tests that a FE may goes through before saving a new record. The date I explained was just
an example because I didn't want to go through details. There are a lot more (and maybe rare) tests that our FE does and
MAYBE is impossible or hard to achieve with server permission system.
For example we receive the order for manufacturing the same product from 5 different customers. So before allowing the user to save a new order, there's a very complex check that is done to be sure CustmoerFK is selected correctly. It involves checking
a- previous orders from the same customer,
b- the type of received OrderNo (each company has its own mix of numbers and digits),
c- the order quantity comparing to previous quantities of the same product for the same customer, (they normally order in same lot)
d- Seiban (which is an almost standard No. and most companies here use it as an identification of the project they'll plan to use the product. There's no equivalent word in English, and I've never seen this in other countries),
e- and some other checks.
If this test fails the user is asked to double check if the CustomerFK (combobox) is selected correctly. The user either confirms the selection or corrects the input data, and only then a new record is saved.
I really don't know how you do this test with SQL Server permission system or stored procedure and how you send a confirmation message to user to be sure if
the data is OK even if it's different with what is expected. It means a different order from what is expected is possible, but it needs the user's confirmation.
That's only a part of the checks. Our FE has more checks to do with Deliveries, Shipping Addresses, Payments etc. that I don't believe it's possible to have the same result with what you explained above. For example we normally need 5 days for manufacturing 10,000 parts and users are not allowed to enter delivery less than today+5 days. In case the Customer Order Sheet says Emergency, a passcode must be issued by the production line manager and only by using this passcode then today+3 days delivery will be accepted by beforupdate event.
3- Even if everything is possible with what you explained above, I still don't think it's a good idea to allow someone create a linked table to SQL Server database even if the server is set to protect the data correctly. As I explained in my previous posts, intentionally or by mistake s/he may delete an order (without a correct check by vba to be sure) and no-one can prevent him (even sql rules) because his account in sql server is set to be able to Add-Edit-Delete data in tables.
4- Again Even if you are able to protect your sql server database perfectly, I think what
@GPGeorge explained
here is a lack of security level for sql server.
5- For now
@Pat Hartman's suggestion covers the answer to my question (thanks to her). But I will read your link tonight in the way back home in train.
Again I really appreciate taking your time and replying.