none
Docmd.RunSQL() VS db.Execute()

    Question

  • Hi, i have a block of codes that works when use Docmd.RunSQL(sql).

    however, when i changed it to db.Execute(sql) , it fails.

    i keep recieving the error "Runtime error 3061", too few parameters.

    My code is as follows

    Dim db As Database
    Dim rs As Recordset
                                                                                        
                                                                                        
                                                               Set db = CurrentDb
                                                              Set rs = db.OpenRecordset("PERSONNEL")
                                                                                        
                                                               Dim sql As String
                                                                                        
                                                             sql = "INSERT INTO PERSONNEL (NRIC, DESIGNATION, DEPT, DEPT_PRIORITY, APPT, CREWSINCE, PESSTATUS, ALLERGIES, MEDICALHISTORY, HEIGHT, WEIGHT, BMI) VALUES (NRICTB.Value, NAMETB.Value, DEPTCB.Value, dept_priority.value, APPTCB.value,CREWSINCETB.VALUE, PESSTATUSTB.VALUE, ALLERGIESTB.VALUE, MEDICALHISTORYTB.VALUE, HEIGHTTB.VALUE, WEIGHTTB.VALUE, BMITB.VALUE )"
    
    
    Docmd.RunSQL(sql)  - this works
    
    db.Execute(sql) - this doesnt work

    kindly advise. the reason why i am switching to db.Execute from Docmd.runSQL is because i want to suppress the warning pop up whenever a change is made to the database tables.

    thanks in advance for any help provided.

    Wednesday, March 27, 2013 4:30 AM

Answers

  • RunSQL automatically attempts to evaluate and fill in values for parameters in the SQL string -- and references to form controls count as parameters.  Execute doesn't do that, so you have to either build the values into the string, or else take extra steps to resolve the parameters.

    An example of building the paramers directly into the SQL string would be this:

    sql = _
        "INSERT INTO PERSONNEL " & _
           "(NRIC, DESIGNATION, DEPT, DEPT_PRIORITY, " & _     
           "APPT, CREWSINCE, PESSTATUS, ALLERGIES, " & _
           "MEDICALHISTORY, HEIGHT, WEIGHT, BMI)" & _
        " VALUES (" & _
           NRICTB.Value & ", " & NAMETB.Value & ", " & _
           DEPTCB.Value & ", " & dept_priority.Value & ", " & _
           APPTCB.Value & ", " & CREWSINCETB.VALUE & ", " & _
           PESSTATUSTB.VALUE & ", " & ALLERGIESTB.VALUE & ", " & _
           MEDICALHISTORYTB.VALUE & ", " & HEIGHTTB.VALUE & ", " & _
           WEIGHTTB.VALUE & ", " & BMITB.VALUE & ")"

    An example of taking extra steps to get Access to resolve the parameters would be this:

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dmi prm As DAO.Parameter            
    Dim sql As String
    
    Set db = CurrentDb
    
    sql = "INSERT INTO PERSONNEL (NRIC, DESIGNATION, DEPT, DEPT_PRIORITY, APPT, CREWSINCE, PESSTATUS, ALLERGIES, MEDICALHISTORY, HEIGHT, WEIGHT, BMI) VALUES (NRICTB.Value, NAMETB.Value, DEPTCB.Value, dept_priority.value, APPTCB.value,CREWSINCETB.VALUE, PESSTATUSTB.VALUE, ALLERGIESTB.VALUE, MEDICALHISTORYTB.VALUE, HEIGHTTB.VALUE, WEIGHTTB.VALUE, BMITB.VALUE)"
    
    Set qdf = db.CreateQueryDef("", sql)
    For Each prm in qdf.Parameters
        prm.Value = Eval(prm.Name)
    Next prm
    
    qdf.Execute dbFailOnError
    
    
    


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by aaronkoh Wednesday, March 27, 2013 1:42 PM
    Wednesday, March 27, 2013 5:29 AM

