DAO problem...

[MidgetsBro]

I am having problems with DAO. I have this same code in a different project, and it works fine, but I am trying to use the same code in a different program to open a table and get info out of it.
OK this code works fine, to get an item out of the table:

Set db = OpenDatabase("C:\Windows\Desktop\inventory.mdb")
strSQL = "SELECT * FROM Current WHERE ID = " & txtID.Text 
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
If rs.RecordCount > 0 Then
    'Text1.Text = rs!ItemNo & "" 'substitute real field names
    buff = rs!Price '& ""
    lstReceipt.AddItem buff
    txtID.Text = ""
Else
    MsgBox "No Match found for that ID.", vbCritical
End If
rs.Close
db.Close

But then I use this code to get info out of a different database and table:

Private Sub cmdLogin_Click()
Uname = txtUsername.Text
Pword = txtPassword.Text
Set db = OpenDatabase("C:\Windows\Desktop\users.mdb")
MsgBox "got here"
strSQL = "SELECT * FROM usertable WHERE Username = " & Uname
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
If rs.RecordCount = 0 Then MsgBox "No user by the name " & Uname & ". Please check the name you entered.", vbInformation: Exit Sub
TestPass = rs!Password
If TestPass = Pword Then MsgBox "Correct password!"
rs.Close
db.Close
End Sub

and I get an error with the dbOpenDynaset variable. It says somthing about Too few parameters, expected 1. So I change dbOpenDynaset to a 1, and it gets an error opening the table. This is frustrating! Just incase you are wondering, I HAVE added a reference to the ADO library, just like in the other project, and I have declared all my variables... I just don't want to post all the code.

Please don't mention ADO, cause I have had so many f**king problem with ADO, it's not even funny.

Thanks in advance
-Joey

[Beacon]

Check db forums joey!!

[alex_read]

Private Sub cmdLogin_Click()
    Uname = txtUsername.Text
    Pword = txtPassword.Text
    
    Set db = OpenDatabase("C:\Windows\Desktop\users.mdb")
    Debug.Print "got here"
    
    strSQL = "SELECT * FROM usertable WHERE Username = ' " & Uname & " ' " '###
    Debug.Print strSQL
    
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
        
    If (rs.RecordCount = 0) Then MsgBox "No user by the name " & _
    Uname & ". Please check the name you entered.", vbInformation: Exit Sub
    
    If (rs!Password = Pword) Then MsgBox "Correct password!"
    
    rs.Close
    db.Close
End Sub

• Remove the spaces between the quotes (I did the above to show you
  what this was meant to be, put this as :
  

  ='" & Uname & "'"

• I HAVE added a reference to the ADO library

  Shouldn't this be DAO library? What version of Access is the database and what version of the DAO library are you using ?

[honeybee]

The error you have quoted means you have got either the table name(s) or the field name(s) misspelt. Check that the table name is usertable and the field name is username. Case doesn't matter.

It's my hunch that the table is not usertable. And once you have corrected that problem you will run into another problem, for which Alex has already given the solution.

.

[MidgetsBro]

Thanks guys. The table names I was using were correct honeybee. Beacon answered my question when I posted it in the DB forums, but I thank you for trying to help.

[honeybee]

Damn!! That's the first time my guess went wrong!!

I wonder if Username is a reserved word in Access like Date etc. If so, my guess is still correct.

And I read Beacon's reply in DB Forums, and that's what prompts me to think what I am thinking.

.