Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2003
    Posts
    3

    Unanswered: DAO.OpenRecordset Problem

    Hello,

    I am using MS Access 97 and am having the following trouble:

    Background:

    I have a report on which I select the record source dynamically. For this, I use the following code in the "On Open" event:

    ----- Code----------
    Private Sub Report_Open(CANCEL As Integer)
    Dim rstClassList As Recordset
    Dim sqlStatement As String
    DoCmd.RunMacro "student_school_selection2.OpenDialog", 1
    On Error GoTo Report_OpenErr
    sqlStatement = "SELECT field1, field2 " & _
    "LONG LIST " & _
    "FROM table " & _
    "WHERE Condition"
    Set rstClassList = CurrentDb.OpenRecordset(sqlStatement, dbOpenDynaset)
    Me.RecordSource = rstClassList.NAME
    rstClassList.close
    Set rstClassList = Nothing

    Report_OpenErr:
    MsgBox (Err.Description & " " & Err.Number)
    Exit Sub
    End Sub
    ------------ Code -----------------------------------------------

    Problem:
    The problem occurs when I have a long string in sqlStatement. My sql string is quite long, around 600 characters. Most of it is taken up in specifying the fields to select. I have found that when the sql string is long, MS Access gives me a error box stating: "Microsoft Access can't find the table or query: sqlStatement".

    When I truncate my SQL statement, the report opens without a problem. Not sure why this is happening.

    Any hints/help will be appreciated.

    Thanks

    Bd

  2. #2
    Join Date
    Jun 2004
    Location
    Terrapin Nation
    Posts
    205
    One thing you may want to try is copying the SQL of a very long SQL statement and pasting it in the Recordsource property of the report to see if a hardcoded long SQL statememt works.

    The only other thing is I am thinking your:

    Me.RecordSource = rstClassList.NAME


    should be

    Me.RecordSource = rstClassList.SQL

  3. #3
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Also you dont need to open the recordset at all to get the sql to the recordsource...

    Regards

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487
    I dunno....Sounds like you're listing ALL the fields from your table within the SQL statement.

    Try:

    sqlStatement = "SELECT * FROM table WHERE Condition"

    Unless of course I heard wrong and you are selecting only specific records from your table(s).



  5. #5
    Join Date
    Dec 2003
    Posts
    3

    Thumbs up Recordset

    Hi All,

    Thank you all for replying. Here is what I have tried and what has worked:
    Setting:
    Me.RecordSource = rstClassList.SQL
    gives me an error saying the method is not found. I am using VB 5. I have not sure if that's got anything to do with it.

    "Also you dont need to open the recordset at all to get the sql to the recordsource..." as suggested by namliam. I am not sure what you mean by that. Again, I am newbie at all this, so excuse my ignorance.

    Finally, as mentioned in the original question replacing select fields by select * does work. So I have just done that. So thank you CyberLnyx. Just curious, if you are selecting all the fields in a table, do you have to use a *. Why can't you just list all of them?

    Cheers,

    Bd

  6. #6
    Join Date
    Nov 2003
    Posts
    1,487
    Quote Originally Posted by baijud
    Just curious, if you are selecting all the fields in a table, do you have to use a *. Why can't you just list all of them?
    No...you don't have to but....Good Lord...why would you want to if you don't have to. Using the * makes the SQL far more readable.


  7. #7
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Using a * retrieves all columns in a table. There are 2 reasons why you shouldnt use it.
    1) Mosttimes you dont need all columns
    2) Using * works a bit slower then listing the columns right off...
    OK we are talking milliseconds but ....

    If you are trying to input the sqlStatemant into the RecordSource there is no need to use an intermediat recordset. Just use Me.Recordsource = sqlStatement
    Be aware tho there is a limmit to the number of line continuations: & _
    You may want to look at that...

    Regards

  8. #8
    Join Date
    Nov 2003
    Posts
    1,487
    Quote Originally Posted by namliam
    Be aware tho there is a limmit to the number of line continuations: & _
    You may want to look at that...
    Hence... *


  9. #9
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    Another thing to keep in mind about using *:

    1) Not all data providers let you get away with using * and you are therefore forced to list the field names (sometimes all of them). I encountered that error once and it took a while to figure out it was the * causing the problem (of course there is no problem using * with Access).

    2) Speed, When you are returning huge recordsets every extra field slows down the reurned recordset.
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  10. #10
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Quote Originally Posted by CyberLynx
    Hence... *

    Still no excuse, the way to go is NOT use * and solve the line continuations in another way e.g. Create a strSelect, strFrom, strWhere
    strSelect = "Select " & _
    "Field1 as bla, " & _
    "Lots more fields as BlaBla, "
    strFrom = "From " & _
    "Lots off tables"
    strWhere = "Where " & _
    "Lots of ands and ors"

    then execute sql: Currentdb.execute strSelect & Strfrom & strWhere

    Or build it like so:
    sql = "Select "
    sql = sql & "Fields, "
    sql = sql & "MoreFields "
    sql = sql & "From "
    ....

    Regards

Posting Permissions

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