"Permission denied" when linked to SQL server via ODBC!!!?

HI!

I have the regular split in my Access 2000 application: The application in
one file and the data in another. All tables are linked. At least this is how
it is when I work locally. When the application is on the network/(in
production) the tables are in SQL Server and linked via ODBC.

The application works fine when I work locally, but on the network I get
‘Runtime error ‘70’: Permission Denied’ when I try to run this statement:

Set db = CurrentDB ‘ have: dim db as DAO.Database

(tried both DAO.Database and Database)

(Same error on:
Set qdf = CurrentDb.CreateQueryDef("") ‘Dim qdf As QueryDef
)


The user granted rights to all tables in the sqlserver is the same granted
rights to access the database for the ODBC connection.

Does anybody have any idea what the error could be?


Thanks,
J.K.

We solved it. The ODBC didn't connect to the SQL Server with a user that had
all rights to the tables in the SQL Server.

This is how it is right no (if anyone care :- ):
An user is created in the sql server. Let's call her 'User1'. User1 has all
rights to all tables. An odbc connection is created on another server and
connects to the database with User1's username and password. All connection
from the application to the tables are now done as 'User1'. All security is
in the application. There are several other ways to do this. This works for
us. Bye :- )