Execute Query From Code

B

Big V

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
 
A

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
 
B

Big V

Thanks Arvin, perfect.

Big V

Arvin Meyer said:
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
Click to expand...
 
P

(Pete Cresswell)

Per "Big V said:
Can someone give me an example of how to call and execute a query in code
please ?
Click to expand...

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
 
Top