February 21, 2007 at 4:03 pm
Hi there, I need some help with MS Access 2003 Link Tables to SQL2000. They are using ODBC FILE DSN with AD Authentication from the network. We can establish the link from MS Access 2003 to the SQL server but the user can not modify the tables. This seems like a permissions thing. When in the Tables section in MS Access 2003, hovering the mouse over the LINK Table shortcut icon it shows the path link directory.
The User is in the correct AD security groups, which by default should allow them to access and modify tables.
The problem is that one particular user is un-able to update tables in a database on a SQL server. The user has the standard AD security groups as everyone else. And he can modify other tables fine and other users within the company can modify the problematic table fine. It appeares like a permission thing or a stored caching of login information on the users computer.
There are no error messages. I can successfully test the ODBC connection fine. I can recreate the connection fine. The link can be created on another machine and then imported to the users computer. this allows him to access the table and modify the tables. In addition, when hovering the mouse over the imported LINK icon the only difference is the Computer Name.
So, does the MS Access Link Table procedure store security information on the computer which could in turn limit the users abiliy modify the tables.
Cheers,
Troy
February 26, 2007 at 8:00 am
This was removed by the editor as SPAM
February 26, 2007 at 4:12 pm
Yes, absolutely the security info is kept in the link. That is why you are not prompted for a password each time you connect via the link. The ODBC DSN specifies a user name, but the link can over-ride the user name. To "fix" this, just use Access's linked table manager, select the link(s) in question, and check the Always prompt for new location box before clicking OK. This will step you through re-specifying the ODBC DSN and entering the security login id and password.
March 1, 2007 at 6:27 am
could this one user have had 'deny' permissions explicitly set in sql server?
March 1, 2007 at 2:49 pm
When using an Access myDatabaseFrontEnd.adp form you may need to create a primay key on the SqlServer Table.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply