Difference between revisions of "Documentation/FAQ/Databases/MS Access/Is there an equivalent to DoCmd RunSQL"

Latest revision as of 12:24, 12 August 2022

Is there an equivalent to DoCmd RunSQL?

Yes, in AOO Base you would use either a Statement or PreparedStatement object to achieve the same function. With AOO Base you must also decide between using execute and executeUpdate.

For example if you had the VBA line

  DoCmd RunSQL( "SELECT * FROM tContacts" )

it would become in AOO Base

  Dim oStatement as variant
 
  oStatement = OForm.ActiveConnection.CreateStatement
    ' Where oForm is a DataForm object
    ' NOTE you could do exactly the same thing with
    ' a resultSet or rowSet object also
    ' oStatement = OResultSet.ActiveConnection.CreateStatement
 
  oStatement.execute( "SELECT * FROM ""tContacts"""  )

If on the other hand the VBA line where

  DoCmd RunSQL( "DELETE FROM tContacts WHERE Status = 'INACTIVE'" )

then we would use the executeUpdate method on a statement

  oStatement.executeUpdate( "DELETE FROM ""tContacts"" WHERE ""Status"" = 'INACTIVE'"  )

The rule is -

• if your SQL statement will return records you use execute or executeQuery.
• if the SQL statement returns no records then you must use executeUpdate.