The following is the code I am trying to use with an Access 2000 .mdb file. The table name which I want to access is "User". The connection seems to work, but the following error comes up for the recordset line:

Run-time error '-2147217900 (80040e14'):
Syntax error in FROM clause.

I have tried to change the syntax about 5 different ways. What is wrong?! Will someone please tell my why this does not work, Thanks!


''''''''''''''''''''''''''''''''''''''''''''
Dim DBpath As String
Dim DBconnection As New ADODB.Connection
Dim LOGINrecordset As New ADODB.Recordset


Private Sub Form_Load()

DBpath = (App.Path & "\TS99_v101.mdb")
DBconnection.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & DBpath & ";"
LOGINrecordset.Open "SELECT * FROM User", DBconnection, adOpenStatic, adLockOptimistic, adCmdTable

End Sub
''''''''''''''''''''''''''''''''''''''''''''

Please try this:

LOGINrecordset.Open "SELECT * FROM [User]", DBconnection, adOpenStatic, adLockOptimistic, adCmdTable


Om Prakash

Came up with the same error. Thanks for trying though.

Perhaps the problem is the:
PROVIDER=Microsoft.Jet.OLEDB.4.0

Most code I have found uses version 3.51, but that did not work with the new versions of Access. Perhaps 4.0 changes the language. Sorry, I am a beginner, but the code seems like it should work according to my research

Hi,

Initially i thought user might be a keyword so it is giving error.

but this code should work with most of ther versions of access, and Jet 4.0 i think..

Please check the following:

http://www.adopenstatic.com/faq/80040e14.asp


Om Prakash

You were right about the 'User' as being reserved. Thought you may have solved the problem, but even after changing the Table name, I still came up with the same error. I will list my newest code, and [u]thanks for the help</u>. Maybe it is the database file?


''''''''''''''''''''''''''''''''''''''''''
im DBpath As String
Dim DBconnection As New ADODB.Connection
Dim LOGINrset As New ADODB.Recordset

Private Sub Form_Load()
DBpath = (App.Path & "\TS99_v101.mdb")
DBconnection.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & DBpath & ";"
LOGINrset.Open "SELECT * FROM [Login]", DBconnection, adOpenStatic, adLockOptimistic, adCmdTable
End Sub
'''''''''''''''''''''''''''''

Please check this:

''''''''''''''''''''''''''''''''''''''''''
im DBpath As String
Dim DBconnection As New ADODB.Connection
Dim LOGINrset As New ADODB.Recordset

Private Sub Form_Load()
DBpath = (App.Path & "\TS99_v101.mdb")
DBconnection.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & DBpath & ";"
LOGINrset.Open "SELECT * FROM [Login]", DBconnection, adOpenStatic, adLockOptimistic
End Sub
'''''''''''''''''''''''''''''

It should work


Om Prakash

It worked! THANK YOU, THANK YOU! I have been trying to figure this out for days.

What is the 'adCmdTable' anyway?

Following are Command Type Constants:

Const adCmdText = 1
Const adCmdTable = 2
Const adCmdUnknown = 8

adCmdTable can be used when u give only table name:
rec.Open "employees", conn, adOpenForwardOnly, adLockReadOnly, adCmdTable

These Options tell the recordset object where the source of the data is. For example, the data could be in a:

SQL command--ADO Constant: adCmdText
a query or stored procedure--ADO Constant: adCmdStoredProc
a table--ADO Constant: adCmdTable
an unknown type.--ADO Constant: adCmdUnknown

Hope this Helps..


Om Prakash

Thanks Again, but I still have an issue. I am simply trying to Have a user Login. With your help I have Set up the DB connection and recordset. When trying to set the Index I come up with an error. From what I have found, there needs to be an "adCmdTable" on the recordset which is what caused the problem in the first place. Tried to attach the "Const" with no results.

I have attached all the code because this is becoming frustraiting. Most of the reference code I have is for RDO databases, but it should be close to the same, right?


Dim DBpath As String
Dim DBconnection As New ADODB.Connection
Dim LOGINrset As New ADODB.Recordset

Dim LogInName As String
Dim LogInPassword As String
Dim BadLogInCount As Integer


Private Sub Form_Load()
DBpath = (App.Path & "\TS99_v101.mdb")
DBconnection.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & DBpath & ";"
LOGINrset.Open "SELECT * FROM [Login]", DBconnection, adOpenStatic, adLockReadOnly
End Sub


Private Sub Form_Unload(Cancel As Integer)
DBconnection.Close 'Close connection to database'
Load frmPM_Main_Menu
frmPM_Main_Menu.Show
End Sub


Private Sub txtLogInPassword_KeyPress(KeyAscii As Integer)
If KeyAscii = 13 Then 'ENTER key press'
        LogInName = UCase(txtLogInName.Text)
        LogInPassword = UCase(txtLogInPassword.Text)
        DBconnection.LOGINrset.Index = "UserName" 'Gives an Error, also tried using "PrimaryKey"'
        DBconnection.LOGINrset.Seek "=", UCase(txtLogInPassword.Text)
        If DBconnection.LOGINrset.NoMatch = True Then
                    MsgBox "INVALID LOGIN NAME..."
                    Unload Me
                    End
        End If
        If UCase(txtLogInPassword.Text) = DBconnection.LOGINrset("Password").Value Then
                    Unload frmINTRO_window
                    Else
                    BadLogInCount = BadLogInCount + 1
                    If BadLogInCount = 3 Then
                            MsgBox "INVALID LOGIN NAME...CLOSING."
                            Unload Me
                            End
                    Else
                            txtLogInName.Text = ""
                            txtLogInName.SetFocus
                            MsgBox "INVALID PASSWORD..."
                    End If
        End If
End If
End Sub

Please check the following code:

Private Sub Form_Load()
DBpath = (App.Path & "\TS99_v101.mdb")
DBconnection.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & DBpath & ";"
End Sub


Private Sub Form_Unload(Cancel As Integer)
DBconnection.Close 'Close connection to database'
Load frmPM_Main_Menu
frmPM_Main_Menu.Show
End Sub


Private Sub txtLogInPassword_KeyPress(KeyAscii As Integer)
If KeyAscii = 13 Then 'ENTER key press'
        LogInName = UCase(txtLogInName.Text)
        LogInPassword = UCase(txtLogInPassword.Text)
        LOGINrset.Open "SELECT * FROM [Login] where username = '" & LogInName & "'", DBconnection, adOpenStatic, adLockReadOnly
        if LOGINrset.EOF then
            'Error message that login does not exits
            MsgBox "INVALID LOGIN NAME..."
               Unload Me
        else
            'Check here for password existance...
            'Or this can be ehecked in the above query itself.
        end if
        LOGINrset.close
End If
End Sub



Om Prakash