Execute Query From Code

[Guest]

Probably simple but I can't work out how to do it !

Can someone give me an example of how to call and execute a query in code
please ?

Thanks

Big V

 

[Arvin Meyer]

Several ways:

Dim strSQL As String
strSQL = "INSERT INTO Table2 ( SomeData, MoreData ) ..."
CurrentDb.Execute strSQL

or:

DoCmd.SetWarnings False
DoCmd.OpenQuery "qrySomeQuery"
DoCmd.SetWarnings True

or:

DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO Table2 ( SomeData, MoreData ) ..."
DoCmd.SetWarnings True

As you can see you don't need to turn off/on warning messages with the first
method.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Guest]

Thanks Arvin, perfect.

Big V

Several ways:

Dim strSQL As String
strSQL = "INSERT INTO Table2 ( SomeData, MoreData ) ..."
CurrentDb.Execute strSQL

or:

DoCmd.SetWarnings False
DoCmd.OpenQuery "qrySomeQuery"
DoCmd.SetWarnings True

or:

DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO Table2 ( SomeData, MoreData ) ..."
DoCmd.SetWarnings True

As you can see you don't need to turn off/on warning messages with the first
method.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[(Pete Cresswell)]

Can someone give me an example of how to call and execute a query in code
please ?

If you mean an existing query,
-----------------------------------------
Dim myQuery As DAO.QueryDef

Set myQuery = CurrentDB().QueryDefs("qryWhatEver")
With myQuery
.Parameters("theWhatever") = Whatever
.Execute dbFailOnError
End With

Set myQuery = Nothing
-----------------------------------------

OR

-----------------------------------------
Dim myRS As DAO.RecordSet
Dim myQuery As DAO.QueryDef

Set myQuery = CurrentDB().QueryDefs("qryWhatEver")
With myQuery
.Parameters("theWhatever") = Whatever
Set myRS = .OpenRecordSet, dbOpenDynaset
End With

With myRS
Do Until .EOF = True
'...
.MoveNext
Loop
End With

Set myQuery = Nothing
myRS.Close
Set myRS = Nothing