April 24th, 2003, 01:38 PM
syntax error in from clause vb6
I'm attempting to open a recordset from an access 2000 .mdb file using vb6 service pack 5, ActiveX ADO 2.7, and I'm gettting a "syntax error in from clause" on the simplest query.
Here's the code:
Private conn As New ADODB.Connection
Private cmd As New ADODB.Command
Private rs As New ADODB.Recordset
Private iTries As Integer
Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpbuffer As String, nSize As Long) As Long
Public OK As Boolean
Private Sub Form_Load()
Dim sBuffer As String
Dim lSize As Long
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\grega\csci 273\phase2\3NF.mdb"
Set cmd.ActiveConnection = conn
cmd.CommandText = "select * from User"
rs.CursorLocation = adUseClient
rs.Open cmd, , adOpenStatic, adLockBatchOptimistic
here's what's check in Project->References:
Visual Basic for Applications
Visual Basic runtime objects and procedures
Visual Basic objects and procedures
Microsoft ActiveX Data Objects 2.7 Library
in that priority order.
nothing but default controls are checked in Project->Components
I can run the same query "select * from User" in Visdata and get no error.
Thanks for the help
April 24th, 2003, 01:44 PM
Spit the query out to the screen just to verify what is being submitted.
April 24th, 2003, 02:02 PM
I tried both a debug.print cmd.CommandText and a MsgBox cmd.CommandText, , "Query"
and both look fine, just as it's typed in the code.
April 24th, 2003, 02:25 PM
I think you need to use a semicolon at the end of your SQL. I'm not sure on that though it may be an optional thing.
cmd.CommandText = "select * from User ; "
April 24th, 2003, 02:38 PM
nope. Adding a semicolon to the end of the query didn't fix it. I had actually already tried it, but decided to give that another shot.
April 24th, 2003, 02:49 PM
how about a semi colon after the connection string.
It's just got to be a semi colon
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\grega\csci 273\phase2\3NF.mdb;"
Can you run any other query? Maybe try a different table.
April 24th, 2003, 03:00 PM
adding a semicolon to the end of the connection string gives me no change.
However, if I switch to a System DSN in ODBC data sources (control panel), and use the following connection string:
then the query works fine. I would much rather not use the System DSN, does anybody have any ideas?