Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Syntax Error in From Clause (2000)

    I seem to be getting a run of these at the moment - a query string that runs if I paste it into a query object, but that gives an arror when I use it to open a recordset.

    The query string is:
    SELECT Week_Start_Date, Week_End_Date, RWYear, AgrWeek FROM qry_RetailWeeks WHERE [RWDate] = #26 August 2004#

    When I try to open an recordset it gives an 'Error in FROM clause' error. The recordset opening code is:

    rst.Open strSQL, CurrentProject.Connection, , , adCmdTable

    with the query string in strSQL. qry_RetailWeeks is a simple select statement that I used to change the names of two of the fields in the underlying table. I did this when I first got the error because I thought that the field names might be reserved words and therefore the cause the error. The SQL for sql_RetailWeeks:

    SELECT RetailWeeks.Date AS RWDate, RetailWeeks.AgrWeek, RetailWeeks.Week_Start_Date, RetailWeeks.Week_End_Date, RetailWeeks.Year AS RWYear
    FROM RetailWeeks;

    Is there something obvious in my SQL or, is it possible that ADO doesn't like reserved words as columns column names even when it is 'once removed' from the underlying table? RetailWeeks is a table used everywhere by the client and cannot be changed.

    Thanks again

    Ian

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

    Re: Syntax Error in From Clause (2000)

    Is your query called qry_RetailWeeks or sql_Retailweeks? You mention both names in your post.

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

    Re: Syntax Error in From Clause (2000)

    Sorry, the line that says 'The SQL for sql_RetailWeeks' should read 'The SQL for qry_RetailWeeks'.
    There's a table called RetailWeeks.
    There's a query called qry_RetailWeeks, which contains a subset of the columns in RetailWeeks and renames two that look as though they have reserved words as column names.
    Then there's a a string variable called strSQL that I put the SQL string into.
    Using either the table RetailWeeks, or the query qry_RetailWeeks in the FROM clause gets the same error.

    Ian

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

    Re: Syntax Error in From Clause (2000)

    The date is not valid. SQL requires dates in a US format of month/day/year. If you paste it into a query, it may be able to resolve the date in the query grid, but not if you pass it into an ADO recordset. I would also be suspicious of that fields Year and Date, especially if you're trying to query a SQL Server database.
    Charlotte

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

    Re: Syntax Error in From Clause (2000)

    Charlotte may well have a valid point. Are you working with SQL Server tables? That might impose more restrictions than working with Access tables.

    (As a test, I created a small table in an Access 2000 format database with fields Date and Year - something I would never do ordinarily - and used an SQL string similar to yours, with the date written out instead of using mm/dd/yy format. Opening an ADO recordset based on this SQL was no problem; it returned the correct record count. So apparently, ADO within Access itself can handle field names such as Year and Date, and dates written as 26 August 2004.)

  6. #6
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax Error in From Clause (2000)

    Just out of curiousity, does it make a difference if you drop the adCmdTable at the end?

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

    Re: Syntax Error in From Clause (2000)

    It might. The correct setting for passing in a SQL string is adCmdText
    Charlotte

  8. #8
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax Error in From Clause (2000)

    That's what I thought, I always leave it blank, which defaults to -1, which I am not sure what that is. But I always thought adCmdTable, and adCmdTableDirect was used when you refer to a table, and not to a SQL string. When I saw this post, I checked the help file, and quite frankly felt more confused about the issue. I have opened tables directly with ADO (if I am just adding a record), and put "tblSomething" where a SQL statement would go, and I have problems if I don't use adCmdTableDirect (though I don't think I tried adCmdTable).

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

    Re: Syntax Error in From Clause (2000)

    You use adCmdTableDirect when you're using JRO, if I remember correctly. If you are just passing in the name of a table, you use adCmdTable. The argument tells ADO how to evaluate the source, is it a table, a SQL string, or what? Even if you use a command object and specifically set its commandtext property, you still need to pass in the adCmdText argument to avoid unexpected weirdness, at least in all ADO versions up to 2.7. I can't say I've tried anything else since it became a habit with 2.1. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  10. #10
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax Error in From Clause (2000)

    Aha. adCmdTableDirect has always worked for me for naming a table in the Open method. Honestly don't think I tried adCmdTable. Quite frankly, for the past year or two, I simply leave the default, because I'm using SQL statements, and if I am just adding a record, I don't even use a recordset, I just use INSERT INTO statements....though, if I had a 'massive' data dump, I would go back to recordsets.

    Either way, it'll be interesting to find out if the Options argument is the cause of the actual problem.

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

    Re: Syntax Error in From Clause (2000)

    That was it, adCmdText should have been used, not adCmdTable. The code now works.

    I have written rst open ststements like this so often that I think I must have got sloppy with the intellisense (is that the right word?) this time, you practically have to type in all of acCmdText, because acCmdTable has almost the same spelling and comes up first in the list, and I probably did this one in a rush. Because they look so similar I didn't spot the difference.

    On the date format, I've been using the long date format because (a) it works with Jet, and ([img]/forums/images/smilies/cool.gif[/img] there is no possible ambiguity with dd/mm or mm/dd. However, I have tripped up with an application that I sent to the US (I'm based in Britain) where the long date format seems to include a day name and a comma. The comma I think caused problems. Is there a better, use everywhere, way of formatting dates that are to be put in SQL strings? Does everybody write their own format to US style function?

    Sorry for wasing everybody's time on the adCmdText issue,

    Ian

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

    Re: Syntax Error in From Clause (2000)

    I routinely put "#" & Format(DateVariable, "mm/dd/yyyy") & "#" into my SQL strings if I need a date, I haven't bothered to write a custom function for it.

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

    Re: Syntax Error in From Clause (2000)

    We all fall over things like that sometimes, and it's never a waste of time if someone else can benefit from it.

    As to the date, our apps are used internationally, so we use a medium format date in displays to avoid confusion; and we created a function we called USDate that takes the passed in date and converts it to a mm/dd/yyyy format for use in SQL statements. It isn't so bad between US and UK formats, but it can get tricky with some other regional settings where delimiters in a date may be spaces or something else besides "/" or "-", so you have to do some testing there as well. One thing to watch out for is that the domain functions like DLookup, etc., also need the correct date format in their WHERE clauses. We tripped over that when we were implementing internationalization in our code too.
    Charlotte

  14. #14
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax Error in From Clause (2000)

    Not a waste of time. Sometimes we all need a second pair of eyes on something, that's why the lounge is here!

    As for the date formatting, if you stick with Access, you can't go wrong with #" & dteSomDateVariable & "# because the date variable is a constant format (double variable, whole numbers for days, decimal for time), which is Universal in Access. If you want it to work in SQL server, then I believe SQL Server wants MM/DD/YYYY.

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

    Re: Syntax Error in From Clause (2000)

    It isn't SQL Server, Drew, it's the SQL language standard that requires dates in US format of mm/dd/yy or mm/dd/yyyy.
    Charlotte

Page 1 of 2 12 LastLast

Posting Permissions

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