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!

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
Do your users need to be able to add/delete/update, or can they get away with just viewing data?

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.
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.
zappaifilusAuthor Commented:
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.


The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Patrick MatthewsCommented:
Then dportas's warning applies: you can build safeguards into your Access app, but that will not prevent users from creating their own connections and potentially breaking something :)
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.
zappaifilusAuthor Commented:
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?
Only you can determine what is acceptable in your organisation. My advice would be to put away the idea that they aren't smart enough to do any harm. The fact that they will use Access runtime is irrelevant. Even a complete novice can create a database connection in Excel or Word once you grant them permission to do it.

Assume that your users will always be one accidental keystroke away from deleting everything. You need to decide if that's a risk worth taking or if it really would be better to build a secure application instead.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zappaifilusAuthor Commented:
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!  
Jim P.Commented:
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.
zappaifilusAuthor Commented:
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-Security-Logins-UserMapping- db_accessadmin, db_datareader, db_datawriter, public.  Then under the Database itself: SQLServerManagement-Database-Security-Users-Properties: db_accessadmin, db_datareader, db_datawriter.  Then for her Schema: SQLServerManagement-Database-Security-Schemas- saved Schema with same properties as a db_datawriter.  Then from the same window there is a link to get to the database properties which has more settings for permissions on what the user can do - those were set to Grant: Connection, Execute, and Update.)  I tried to minimize as many permissions as I could, but had to ultimately give up the db_accessadmin in the User mappings before it would really work.  Thanks again... This will be used sparingly and with MEGA caution.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.