zappaifilus
asked on
FEAR of linking SQL Table to Access 2007 database... (Need some perspective/wisdom/philosophy here.)
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!
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!
If you grant users direct access to tables then you must accept that they can circumvent any protection you build into your forms and application. They can just bypass your application completely by making their own connections into the database. Only grant users access to tables if you are sure you want to allow that.
ASKER
Hi MP,
Here's the overall situation:
We committed to a software business package called "Everest" - it is really pretty cool! However, as no software company can (unless the company spends booko denero!) make a complete software business solution that is going to fit our company exactly the way we want to interact with it for every nook and cranny situation.
Everest covers a great deal and does overall an A+ with all the data etc... they use SQL 2005 database...
Over the last 10 years, I've been pretty much been an Access peon, however I really am in awe over the last couple years of all the SQL stuff and learning more daily.
For some situations/A2007 databases, I'd like them to have permission to at minimum edit records (then there is no back and forths/imports & exports etc...) (so for that, I use the USER DSN and make sure they have authority, so they can edit) Right now I am the only one I trust in connecting my databases this way, and am entertaining allowing more permissions for others so they can edit
... for read only situations we use a SYSTEM DNS which of course let's them just view the info. Then depending on the database & user & what needs to get done - I can specify which ODBC connection to use... either LIVE or READ Only.
Thanks!
Here's the overall situation:
We committed to a software business package called "Everest" - it is really pretty cool! However, as no software company can (unless the company spends booko denero!) make a complete software business solution that is going to fit our company exactly the way we want to interact with it for every nook and cranny situation.
Everest covers a great deal and does overall an A+ with all the data etc... they use SQL 2005 database...
Over the last 10 years, I've been pretty much been an Access peon, however I really am in awe over the last couple years of all the SQL stuff and learning more daily.
For some situations/A2007 databases, I'd like them to have permission to at minimum edit records (then there is no back and forths/imports & exports etc...) (so for that, I use the USER DSN and make sure they have authority, so they can edit) Right now I am the only one I trust in connecting my databases this way, and am entertaining allowing more permissions for others so they can edit
... for read only situations we use a SYSTEM DNS which of course let's them just view the info. Then depending on the database & user & what needs to get done - I can specify which ODBC connection to use... either LIVE or READ Only.
Thanks!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You want to give end users read/write access to the underlying database of a third-party application from their desktops? I think you are setting yourself up for big, big problems. Possibly quickly followed by your vendor terminating their support for you.
For your own sanity, make it a N-tier application so that you can control access to the data properly. Forget about linking tables into Access on the desktop.
For your own sanity, make it a N-tier application so that you can control access to the data properly. Forget about linking tables into Access on the desktop.
ASKER
Hi dportas,
Thanks for the response... our Access users are limited to Access Runtime only... they have no Design View abilities. Most folks here are not computer savvy at all, and I don't think they'd intentionally try to do any damage...? Nobody knows what ODBCAD32 in the run command is or any of that deeper stuff. Everyone has a connection via the Everest program right now... If they want they can mess with the real data as it is... I spose they could make a macro, open up a browser, cycle through the records to disrupt or mutate them... but I really don't project anyone doing that. Am I safe?
Thanks for the response... our Access users are limited to Access Runtime only... they have no Design View abilities. Most folks here are not computer savvy at all, and I don't think they'd intentionally try to do any damage...? Nobody knows what ODBCAD32 in the run command is or any of that deeper stuff. Everyone has a connection via the Everest program right now... If they want they can mess with the real data as it is... I spose they could make a macro, open up a browser, cycle through the records to disrupt or mutate them... but I really don't project anyone doing that. Am I safe?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi dportas,
((PS - from prior post) The 3rd party has always been aware of us using access as a front end for a descent handful of situations... (prior to new server & software, we had lots of databases hanging off our server), so it was a key part in us making the purchase of the software in the 1st place (ie. being able to use Access as a front end). I've talked with support there numerous times and they know we need to be careful, however they are A ok with us doing this. I hope this didn't come across as being defensive... I think your perfect for bouncing this off of.... thank you for helping get my head around the landscape.)
It just baffles me that, they have full edit power via the main program... but now it's all crazy scary to edit the data from a different interface....? After all, they're doing EXACTLY the same function... editing records, the data is just in a format and order that suits them... which = saved time & $.... but like you said.... at what RISK? Funny, it all comes down to a gamble! lol!
I totally see now the danger of permissions... and if a somewhat savvy user does figure it out, they could do some accidental damage... or even a savvy customer hijack's their computer for that sake!
We got great backups with date/versions selects going way back and on occassion have had to grab some tables/databases for recovering some wipe-outs. I have confidence there. My biggest fear would be record deletions and for now... additions... however, I'd be willing to risk a connection that allows EDIT/READ ONLY Permission but it would have to exclude RECORD DELETIONS/ADDS....
I suppose my new question would be... is this a possible option to set a connection up like that?
Thanks again!
((PS - from prior post) The 3rd party has always been aware of us using access as a front end for a descent handful of situations... (prior to new server & software, we had lots of databases hanging off our server), so it was a key part in us making the purchase of the software in the 1st place (ie. being able to use Access as a front end). I've talked with support there numerous times and they know we need to be careful, however they are A ok with us doing this. I hope this didn't come across as being defensive... I think your perfect for bouncing this off of.... thank you for helping get my head around the landscape.)
It just baffles me that, they have full edit power via the main program... but now it's all crazy scary to edit the data from a different interface....? After all, they're doing EXACTLY the same function... editing records, the data is just in a format and order that suits them... which = saved time & $.... but like you said.... at what RISK? Funny, it all comes down to a gamble! lol!
I totally see now the danger of permissions... and if a somewhat savvy user does figure it out, they could do some accidental damage... or even a savvy customer hijack's their computer for that sake!
We got great backups with date/versions selects going way back and on occassion have had to grab some tables/databases for recovering some wipe-outs. I have confidence there. My biggest fear would be record deletions and for now... additions... however, I'd be willing to risk a connection that allows EDIT/READ ONLY Permission but it would have to exclude RECORD DELETIONS/ADDS....
I suppose my new question would be... is this a possible option to set a connection up like that?
Thanks again!
Not really.
When you make something idiot-proof, someone will build a better idiot.
About the only way to do something like this is to build an unbound app (or Access unbound with forms) to pull something off like this.
When you make something idiot-proof, someone will build a better idiot.
About the only way to do something like this is to build an unbound app (or Access unbound with forms) to pull something off like this.
ASKER
Really great feedback! Much appreciated! I will use very sparingly and it's very informative knowing the true exposure to what I'm trying to get done. We're a small organization, and I have some very trusted long timer co-workers who have never had the time to imagined what a "data connection" even means. For the most part there would only be a few app's that qualify, but very time saving and non-confusing to design a few app's that cut right to the chase with trusted users & box location of course. Thanks again for the perspective on this! (I configured just one very trusted user's settings: SQLServerManagement-Securi
If the latter, then set up a database user with only select perms, and have your people use that user id to connect to SQL Server.