Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Apr 2013
    Posts
    10

    Unanswered: access sql, simplest question yet

    Hi Helpful People,
    Hanging my head in shame, I'm using Acc2010 and can't seem to make Access-generated SQL statements work. I've attached a simple code snippet to illustrate. The SQL statement is a copy/paste straight from the query in design view, so you'd think it "must" be right, but no: "A RunSQL action requires an argument consisting an SQL statement". (Showing my age, but this worked a treat in Acc97 and 2000.)
    Yes, there's a semi-colon at the end of the statement and quote marks at the beginning and end for assigning to a string variable. Please don't tell me to put my glasses on, I've been looking at this for a week and just about losing my mind. ANSI92 is NOT enabled. Thanks very much for your help.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Perhaps I've misplaced my glasses: I don't see any SQL code here?
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2013
    Posts
    10
    Sorry, thought I'd attached a code snippet .txt file. Here it is:
    ===
    Private Sub cmd1_Click()
    Dim sqlstr As String
    sqlstr = "SELECT Membership.[Last Name], Membership.[First Name], Membership.[S-opt] FROM Membership WHERE (((Membership.[S-opt])>0));"
    DoCmd****nSQL (sqlstr)
    End Sub
    ===
    The line "sqlstr=" is a single line in code. It's wrapped to the next line here.
    Is that better?

  4. #4
    Join Date
    Apr 2013
    Posts
    10
    The DoCmd line should read DoCmd****nSQL. Don't know how it got messed up in copy/paste. That's Do Cmd(dot)Run_SQL as documented.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    SQL DML (Data Manipulation Language) statement can use 4 "verbs": SELECT, which is used to retrieve data in a data set, and DELETE INSERT, UPDATE, which change the data in one or several tables.

    Here you use a SELECT statement, meaning that you expect a data set to be returned. However, you do not provide any container for this data set. DoCmd(dot)RunSQL or the alternate CurrentDb.Execute are used with DELETE, INSERT and UPDATE SQL statements. For a SELECT statement, the syntax is different as you must first provide a container for the returned data set (by declaring it) then assign the SELECTed values to this container.

    One of the most usual method when you work with an Access database with the tables also residing in an Access database is:
    Code:
    Sub Dataset()
    
        Dim rst As DAO.Recordset
        Dim strSQL As String
        
        strSQL = "SELECT Tbl_Items.Item_Id, Tbl_Items.Item_Type, Tbl_Items.Description " & _
                 "FROM Tbl_Items " & _
                 "WHERE Tbl_Items.Inactive = False;"
        Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
        With rst
            Do Until .EOF
                '
                ' Process the RecordSet, one row at a time.
                '
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
        
    End Sub
    See Access help for the properties and methods of the DAO.RecordSet and also for the OpenRecordset method.
    Have a nice day!

  6. #6
    Join Date
    Apr 2013
    Posts
    10
    Hi Sinndho,
    Thanks for your advice, but I'm still struggling. I've got the SQL to run, but it keeps returning the first record instead of moving through the main table. I do a Debug(dot)Print of the record retrieved, nothing else (code snippet attached).
    You said I need to create a 'container' for the returned records, but can't figure out how to do that. "temp" is as good a container name as any, and can be deleted at the end. I'm sure this worked OK in Acc97 (although I really prefer the DoCmd(dot)---SQL construct). So, if I must use the DAO paradigm, how do I tie all this together? Please go easy on a retread newbie! Thanks.dawg-dbforum.txt

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What does this output:
    Code:
    ...
    Do Until .EOF
        Debug.Print mycount
        .MoveNext
        mycount = mycount + 1
    Loop
    ...
    George
    Home | Blog

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Code:
    Private Sub cmd1_Click()
    Dim strSQL As String
    Dim dbs As dao.Database
    Dim rst As dao.Recordset
    Set dbs = CurrentDb
    strSQL = "SELECT Membership.[ID], Membership.[Last Name], Membership.[First Name], Membership.[S-opt] FROM Membership WHERE (((Membership.[S-opt]))<>0);"
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
    With rst
        mycount = 0 'initialise counter to zero
        if .bof = true and .eof = true then 'we didn't find any rows
            debug.print "Oh shaggy no rows returned." & vbcrlf & strsql
        else 'we found some rows
            Do until .eof = true 'iterate through to the end of the recordset
                mycount = mycount + 1 'add if we found a record
                If IsNull([S-opt]) Then
                    Debug.Print mycount; [ID]; " "; [Last Name]; " "; [First Name]
                Else
                    Debug.Print mycount; [First Name]; [Last Name]; [S-opt]
                End If
                .MoveNext
            Loop 'finished processing that row
        endif
        .close
    End With
    Set rst = Nothing
    End Sub
    make certain you are explicitly declaring all variable before first use
    use
    Code:
    option explicit
    as the first or second line in every code module where you are using code

    when posting code here please paste the relevant code here, wrapped in [ c o d e ] and [ / c o d e ] (without the spaces to [ / c o d e ] becomes [/code]. it makes it easier for contributors to look at your code
    if you are using the 'go advanced', instead of 'quick reply' then select the code you are psoting and then tap the # button
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Apr 2013
    Posts
    10
    Thanks for the tip, Healdem.
    The SQL is still returning only the first record repeatedly, but mycount is still being incremented. You would think that if there was a problem with my SQL statement Access would have complained. I also set Option Explicit, and had to DIM mycount. Strangely, the record being returned has [S-opt] as "Null". This can't exactly be rocket science, and it's a copy/past directly from Query design view -- I've just added quotes. Any ideas? Thanks.

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    did you insert option explicit
    what I think is happenig here is that you are not explicitly telling the code to use values from the recordset. I can't remember if you use ! or ., but lets assume its !
    try:-
    Code:
    If IsNull(![S-opt]) Then
      Debug.Print mycount; [ID]; " "; ![Last Name]; " "; ![First Name]
    Else
       Debug.Print mycount; ![First Name]; ![Last Name]; ![S-opt]
    endif
    End If
    if that fails replace the ! with .

    the best thing to do in these sort of circumstances is to put a breakpoint ont he code and then step though (using F8) and then examining variables using the immediate panel
    eg
    ?rst!S-opt

    you can even set values
    myvariable = 5
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Apr 2013
    Posts
    10
    Excellent Healdem!! Works a treat with the "bang" character explicitly defining the fields. I'd already specified Option Explicit and set a break point, but just couldn't see it. (Would still prefer the "simpler" DoCmd[dot]---SQL though). So is the snapshot I declared in rst the 'container' to hold the returned records, and do I need to open it as dbOpenSomethingElse like dbOpenTable? Thanks very much for your patience.

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Would still prefer the "simpler" DoCmd[dot]
    the docmd doesn't return anything. so its not useful for a select statement (unless I suppose a select INTO). its designed to update the db using an UPDATE query or for altering a table, soemthign that doesn't neccesarily require something to be returned. although personally I prefer to use a recordset so I can make certain that what was requested int he SQL has actually done something.

    incidentally the reason why docmd. runql copmes out as docmd*** is that we had a prolfiic spambot posting references to rumainina website so the . ru bit is modified on this website

    as to
    do i need to open it?
    I dunno, I dont' know what you are trying to achieve with this recordset.
    Im guessing you probably want to use this as feedstock in a form or report, but you haven't said what you want to do, and My Crystal ball is away for its annual cleaning and polishing ao I cna't suggest what to do with it
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Rumania?! I think you mean Russia
    George
    Home | Blog

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by gvee View Post
    Rumania?! I think you mean Russia
    good point and well made
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    Apr 2013
    Posts
    10
    Hi Healdem,
    At the moment, I'm just trying to prove 'proof of principle', to (1) get my head 'round dao Recordsets and (b) use code to construct & run (Access) SQL. I take your point that in most cases it'll be UPDATE / DELETE / APPEND type tasks, but sometimes I just want to see the result in Datasheet format. I might even eventually get good enough to do a JOIN, but don't hold your breath. And yes, I can foresee several occasions where it will be feeding a report.

    So far, they are Homer Simpson "doh!Recordsets" to me, so would appreciate any pointers in the right direction. As usual, Microsoft Help is an oxymoron, like Military Intelligence. Thanks again, and
    Best regards,
    Dave

Posting Permissions

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