Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Colorado, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Opening a Recordset using an SQL Source (Access 2000)

    I'm attempting to open a DAO Recordset using a SQL statement as the source parameter. When I run the code I get a Run-time error 3061: Too few parameters. Expected 4. I'm simply opening the SQL statement, contained in a variable as a snapshot recordset. The SQL string is long but look OK as far as I can see.

    I also find that if I do a DoCmd.RunSQL, it returns an error stating the the action requires an argument consisting of an SQL statement. the assignment is as follows:

    strSQL = "SELECT [" & strSTable & "].Field1, [" & strSTable & "].Field2 [" & strSTable & "].Field3 FROM " & strSTable & " LEFT JOIN " & strTTable & " ON [" & strSTable & "].Field1 = [" & strTTable & "].Field1 WHERE ((([" & strTTable & "].Field1) Is Null));"

    strSTable and strTTable contain a string like 344_101 which is the name of a table in the database.

    Obviously, something is wrong with the SQL statement, but I can't see it. Has anyone experienced this problem before? Any ideas of what to do to fix this problem?

    Thanks.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening a Recordset using an SQL Source (Access 2000)

    I think you forget a comma after field2
    Francois

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Colorado, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening a Recordset using an SQL Source (Access 2000)

    Sorry. The comma is there-I just left it out when copied it to this message.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening a Recordset using an SQL Source (Access 2000)

    Put a breakpoint on the line next strsql = ....
    Go to the immediate window.
    enter ? strsql
    Copy the returned line
    Open a new query without adding a table.
    Select View, SQLView and paste the line.
    Try to run the query.
    What error do you get ?
    Francois

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening a Recordset using an SQL Source (Access 2000)

    Sorry, I have forget to say to run the application after putting the breakpoint.
    When the program breaks then go to the immediate window.
    Francois

  6. #6
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Colorado, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening a Recordset using an SQL Source (Access 2000)

    It runs fine - no errors.

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening a Recordset using an SQL Source (Access 2000)

    I'm out of ideas so I try to create a little db with these code and it seems to work.
    I'm attaching it, maybe you see a difference with yours.
    Open the module1 and run function test.
    Attached Files Attached Files
    Francois

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

    Re: Opening a Recordset using an SQL Source (Access 2000)

    DoCmd.RunSQL executes an action query. It does NOT return a recordset.
    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
  •