ADODB Connection with AS400

[UAMI]

I'm having problem's in validating the user and password in the AS400 machine. I garantee that's the right user and password, i'm using. It happend's that Sai2 only active if i dont put a user or a password. If both field's of the form, text0 or text1 are filled with something, sai2 actives, although, the user and password do not exist.
I substituded de nº of the DNS, with xx.xx.xx.xxx

Private Sub Text2_Exit(Cancel As Integer)
On Error GoTo Sai2
Dim stDocName As String
Dim stLinkCriteria As String
Dim val As String
Dim liga As New ADODB.Connection

liga.ConnectionTimeout = 520
liga.Open "Provider=IBMDA400;Data Source=xx.xx.xx.xxx;", UID = Me!Text0, PWD = Me!Text2
Dim Rcds As Variant
liga.Close
stDocName = "MENU"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit Sub
Sai2:
MsgBox " User or Password Incorrect!"
Me!Text0 = Null
Me!Text2 = Null
Me!Text0.SetFocus
Exit Sub
End Sub

 

[nicsin]

I believe the connection open should be:

liga.Open "Provider=IBMDA400;Data Source=xx.xx.xx.xxx;", Me!Text0, Me!Text2

 

[UAMI]

Thank you nicsin,but it didn't work. It does the same. In some way i have to valid if the user and password are correct.
I put the code like you suggested and one more line, executing an sql (no meather what) and so, the sai2 actives because it doesn't do the sql without a valid login.
This is a bad solution, but it work's!
Here is the new code:
Private Sub Text2_Exit(Cancel As Integer)
On Error GoTo Sai2

Dim stDocName As String
Dim stLinkCriteria As String
Dim val As String
Dim liga As New ADODB.Connection
Dim Rcds As Variant
Dim rec1 As New ADODB.Recordset
Dim sql As String
Dim Msg, Style, Title, Help, Ctxt, aResponse, MyString

liga.ConnectionTimeout = 520
liga.Open "Provider=IBMDA400;Data Source=10.42.48.201;", Me!Text0, Me!Text2
sql = "select * from acogivc.moda00"
Set rec2 = liga.Execute(sql, Rcds, adCmdText)
liga.Close
stDocName = "MENU"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit Sub

Sai2:

MsgBox " Utilizador ou Password Incorrecto!"
Me!Text0 = Null
Me!Text2 = Null
Me!Text0.SetFocus

Exit Sub


End Sub


Could anyone suggest me, how to handle with the login validation?

Thank you!

 

[nicsin]

AFAIK, this is the way to check for invalid passords. Trap the error produced by the database. The one thing i can add is to use a seperate error handler for the connection. Here's an example:

Private Sub Text2_Exit(Cancel As Integer)
On Error GoTo Sai1

Dim stDocName As String
Dim stLinkCriteria As String
Dim val As String
Dim liga As New ADODB.Connection
Dim Rcds As Variant
Dim rec1 As New ADODB.Recordset
Dim sql As String
Dim Msg, Style, Title, Help, Ctxt, aResponse, MyString

liga.ConnectionTimeout = 520
On Error GoTo Sai2
liga.Open "Provider=IBMDA400;Data Source=10.42.48.201;", Me!Text0, Me!Text2
On Error GoTo Sai1
sql = "select * from acogivc.moda00"
Set rec2 = liga.Execute(sql, Rcds, adCmdText)
liga.Close
stDocName = "MENU"
DoCmd.OpenForm stDocName, , , stLinkCriteria

endSub:
exit sub

Sai2:
MsgBox " Utilizador ou Password Incorrecto!"
Sai1:
Me!Text0 = Null
Me!Text2 = Null
Me!Text0.SetFocus

resume endsub

End Sub

 

[UAMI]

I agree with you, nicsin! I have to trap the error produced by the database,separately, but like you demonstrated, it didn't work. Sai2 doesn't actives. Is there another way to validate the user and password, out of the connection string?

 

[jbradley]

Try this to open your connection

liga.Open "Provider=IBMDA400.DataSource.1;" & _
"Data Source=xx.xx.xx.xxx;" & _
"User ID=" & Me!Text0 & ";" & _
"Password=" & Me!Text2 & ";"
If Err.Number <> 0 Then MsgBox &quot;Connection Open Failed&quot;

 

[UAMI]

Sorry jbradley, also dont't work!

err.number=0, but user and password are not correct.

Now i noticed that when i was in debug mode, after passing this line:
liga.Open &quot;Provider=IBMDA400;Data Source=xx.xx.xx.xxx;&quot;, uid = Me!Text0, Password = Me!Text2

In the immediate window, the content of UID and password are empty, although Me!Text0 and Me!Text2 are filled?

UID and Password shoulnd't have the same values?

 

[nicsin]

Hello and Happy New Year!

UAMI,

If you are using the open method as in your previous post, I am not surprised you get an error. it should either be

liga.Open &quot;Provider=IBMDA400;Data Source=xx.xx.xx.xxx;&quot;, Me!Text0, Me!Text2

or as jbradley suggested

liga.Open &quot;Provider=IBMDA400.DataSource.1;&quot; & _
&quot;Data Source=xx.xx.xx.xxx;&quot; & _
&quot;User ID=&quot; & Me!Text0 & &quot;;&quot; & _
&quot;Password=&quot; & Me!Text2 & &quot;;&quot;

 

[UAMI]

Happy New Year, for you to Nicsin!
Well, i think i have done the conection string in all ways possible including the one you say. Perhaps this problem it has to do with the kind of mashine (i dont't know).
The only way i resolved this problem was puting an SQL after the conection (sql = &quot;select * from acogivc.moda00&quot and in this way he goes validating the user before execute the sql. It makes no sense but it was the fastes way to make it work.

Thank's

 

[rzs0502]

Your strings are probably blank because you are missing the property.

Should it not be Me!Text2.Text and Me!Text0.Text

Using Public Variables might be better.


&quot;If you always do what you've always done, you will always be where you've always been.&quot;