[Resolvido]Qual a diferença entre DoCmd.RunSQL e CurrentDb.Execute( )

Alguem poderia me dizer qual a diferença entre DoCmd.RunSQL e CurrentDb.Execute( ) ?

Obrigado !

Ola Rafael;

O DoCmd. executa ações conforme os passos do usuário mostrando as mensagens do sistema, enquanto o CurrentDb.Execute apenas executa diretamente sem avisos, mas pode também utilizar ae o dbFailOnError, se tiver interesse em saber os erros.

CurrentDb.Execute
>> Insert Into - Insere dados em uma tabela
>> Update      - Atualiza dados em uma tabela
>> Delete       - Deleta registros em uma tabela

DoCmd.RunSQL

Como o próprio nome diz.. roda uma consulta em SQL que pode ser atualização, deleção ou inserção
Com este comando os avisos do access ficam ativos e podes ocultá-los com a instrução

DoCmd,SetWarnings = False
   e após
DoCmd.SetWarnings = True

Ambas podem lhe dar o mesmo resultado 


Em um tópico em inglês:


DoCmd.RunSQL operates within the Access interface, while CurrentDb.Execute operates within the underlying database engine (it is a DAO command).
This has - among other things - the following consequences:

1) Since DoCmd.RunSQL works within the Access interface, it "knows" about forms and controls, so you can refer to controls in the SQL string, e.g. [Forms]![frmMyForm]![txtMyControl].
Referring to controls on forms fail with CurrentDb.Execute since DAO doesn't recognize forms and controls.

2) DoCmd.RunSQL will cause Access to ask you for confirmation ("You are about to update/append/delete 37 records ..."), unless you insert a line

DoCmd.SetWarnings False

before invoking RunSQL, and

DoCmd.SetWarnings True

afterwards. CurrentDb.Execute bypasses the Access interface and executes the SQL without asking.

3) For complex queries, CurrentDb.Execute will be faster because it doesn't have to perform all the checks the Access interface does.

4) If you run CurrentDb.Execute on the data currently loaded in a form, and then try to save a record in the form, you may get a message that another user has modified the record. The "other user" is the database engine.

The error may occur in the following situation:
- You have started editing a record.
- You run code that uses CurrentDb.Execute to modify records in the form's record source, including the current record in the form.
- At this point you don't get an error message yet, so there is no point in trying to suppress errors here.
- When you perform an action that will save the current record, such as moving to another record, you'll get a warning:

x1210.png (7.32 KiB) Viewed 797 times

- The workaround is to make sure that the current record has been saved before you run the SQL:

Code: Select all

Código:

    If Me.Dirty Then
        Me.Dirty = False
    End If
    CurrentDb.Execute SQLString, dbFailOnError

Muito Obrigado Piloto e JPaulo!


Resolvido!

O Fórum agradece o retorno.