best practice about connecting to database

[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.

How can i create a customized login form so that users can login into the database. This login is not a jet-login in access.

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

 

[CautionMP]

Create a startup form that asks for a login, and athenticate against a local Access table. If the user is authenticated then open the connection to the SQL server that you have already coded into the database.

One thing that you might consider since you are only trying to track usage is create a startup form that the user never sees (hidden). Tie the form to a local table (i.e. tblUsageLog) that contains strUserName, dteOpen, dteClosed.

In the OnOpen event for the form set strUserName = Environ$("USERNAME"), and dteOpen = Now().

In the OnClose event set dteClosed = Now().

This will create a record that shows the users Login/LAN id and the time they open & close the database.

 

[jrbarnett]

Why not use Windows integrated logins on SQL SErver then they won't need to remember another username and password for your database. If the SQL Server security is set correctly, then only authorised users will be able to access the database.

CautionMPs trick of Environ$ ("USERNAME") will work on Windows NT, 2000 and XP workstations, but not 95/98/Me. If you have workstations running any of these operating systems, there is a VBA function in faq181-3779 that will retrieve it for you.

John

 

[DTSFreak]

Hi,

The security is set to sql login, we don't have a domain controller. So i need a workaround.

 

[jrbarnett]

You don't need a domain controller. If SQL server is running on Win2K or Win2K3 server, you can set it to grant login to BUILTIN\<groupname>

where you set up group on the machine with named accounts for everyone who uses it in the group. Set your database as the default for this group and that's it.

John