access SQL Server from application

Hi,

I have recently transfered data from MS Access to MS SQL Server 2000 and created user interface in MS Access Project (ADP). Now I need to secure the data on server and want to access it ONLY through interface I created in MS Access. Every user has to log in the application (user id and password). Users may have the same access rights to the server (so maybe just one account or whatever I need for all of them). Can anyone advice me how to set it up? To access sql server from MS Access I am using ADO.

Thank you.
djLookup "Application Roles" in Books Online.

But be aware that it is better practice to limit your to establish security at the data level than the application level. Establishing Application Roles is no substitute for making sure that your database is locked down.|||Yes, I agree. I have no experience with SQL Server. In MS Access it was easy. So, how to lock down database. What I should create there to secure data? Any password? And then I need to access data through ADO. There is connection string where I have to pass a password.

Could you help me to clarify that.
Thanks
dj

Access Front End?

Is there any way to set up a database, that was access but I transfered it into SQL 2000 using DTS, so that the front end of the database is using Access 2000 but the backend is SQL Server 2000? I want to do this, or something like it so that people can use the database to create queries and reports, but utilizing the SQL database.You can link the SQL Server through an ODBC data source within the Access db. You have to be careful though, that some SQL data types are not recognized/supported in Access. If I remember correctly, bigint is one of them I think.|||I did that and it will not allow me to add or delete records (using the access front end) in the tables that I have linked back to the SQL database. Any ideas why?|||Does the account you used to set up your ODBC connection have the necessary permissions to delete files in the database?

Do the tables in question have a primary key/unique identifier as this is required if connecting through an ADP or OLEDB connection.

Cheers,
Andrew|||Here's a "new" problem. I have this database set up so that it has an access front-end and an SQL backend. When I open the database on a computer with the SQL Server client installed it runs fine. When I open the database (access front-end) on a computer without the SQL client installed it prompts me to enter some login information to connect to the server. Is there any way to modify this front-end/backend so that the login information is automatically entered and it does not prompt the user to enter it?