All replies

  • RunSQL automatically attempts to evaluate and fill in values for parameters in the SQL string -- and references to form controls count as parameters.  Execute doesn't do that, so you have to either build the values into the string, or else take extra steps to resolve the parameters.

    An example of building the paramers directly into the SQL string would be this:

    sql = _
        "INSERT INTO PERSONNEL " & _
           "(NRIC, DESIGNATION, DEPT, DEPT_PRIORITY, " & _     
           "APPT, CREWSINCE, PESSTATUS, ALLERGIES, " & _
           "MEDICALHISTORY, HEIGHT, WEIGHT, BMI)" & _
        " VALUES (" & _
           NRICTB.Value & ", " & NAMETB.Value & ", " & _
           DEPTCB.Value & ", " & dept_priority.Value & ", " & _
           APPTCB.Value & ", " & CREWSINCETB.VALUE & ", " & _
           PESSTATUSTB.VALUE & ", " & ALLERGIESTB.VALUE & ", " & _
           MEDICALHISTORYTB.VALUE & ", " & HEIGHTTB.VALUE & ", " & _
           WEIGHTTB.VALUE & ", " & BMITB.VALUE & ")"

    An example of taking extra steps to get Access to resolve the parameters would be this:

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dmi prm As DAO.Parameter            
    Dim sql As String
    
    Set db = CurrentDb
    
    sql = "INSERT INTO PERSONNEL (NRIC, DESIGNATION, DEPT, DEPT_PRIORITY, APPT, CREWSINCE, PESSTATUS, ALLERGIES, MEDICALHISTORY, HEIGHT, WEIGHT, BMI) VALUES (NRICTB.Value, NAMETB.Value, DEPTCB.Value, dept_priority.value, APPTCB.value,CREWSINCETB.VALUE, PESSTATUSTB.VALUE, ALLERGIESTB.VALUE, MEDICALHISTORYTB.VALUE, HEIGHTTB.VALUE, WEIGHTTB.VALUE, BMITB.VALUE)"
    
    Set qdf = db.CreateQueryDef("", sql)
    For Each prm in qdf.Parameters
        prm.Value = Eval(prm.Name)
    Next prm
    
    qdf.Execute dbFailOnError
    
    
    


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by aaronkoh Wednesday, March 27, 2013 1:42 PM
    Wednesday, March 27, 2013 5:29 AM
  • Forget about .RunSQL method. Why does it exist at all? I think just to support the same macro action.

    First of all, how did you try .Execute method? It should be

    db.Execute sql, dbFailOnError
    Secondly, maybe it is not the whole code you provided above, but I don't see why you need a recordset.

    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru

    Wednesday, March 27, 2013 5:32 AM
  • Oh, all these "VALUE" things are form controls? I thought they were .Value properties of multi-valued fields. That's why it's better to use Me.MyControl.Value, at least, for overall understanding of what the things are.

    Dirk, thanks for the tip about parameters. 


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru

    Wednesday, March 27, 2013 5:39 AM
  • I don't think it would work with that syntax. The command wouldn't know where are the values are coming from.

    Try a different approach. Something like.....

    INSERT INTO personnel SELECT [whateverColumns] FROM [whateverThatTableName].

    If you got the criteria just use the WHERE clause.

    This is some of the currentdb.execute that doesn't work.

    You could use the RunSQL with "Docmd.SetWarnings = False" to disable the warning message and set it back with "Docmd.Setwarnings = True.

    Wednesday, March 27, 2013 5:51 AM
  • Oh, all these "VALUE" things are form controls? I thought they were .Value properties of multi-valued fields. That's why it's better to use Me.MyControl.Value, at least, for overall understanding of what the things are.

    Dirk, thanks for the tip about parameters. 


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru

    The way I see it is that the string syntax in this way. (although I guess/knew that OP might refer to a control)

    Example...."NRICTB.Value". This refers to "MyTableName.MyColumnName" in the Action Query. Since all the input values are of different source, that is to say more than one Tables. But of course, it will never work.

    Not sure why he claims that the insert query works in RunSQL.

    Anyway, "Value" is a reserved word in Access. Should be avoided.

    Wednesday, March 27, 2013 6:52 AM
  • I always found the following article to be quite informative to understand the nuances of these: http://www.databasejournal.com/features/msaccess/article.php/3505836/Executing-SQL-Statements-in-VBA-Code.htm

    Daniel Pineault, 2010-2012 Microsoft MVP
    http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Wednesday, March 27, 2013 12:02 PM
  • Thanks a million Dirk.

    That worked really well for me. The explanation was crystal clear.

    Thanks

    Wednesday, March 27, 2013 1:42 PM