#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Location
    Chico, CA
    Posts
    14
    Rep Power
    0

    syntax error in from clause vb6


    Hello all,

    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
    OLE Automation


    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

    Greg
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    No es importante
    Posts
    2,065
    Rep Power
    14
    Spit the query out to the screen just to verify what is being submitted.
    El éxito consiste en una serie de pequeñas victorias día a día

    MySQL, MS SQL, MS ACCESS, Oracle Database Manager - http://victorpendleton.net/products/psdviewer.html
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Location
    Chico, CA
    Posts
    14
    Rep Power
    0
    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.
  6. #4
  7. No Profile Picture
    Overly white
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Location
    Fresno, CA
    Posts
    83
    Rep Power
    12
    Code:
    cmd.CommandText = "select * from User ; "
    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.
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Location
    Chico, CA
    Posts
    14
    Rep Power
    0
    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.
  10. #6
  11. No Profile Picture
    Overly white
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Location
    Fresno, CA
    Posts
    83
    Rep Power
    12
    how about a semi colon after the connection string.
    Code:
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\grega\csci 273\phase2\3NF.mdb;"
    It's just got to be a semi colon

    Can you run any other query? Maybe try a different table.
  12. #7
  13. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Location
    Chico, CA
    Posts
    14
    Rep Power
    0
    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:

    conn.Open "DSN=stormnet;uid=sa;pwd=;database=stormnet"

    then the query works fine. I would much rather not use the System DSN, does anybody have any ideas?

    Greg

IMN logo majestic logo threadwatch logo seochat tools logo