Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    67

    Unanswered: Syntax Error in "rs.open . . ." statement

    I just started getting error messages from an Access module that was working fine. I believe it has something to do with my "References", but I can't figure out what is wrong.

    My references are and always have been:
    Visual Basic for Applications
    Microsoft Access 10.0 Object Library
    OLE Automation
    Microsoft ActiveX Data Objects 2.5 Library
    Microsoft Excel 10.0 Object Library
    Microsoft Office XP Web Components
    Microsoft ADO Ext. 2.8 for DDL and Security
    Microsoft DAO 3.6 Object Library
    Microsoft Visual Basic for Applications Extensibility 5.3

    My code looks like this:

    Dim rs As ADODB.Recordset
    Dim strTest As String
    Set rs = New ADODB.Recordset
    rs.ActiveConnection = CurrentProject.Connection
    strTest = "qryRecords"
    rs.Open "Select * from " & strTest

    I keep getting the following error message at the rs.Open "Select . . . Statement:

    Syntax error in FROM clause

    Any help would be greatly appreciated.

    Thank you.

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    This is going to sound wierd - but try it.

    Create a new module and copy and paste the code over. There are many things that start acting squirly after a Windows update. Recently, Microsoft has posted a flury of updates and many things that used to work just fine started acting up. In some of the more bizarre cases, I found it easiest to simply recreate the object.

    Good luck!

  3. #3
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    If that didn't work, try removing and resetting the references.

    Also, does similar code work elsewhere in your application? How about in other files?
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    I would try

    replace
    rs.Open "Select * from " & strTest

    rs.Open "Select " & strTest & ".* from " & strTest

    I allways add the Tablename to the .*
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  5. #5
    Join Date
    Feb 2004
    Posts
    67

    Thank you

    Todd & StePhen,

    Thank you both very much for your help. It turns out that the variable I was trying to open had a hyphen in its name. Apparently, a Query's name cannot contain a hyphen when trying to use VB.

    Thanks again.

    Jim

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •