Hi,

I have a SQL Server Express backend which has all the tables and etc. and an MS Access 2007 front-end that i use for queries, data entry etc. Since i am familiar with Access i pefer to use it as a front-end.

Now, I am able to access the data via the MS Access fron-end on my system, but my colleagues who also need to query the DB are not able to access the data when they try to open the MS Access form/query (we are on the same network).

I have also set-up ODBC connections on my colleagues system and ODBC admin tool shows that its able to connect to the SQL Server on my system. Also the SQL Server logs show the connection established b/w the two systems.

What i am not able to figure out is why this is happening, tried to delink/link again, system/user DSN and other solutions that i could understand on this site.

Can someone please throw some ideas as to what else can i do to identify the problem (am a beginner in the SQL world)???? Thanks!!

Did you grant the permissions to the other user to the database? If not, then take a look at the steps outlined in this thread (http://www.utteraccess.com/forum/SQL-Permissions-Access-F-t1947795.html).

yes I have,

added them as db_datawriter.. do i need to give them additional roles/permissions?

Okay, just to be sure, did you also identify them for Login and do they have db_datareader role, too?

nope, i didnt give them db_datareader role, will add that as well.

Not sure i understand the "identify them for login" part. I created logins for them under Security->Logins and also added them as users to the database for which access is required.

Do I need to add them anywhere else as well?

ok, another thing that i just tried is, i logged in from a different machine using my own login credentials, even i wasnt able to open up the form, it still give the ODBC call failed error,

does this have something to do with network permissions?

Perhaps.

How are you/other users networked? Are you in same LAN? Are you using Windows Authentication (aka Trusted Connection) or SQL Server Authentication?

yes we are in the same LAN and i am using windows Authentication... when i open the access front-end from a different system, i do get a security message saying content not trusted, but when i try to open the form it gives a ODBC call failed error.

Wait, did you click "Enable Content" button when you got the security message before you try to connect to SQL Server? If you immediately get an error message before you can do so, clear out the startup code (e.g. disable AutoExec macro or clear out the form name for the startup or both if needed).

yes i did, i also cleared up the default forms... i can't even access the linked tables from a different system, it still gives me ODBC call failed error.

When you set up the ODBC DSN did you make sure to change the DEFAULT database there? We've had the situation where some IT folks will set up a DSN on the user's machine and the default database is set to MASTER instead of the actual database that it should be using. And they have security rights to the database but NOT to MASTER.

Thanks for your response. Yes, i did change the default DB to the actual database. They do have rights to the actual DB and yes NO rights on master :((

Does sql server error log tell you anything?

exec xp_readerrorlog

Yes, it says login succeeded for remote users...

Login succeeded for user 'XXXXXX'. Connection made using Windows authentication. [CLIENT: IP Address]

No actual errors then? I would suggest giving a test login "system administrator" access and see if this fixes the issue. At least then you will know whether you have a permissions problem or some other kind of issue.

I don't suggest using system adminstrator access for your users, only for the prupose of testing this issue

Thanks for you inputs....

I have tried this and few other options but nothing seemed to work.. anyway i have now created a ADP and everyone seems to be able to use it and enter/save data without any issues... the only thing i am working on how to prevent people from copying it to local machines or even if they copy the ADP to the local machine, how to ensure that they have the most current data in the ADP... any ideas?

Also i can create queries in a design view in MDB, but i am unable to do that in ADP, is this because i am using Access 2007 and SQL 2008? if i install Access 2005, would that work?