Sharing SQL Tables via MS Access

[GBD]

Goal
I'm trying to trying to setup a MS Access database on a shared server drive where all of the main tables are linked via ODBC into an SQL database. The SQL database is updated and maintained via purchased application. It appeared that MS Access security could be used to control the type of access each user has. Most of these users will have read only access for reporting purposes since the main application is used to update and maintain the data.

What I've done.
1) I've setup ODBC Access for each user. Not sure if this should be User, System or File DSN. They all work and I can't tell at this point that it makes a difference.
2) Created the MS Access Database, created users and assigned security.
3) Linked the needed tables.

Problem
1) When I had a user try to access a table it said "Table definitions not defined". What I then realized is that the MS Access on there machine was picking up their default security. A security Table and a shortcut (.mdb1 file) that pointed to the correct security and table definitions.

2) Now when the user gets in everything seems to come up fine but when they try to go into a table they get an error saying they don't have permissions. If I relink while they have it open, they can get into the table just fine. However, they can't delete the old tables because they have 'Read only' access. I don't really want to have to relink all the tables for each user, I might as well to go to each of their machines and set them up individually...not a good idea.

What now?
This seems like something that should be fairly easy to do..what am I missing?

 

[Pat Hartman]

When the data is in SQL Server, the access should be controlled by SQL Server rather than Access. This means that each person has to have a non-update account that they can use. Most DBAs would prefer the individual identification for each user rather than a single shared account used from Access.

 

[IgorB]

1) Set up System DSN- works just fine!!
2)You have probably dbo or dba account on SQL Server side.
You have to create on every computer the same DSN with dbo or dba Login and password with TCP/IP client configuration setting.
3) Link once all tables using your new DSN with save password checked option.
4) place mdb with linked tables and front end on Network shared folder
5) reserve your rights(read,write) in Network to relink tabled at any time you need and do it only on your computer having these rights.

You shouldn't have any problem after all.
Igor.

 

[Pat Hartman]

In most companies, the sharing of userIDs and passwords is considered to be a serious breach of security. In some companies, it would be cause for dismissal.

 

[GBD]

Shared User ID's

Thanks everyone for your imput.

Each user will have their own User Id and password via Access Or SQL, so that is not an issue.

I'm working with some of the other suggestions.

I'll let you know what happens.
Thanks,
GBD

 

[GBD]

Finally got it to work!!

The first part of the problem was in the ODBC setup. I used the System DSN option but made a few typo's that left ODBC names not matching from machine to machine. Once I got that straight each user had access to the linked tables.

Then the Security in MS Access got a little tricky. The default security file for MS Access is 'Secured.mdw' which must be overridden. This is accomplished by creating a short cut that points at the correct database and 'Secured.mdw' file.
Running the security wizard while the new MS Access table is open is the easiest way to create both the shortcut and the new Secured file. Only trick is to make sure that you allocate 'Admin' permission to yourself, else you can't do anything. Admin seems to become disabled in this process.

That seemed to do the trick..thanks everyone.