Access uses a DSN once then ignores

[John Grove]

I am using SQL server as a back end to Access 2000. I am
using linked tables through a machine DSN. I want the
access to be read only so I created a log in called Foo in
SQL server. Foo is given read only permission in my SQL
database. My DSN is created for Foo and uses SQL server
security. I use the relink tables tool in Access to link
my tables through the DSN to my sql database. In
enviroment A (my office) all works as you would expect.
You can not change any of the data in SQL Server, if you
try you get an ODBC error. In environment B (my client)
doing all the steps as outined above it does not work as
expected. The first time Access is opened the data can not
be modified. But if you just close and reopen Access you
have full read write capabilities. After opening Access
the second time you are not asked for a password when
opening a table, you are somehow automatically connected
with full permissions. Access seems to be forgetting that
it is supposed to be going through the DSN when asked for
the data after it has been opened and closed at least
once.Can anyone help?

 

[Joe Fallon]

This can happen in environments that use Mixed Security on their SQL Server.
It is almost 100% sure that this is a SQL Server permissions issue.
Your Foo account may have Select only permissions ut are there any other
acocunts available?
(e.g. Guest, dbo, etc.)

If your client allows Windows Authentication then perhaps they are
"bypassing" the DSN and using Windows and acting as Guest!


If you can't figure it out right away then
do a Profiler trace and monitor all activity during sign on.

 

[david epsom dot com dot au]

Access may copy some of the information out of the
DSN into the connect string, and may have used that
to make a connection the second time?
It might be that you would be better off using a
DSN'less connection (where you use code to create
the connections and write the information to the
connect property of the tabledefs)

(david)