Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    What's wrong with this code?

    I keep getting a VBA runtime error "Error in From clause" in the following code. Yet when I put the same SQL statement into a query and substitute the query into the
    ADO open statement it works just fine. I'm a bit puzzled how this can be. Can someone point out the obvious?

    Dim CurConn As ADODB.Connection
    Dim rstTOTAL As New ADODB.Recordset
    Set CurConn = CurrentProject.Connection

    rstTOTAL.CursorType = adOpenKeyset
    rstTOTAL.LockType = adLockOptimistic
    rstTOTAL.Open "SELECT [Sales].[OrderDate], Sum([SalesDetails].[Price]) AS SumOfPrice" _
    & " FROM Sales INNER JOIN SalesDetails ON [Sales].[Sale ID]=[SalesDetails].[Sale ID]" _
    & "GROUP BY [Sales].[OrderDate]HAVING (((Sales.OrderDate) = Date))" _
    & "ORDER BY [Sales].[OrderDate] DESC", CurConn, , , adCmdTable
    <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>

  2. #2
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Oregon, USA
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What's wrong with this code?

    Hi David,

    I'm not sure this is it, but...

    I've always run into problems breaking SQL statements into multiple lines. In this case it breaks right before the FROM statement and might be thinking there's an error in the FROM statement because it isn't there.

    I've gotten around this in the past by either <shuddder> putting the SQL statement into one line or assigning the SQL statement to a string.

    HTH,

    Chris

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What's wrong with this code?

    Thanks but I tried all that - it gives the same error message each time exactly. I am not sure what you mean by
    &lt - is this the same as _ & ?
    I suspect it is some fussy requirement in ADO syntax although I can't see what

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

    Re: What's wrong with this code?

    In addition to making sure you have spaces before your Group BY and Order By clauses, change the option to adCmdText, since that's what you're passing into the Open method rather than a table.
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What's wrong with this code?

    Thanks Charlotte! I knew it was something simple - I'm just
    starting to use ADO instead of my usual DAO so I wasn't aware of the meaning of this last parameter and the error message is rather misleading as it made me think the SQL was wrong rather than the Open Method syntax was inappropriate in this case.
    Problem solved!
    <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

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

    Re: What's wrong with this code?

    I sympathize. I've been working with it for awhile, and I still run into brick walls regularly. The object model is simple, but using it isn't. <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15> To make it even more maddening, there are properties (like CommandType) that don't necessarily work. If you specify a CommandType, you still have to pass the commandtype argument in Options or it will simply fail.

    I like the Visual Basic Developer's Guide to ADO, but I wish there were one just for Access (extracting it from the Access Developer's Handbook is way work!). <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>
    Charlotte

Posting Permissions

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