Connect to SQL Server 2000 .mdb .adb

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I recently moved my Access BE tables to SQL Server 2000. I re-worked my
Access front end .mdb to link to these tables through a DSN file. I got
daring and expiremented with .adb Access Project format. It worked great
except for when I tried to share it off a shared draft with other users.
My other users are able to connect fine with my .mdb Access file and the DSN
file on the shared drive; however, my users are not able to connect to these
same SQL server tables through the .adb file (which has the location of the
SQL tables defined within - it works great for me, but I'm the only one who
can connect up). These user's received "SQL Server does not exist or
access denied." Error message (but I know SQL server exists as I'm able to
connect with the .adb file and I know the other users have access as they're
able to connect to it through the .mdb file).

1 other note, 1 user was able to connect to the SQL tables using the .adb
file after I gave her dbo owner permissions (this user has Enterprise Manager
on her system which the other user's don't have. My understanding is that
you shouldn't need EM or dbo owner level of permission to work with MS Access
Project. I definitely don't want regular users to have that level of
permission).

Any thoughts as to what may be causing this?
 
When setting up the DSN you provided information for your specific login in
order to connect using the mdb database, when your users use the mdb that is
the access they are using, on the other hand the adp database uses the
security setup in EM for SQL server, to be more secure setup roles in EM and
add users to those roles that way you can continue to use the adp, the Books
Online option in SQL server will give you all the details to handle this task,
 
Josh,

I may have neglected to specify. The other users ARE set up in SQL server
roles. In fact, I placed them in the fixed db role of reader and writer to
make sure they had permission to the user tables.

Ideally what I'd like is for SQL to use Windows NT security to authenticate
users when coming through either the .mdb or .adb front end. Any thoughts on
how to accomplish this? The roles are established. Also, preference is to
use a shared drive / shared front end for now.

thanks, George
 
Here's some more clues - 2 other users who have Enterprise Manager installed
were able to connect to my SQL tables using the .adb file. I am also able to
connect using the .adb from my desktop machine that has Enterprise Manager;
However, I cannot connect from my laptop which DOES NOT have Enterprise
Manager installed.

What is needed to make .adp connection work? I thought Access 2003
installed plus permissions to a valid SQL server would do the trick... Is
there something else that I'm missing? thanks, George
 
Back
Top