login form

[DTSFreak]

Hello,

I have an access mdb front-end which i use for end-users. The back-end is sql server 2000.

In the front-end there no linked tables or pass-through-queries, all recordsources are binded in forms through ado.

At the moment i've created a login for the front-end to connect to the database. This is hardcoded in a module, but i don't think this is best practice.
Every form uses that connection to get data. So yes, the users won't have to login. What i would like, is to have users log in but without creating extra logins on sql server and still be able to know who's currently logged in. We don't use windows authentication.

How can i create a customized login form so that users can login into the database. This login should not be a jet-login (user level permissions with a .mdw).

Can anyone help me on this and if it is not best practice, please tell me so.

 

[BSman]

Here's what I've been doing:

The connections to my SQL back ends use a standard userid and password that I ask Access to save when I create the links.

In the front end I set up a login form which is the first form opened. It asks the user to enter a userid and password for entry into the application. Even allows them three tries before exiting. Once the correct userid and password are entered, the user is asked if he or she wants to change the password and if the answer is "yes" then the password change is allowed.

I save the userid in a public variable in the application as well as the application rights for the user (all stored in a UserList table in the SQL Server database) and use the rights to determine what the user can see and do in the application.

You actually have two levels of control with this method. The first is on the network, where you define which application folder(s) each user can see (and, by definition, run). The second is the login described above.

This has worked very well for many applications. For almost total security you need to disable the abiilty of users to start the mde file (which is the version you should make available to users, not the mdb version) with the shift key held down. For information about the shift key setting, see:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acstartupoptions.asp

I fully agree that the Access workgroup is a major headache. It becomes even more of one once you get caught in version hell where you need to support multiple versions of Access. (I'm working in an environment that now has Access 97, Access 2000, and Access 2003, any combination of which may be on users' workstations to run the same database application.)

 

[willir]

At the moment i've created a login for the front-end to connect to the database.

Instead of closing the login form, what about hiding it. This will keep the link open to the SQL server, should improve performance, and would it also maintain authentication.

Richard

 

[BSman]

The links all use a standard login and password for the SQL database and when you set up the links you specify that the password is saved. The login form I describe is to log in to the application database (front end) and I use it rather than the Access mdw security. When I said "connect to the database" I was really referring to allowing the user to connect to the front end database application. Has nothing to do with the links.