Using CurrentDb.Execute with Parameters (1 Viewer)

whdyck

Registered User.
Local time
Today, 09:04
Joined
Aug 8, 2011
Messages
169
I'm using MS Access 2003.

I have not been able to find any examples of using the CurrentDb.Execute command with parameters. Specifically, I have a predefined query that copies a record from one table to another, but it requires an ID parameter to know which record to copy. I'd like to run this query using CurrentDb.Execute.

Does anyone have some sample code to show how this can be done with parameters? Or is this even possible?

Thanks.

Wayne
 

Isskint

Slowly Developing
Local time
Today, 14:04
Joined
Apr 25, 2012
Messages
1,302
As far as i know it follows SQL structure. So you could build an SQL string and use that. Not sure that helps with your particular issue though.
 

pr2-eugin

Super Moderator
Local time
Today, 14:04
Joined
Nov 30, 2011
Messages
8,494
Try Input Box?? Before executing the Query call the Input Box to obtain the value then use that to Execute.. Something like..
Code:
Dim strSQL As String
strSQL = "SELECT something FROM somewhere WHERE id = " & InputBox("Enter ID:")
CurrentDB.Execute strSQL
 

whdyck

Registered User.
Local time
Today, 09:04
Joined
Aug 8, 2011
Messages
169
As far as i know it follows SQL structure. So you could build an SQL string and use that. Not sure that helps with your particular issue though.

Yes, I had considered that, but my SQL statement is quite large, so I was hoping I could use the saved query rather than creating a huge inline SQL statement.
 

pr2-eugin

Super Moderator
Local time
Today, 14:04
Joined
Nov 30, 2011
Messages
8,494
Why do you need to use CurrentDB.Execute if you have a stored Query?? You could use DoCmd.OpenQuery ??!!
 

whdyck

Registered User.
Local time
Today, 09:04
Joined
Aug 8, 2011
Messages
169
Why do you need to use CurrentDB.Execute if you have a stored Query?? You could use DoCmd.OpenQuery ??!!

I don't want to actually display the query window, just do the insert. My understanding of DoCmd.OpenQuery is that it will display it.

Wayne
 

Isskint

Slowly Developing
Local time
Today, 14:04
Joined
Apr 25, 2012
Messages
1,302
It will only display the query if it is a normal select query. If it is an action query - make query, update query, delete query - then there is nothing to display. Just remember to turn off warning message before you run it and turn back on after;

DoCmd.SetWarnings False
DoCmd.OpenQuery qryName
DoCmd.SetWarnings True
 

spikepl

Eledittingent Beliped
Local time
Today, 15:04
Joined
Nov 3, 2010
Messages
6,144
When you have a stored query, you can

  1. get hold of the SQL in the query and modify it, using the QueryDef object, and a sub like eg. ReplaceWhereClause (google it). This is useful if you have a complex WHERE clause to be constructed in code.
  2. With QueryDef object use .Parameters. Google QueryDef object, also look it up in Access help.
 

MarkK

bit cruncher
Local time
Today, 07:04
Joined
Mar 17, 2004
Messages
8,178
Here's code you might find useful...
Code:
With CurrentDb.CreateQueryDef("", _
  "INSERT INTO tTable1 " & _
    "( Field1, Field2, Field3 ) " & _
  "SELECT Field1, Field2, Field3 " & _
  "FROM tTable2 " & _
  "WHERE Field4 = prm0")
  .Parameters("prm0") = Me.SomeCriteria
  .Execute
  .Close
End With
This creates a temp QueryDef object. Any terms in the SQL text that the parser can't identify as fields or keywords are assumed to be parameters. Then you can assign values to the parameters, DAO handles all the datatypes and delimiters, and you can run the Execute method of the QueryDef.
 

Users who are viewing this thread

Top Bottom