Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Recordset won't open with Order By clause (2000)

    I'm trying to open an ADO recordset based on a SQL string that I have constructed in code. If I include an order by clause in the SQL then I get the following error:
    Method 'open' of object '_Recordset' failed.

    However, if I remove the order by clause the recordset opens OK.
    The full SQL looks like this after references to variables have been resolved:
    SELECT * FROM tbl_FilterMacros_Detail WHERE MacroID = 1 ORDER BY Position;

    I've tried all sorts of combinations of cursor type and lock type without much luck. The one I'm using at the moment (that works if the order by clause is removed) is:
    rst.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly, adCmdText

    The SQL string works fine if I list it in the immediate window then paste it into an Access query.
    The table in question has a compound primary key that includes the field Position.

    I am baffled.

    Ian

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Recordset won't open with Order By clause (2000)

    In itself, you should be open to open a recordset with a sort order. Does it work if you enclose the Order By field in square brackets?

    ... ORDER BY [Position];

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Recordset won't open with Order By clause (2000)

    Try using adOpenDynamic as the cursor type.
    Charlotte

  4. #4
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Recordset won't open with Order By clause (2000)

    Yes, enclosing the order by field in square brackets seems to have worked. Thanks again. However, I am still baffled. Do you have any idea why the brackets are required?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Recordset won't open with Order By clause (2000)

    According to the online help, Position is a reserved word in Jet SQL, so I guess its use without square brackets confused the SQL processor. Using square brackets tells SQL that it is a name.

  6. #6
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Recordset won't open with Order By clause (2000)

    No longer baffled. Thanks.

Posting Permissions

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