SQL Server Login Form

[dmaranan]

I've created a SQL Server Login form in MS Access. When a user enters the wrong username/paswword I want to be able to pop up a message stating thus instead of using SQL server's error message.

Does anyone know how to do this?

 

[EstuardoSierra]

dmaranan:

There are hundreds of ways to do it, here is just i think is simple and efficient

Table user (4 fields)
Field type Size
UId Autonumber
Uname texto 25
ULname texto 25
Upass texto 18
Input Mask: PASSWORD

build a form with the following properties
Format Tab
Caption Login Area
Allow Datasheet view No
Allow PivotTabel View No
Allow PivotChart View No
Scroll bars No
Record Selectors No
Navigation buttons No
Dividing lines No
Auto Resize No
Auto Center No
Border Style Dialog
Control Box No
Min Max buttons No
Close Button No
Width 10cm
Moveable No

DATA Tab
Record Source tblUsers
Allow Deletions No

OTHER Tab
Pop Up Yes
Modal Yes
Shortcut Menu No
Allow design Changes Design View Only

Controls

Fields Uname and Upass
1 txtBox
2 bottons
1 comboBox
Upass control's properties
visibile No

combobox properties
Combobox Type: "Find a record" (3d. otiond, in the wizard first tab)
Available field: Uname

 

[dmaranan]

I'm not sure if I asked the write question.

I'm using MS Access as a front end for SQL Server 2000. When a user enters the wrong username and password I get an error message on the lines of :

Connection failed:
SQLState: '01S00'
SQL Server Error: 0
[Microsoft][ODBC SQL Server Drive]Invalid connection string attribute
Connection failed:
SQLState: '28000'
SQLServer Error: 18456
[Microsoft][ODBC SQL SErver Drive][SQL Server]Login failed for user 'userusge'

Beause this error message doesn't make sense to most users, I would like to create my own error message saying, "Please re-enter your username and password."

 

[dmaranan]

I still don't know how to resolve this. Can anyone help?

 

[Krystoff]

Dmaranan,

How are you connected to the SQL Server? IE I have a Microsoft ADP project that is connected to SQL Server 2000. This makes it much easier as there is a connection option under File.

Using SQL Server Authentication, there is a section for a specific username and password. Everytime I open the ADP file, it asks me to logon.

If you are using a connection string from VBA to logon because you are using an MDB file, I am not really positive. I really like using the ADP file although it is a little different to program in compared to an MDB file.

Let me know how you are logging in and I will see what I can do.

Chris W

 

[dmaranan]

I am using a connection string from an MDB file. The login capability is important because I'll have over 20-30 users using copies of the file. The connection string needs to have the server name, database, username and password.

Any help is appreciated.

Thanks!

 

[diamondsc]

If you are using a form for the user login, use the contents of the text boxes for the username and password to populate the connection string. For example, say the text box for the username is call Me!UName and the password is me!PWD. The connection string would look something like:

Dim ConnStr as String

ConnStr = "Provider=sqloledb.1;Driver=SQL Server;Server=sqlSvr;Database=sqlDB;UID=" & Me!UName & ";PWD=" & Me!PWD & ";"

You can use string variables instead of text box names. You can also use string varibles for the server and database names.