Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > Beginning VB 6
|
Beginning VB 6 For coders who are new to Visual Basic, working in VB version 6 (not .NET).
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Beginning VB 6 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 28th, 2005, 04:19 PM
Authorized User
 
Join Date: Jan 2005
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default VB6 database ADO recordset error

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
''''''''''''''''''''''''''''''''''''''''''''

 
Old January 28th, 2005, 04:58 PM
Friend of Wrox
 
Join Date: May 2004
Posts: 642
Thanks: 0
Thanked 43 Times in 42 Posts
Default

Please try this:

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


Om Prakash
 
Old January 28th, 2005, 05:14 PM
Authorized User
 
Join Date: Jan 2005
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old January 28th, 2005, 05:38 PM
Friend of Wrox
 
Join Date: May 2004
Posts: 642
Thanks: 0
Thanked 43 Times in 42 Posts
Default

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
 
Old January 28th, 2005, 06:00 PM
Authorized User
 
Join Date: Jan 2005
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
'''''''''''''''''''''''''''''

 
Old January 28th, 2005, 06:22 PM
Friend of Wrox
 
Join Date: May 2004
Posts: 642
Thanks: 0
Thanked 43 Times in 42 Posts
Default

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
 
Old January 28th, 2005, 07:38 PM
Authorized User
 
Join Date: Jan 2005
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

What is the 'adCmdTable' anyway?




 
Old January 28th, 2005, 08:21 PM
Friend of Wrox
 
Join Date: May 2004
Posts: 642
Thanks: 0
Thanked 43 Times in 42 Posts
Default

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
 
Old January 31st, 2005, 03:08 PM
Authorized User
 
Join Date: Jan 2005
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old January 31st, 2005, 05:06 PM
Friend of Wrox
 
Join Date: May 2004
Posts: 642
Thanks: 0
Thanked 43 Times in 42 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Clone DAO Recordset into ADO Recordset kamrans74 VB How-To 0 March 6th, 2007 11:57 AM
dreamweaver(Database/recordset error ) Divya Kedia Dreamweaver (all versions) 0 March 18th, 2006 10:39 AM
Database Printing Recordset VB6 Hondacars VB How-To 1 March 11th, 2006 06:15 AM
ADO Recordset Scootterp Access VBA 4 February 27th, 2006 06:44 PM
Convert ADO recordset to DAO recordset andrew_taft Access 1 May 5th, 2004 02:31 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.