I have created an ADP front end connecting to a SQL 2000 Database. Due to the current Network Infrastructure I am using SQL authentication for the users to logon.

The Front end will be deployed by Group Policy to Approx 60 Users. However the front end opens the authentication logon box with the Default Username already entered. I want both the Username & Password boxes to be blank ready for the Front end user to type in their credentials.

What I am thinking is that I create a basic SQL User(to use as the default for Data Link) with a Saved Password which allows them just to connect to the SQL database.

With the Autoexec Macro I then open a form asking them to put in their individual logon credentials, which when accepted allows them to input, query the database. Failing logon obviously chucks them out of the system.

Am I going the right way about this???

I have done something similar. What I do is set the start up options to open a form that is bound to a hidden table, and then store their username and password there. Then I open a hidden form with the username and password fields, and keep that open while they are in the database. If they want to run a transaction, then I use the credentials found on the form. The hidden form's on close event runs a delete query to clean out the credentials from the table.

If you want to check their credentials when they first log in, then you can use them to try to open a connection to the database. Like:

On Error Resume Next

Err.Clear
Set cn = New ADODB.Connection
etc...

If Err <> 0 Then
   MsgBox "Please enter proper credentials", vbCritical
   DoCmd.OpenQuery "qryDeleteCredsTable"
   DoCmd.Close 'this intermediate form
   DoCmd.OpenForm "frmLoginForm"
End If

The frmLoginForm should have an application exit button on it, and should be pop up and modal.

Does any of that help?


mmcdonal

Look it up at: http://wrox.books24x7.com

Thanks for the info so far. When you talk about opening a form bound to a hidden table. Am I right in thinking that this table is held on the SQL backend. This will then become a container for logon details when every user who logs.

No, I was talking about a local hidden table, and local hidden form to take the login credentials from the login form, and then hold them for easy reference when the user wants to make a transaction. These COULD be checked against a table in SQL, which I have also seen done.



mmcdonal

Look it up at: http://wrox.books24x7.com

Also the hidden table. I presume hidden is not giving them the Select Permission, and giving them the Insert to add their logon details to a record.

How do you create a local table in an ADP file. Any table I create ends up on the Backend??

Yikes. I missed the ADP issue. You can put the values in public variables then. You could also put the values in a SQL table. The issue is making sure that the text field is a password field, which can be set at the table level, but not at the form level, I think. I don't think SQL Server supports a password field... never did it.


mmcdonal

Look it up at: http://wrox.books24x7.com