MS Access linking to SQL Server problem - could it be permissions???
-
Hi,
My Access knowledge is limited so bear with me here.
One of our user groups uses Access as a front-end to our SQL Server warehouse. They come in via linked tables (with me so far? Good!).
They are having issues opening some of their Access queries in design mode - the machine was hanging. I went up to have a look, logged in as myself, opened the same query in design mode and it returned something immediately (OK, it returned an error but that's a completely different error, trust me
). The point is, it didn't hang the machine.The only difference between myself and the users is that I am an administrator in SQL Server, they are not. (Admittedly there may be other differences, AD settings for example, but we're working on the assumption that its something to do with SQL at the moment).
I don't understand though why the difference in permissions would cause a problem just opening an Access query in design mode - surely Access already knows the metadata of the linked tables so it doesn't need to interrogate SQL Server. Just to prove this point I ran Profiler as I opened the Access query in design mode and sure enough, there was no interrogation of SQL Server.
So, could the different permission sets between myself and the users be causing the client machines to hang. And if so, why?
Also, is my assumption that Access already has all the metadata it needs in order to open a query in design mode a false assumption?
Any help very much appreciated.
Regards
Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
Hi Jamie,
I don't have this kind of problem in my office but perhaps I can help you.
In Access you can re-link all the tables, did you try this before opening the query in design mode ?
I suppose the computer on which you are trying to open the Access query have the right ODBC connection to establish the link with your SQL server ?
In SQL did you allow them to open the tables ? you have to define for each user or group some access rights to the tables.
Let me know how it goes.
Regards,
Pascal
-
Hi Pascal, thanks for the reply.
Re-linking the tables is a good idea. As soon as I've sent this mail I'll try that out. I'll report back if there's any success.
Yes, all ODBC connections are set up correctly and yes, the users have sufficient permissions on the tables. They can actually see data in the Access queries when they run them.
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
Doh, I'm not thinking straight. Relinking the tables won't make any difference. Remember that when I open the mdb it works OK, but when the users open it, it hangs. In both cases the same linked tables are used so refreshing them won't make any difference.
Any other ideas?
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
What version of Access are they using? Do they have an MDB file or an ADP file?
-
They are using MS Access 2002 ad it is an MDB file.
But that isn't the issue. Remember that I opened up the same thing on the same machine and it worked fine.
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
Jamie,
you can also Compact and repair the mdb database but I think it won't solve the problem.
If you use Access 2002, you can perhaps open an ADP project instead of an mdb database. if your query uses only SQL tables and nothing else, you can open an ADP project. If your queries are using some other link tables (other than SQL), then forget this idea.
I have a user in my office who uses Access to query Oracle database and I know he has a big .mdb database and if he wants to enter in the design mode, he has to wait 15 minutes (his computer is a pentium 4, 3.2GHZ, 1 GB RAM). Did you wait until you received an error ?
Pascal
-
Thanks everyone for the replies.
We now know that there is a group of people that can always open the object without any problems whereas another group always DO have problems. This is inexplicable really. It can't be something to do with Access otherwise everyone would experience the same problem.
By best guess is its something to do with the AD setup for each user because the only thing that determines the behaviour is the person that happens to be logged on at the time. Everything else is the same. It also seems to be that anyone that has ever been part of the IT department doesn't experience problems, everynoe else does. So, we've turned it over to network support. If and when they come back with anything I'll be sure and post it up here.
Strange.
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
Jamie:
Since your using version 2002, try using this add-in for Access. It's called DSN Stripper and gives you what's called a DSN-less connection. No more need for ODBC drivers. Here is the link:
http://www.mvps.org/access/modules/mdl0064.htm
I have had great success with this where I work due to no more need of ODBC drivers. Good luck!
M. Todd Smith
http://www.mtsmith.blogspot.com/
-
> Everything else is the same. It also seems to be that anyone that has ever been part of the IT department doesn't experience problems, everynoe else does.
That may be the issue right there. Have your network guys look in the network accounts for differences between the permissions for the IT people and for everyone else.
-
I have also to point out that whe Access open the DB it DOES checks the security settings on ADP files and allows only certain permissions to certain groups.
On MDB files though the Only thing that is saved is the informantion of the DSN.
you should check LOCAL permissions
* Noel
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply