I know there are obvious inherent risks of giving a user a live/raw table that is ODBC linked into A2007, however without being able to do so in perhaps a FORM format - it creates a massive amount of work-arounds, creating tables, sub-system dependency, even writing back info to SQL... on & on. To me this is madness at it's finest! Why not go direct from A2007 edits right into SQL???
Perhaps I'm the mad one though????
I know I'm probably not alone in my next thought here, but here is my thinking:
1. In A2007, with a full permission table ODBC linked from SQL, I know I can create a Query and make a form to be it's record source, then I can fully edit the information in A2007... as it's being edited, the information going to the SQL table is also updated either real time or as I leave the current record.
2. I know I'd have to put a couple guard rails up in the Form, such as: "NO" for allowing additions or deletions in the FORM prop's, also in the FIELD prop's I'd put a lock on particular fields (like primary key fields) to be "YES" LOCKED. (Depending if this is good practice or not, I think perhaps it would be cool to be able to ADD new records... but for now the FEAR is too great!)
3. I also know that any bad character entries/garbage trying to go into any fields will be halted by SQL table rules and not what A2007 says. (My belief is that it would be very hard to corrupt or damage the SQL database or table, aside from a crazy delete query or update query that has the right data type, however writes a bunch of garbage in over good data.)
4. One area of fear for me is if an entry or edit from A2007 that's linked to SQL has any type of dependencies or cascading updates that would not get triggered, therefore leaving some un-populated information for other tables etc...? If so, how can I search for what is related to that field, so I can address the missing updates??
Anyways, that's my jist.... I may be waaay off in my thinking, but would like to know & consider or unconsider the possibilities, or at minimum develop some guardrails so as not to create a huge mess.
Thanks for any insights!