Using recordset in Append query.

[stevekos07]

Hi. I am not very familiar with using recordsets in VBA, but I think for this problem I am going to learn how. I have an append query that I want to run as part of a backup routine. I have designed an append query that uses a simple select query as the data source. I seem to have a problem with the DoCmd.OpenQuery "qryApdXYZ" method not resulting in the append query doing the job. So I think that I am going to have to specify the recordset for the append query.

Can anyone give me some guidelines or point me to a good tutorial for how to do this?

 

[bob fitz]

I think you need to "Execute" the query rather than "Open" it.

See: http://www.fmsinc.com/microsoftaccess/query/action-queries/SuppressWarningMessages.htm

 

[pbaldy]

Curious, as I have run append queries with OpenQuery. The Execute method is more efficient but can't handle form references in the query.

 

[isladogs]

Curious, as I have run append queries with OpenQuery. The Execute method is more efficient but can't handle form references in the query.

Curiouser ... and I'm sorry to contradict again....
You can certainly use OpenQuery, RunSQL or Execute to run any action query
But you can also include form references in both query & SQL.

For example:

CurrentDb.Execute "UPDATE Table2 SET Table2.Amount = " & [Forms]![Form2].[txtAmount] & "" & _
        " WHERE (((Table2.Gender)='" & [Forms]![Form2].[cboGender] & "'));"
        
   CurrentDb.Execute "INSERT INTO Table2 ( Gender, Color, Amount )" & _
        " SELECT '" & [Forms]![Form2].[cboGender] & "' AS Gender, 'Green' AS Color, " & [Forms]![Form2].[txtAmount] & " AS Amount;"

 

[pbaldy]

I was talking about a saved query with form references in it. It's possible the Eval() function could get around that problem.

 

[isladogs]

I was talking about a saved query with form references in it. It's possible the Eval() function could get around that problem.

I'm wondering if we are talking about different things here.
Form references still work using a saved query

This works if you just run the query ...whilst the form is open of course
OR it can be run via code from the form

Private Sub Command15_Click()
  '  CurrentDb.Execute "UPDATE Table2 SET Table2.Amount = " & [Forms]![Table2].[txtAmount] & "" & _
        " WHERE (((Table2.Gender)='" & [Forms]![Table2].[cboGender] & "'));"
        
 '  CurrentDb.Execute "INSERT INTO Table2 ( Gender, Color, Amount )" & _
        " SELECT '" & [Forms]![Form2].[cboGender] & "' AS Gender, 'Green' AS Color, " & [Forms]![Form2].[txtAmount] & " AS Amount;"
    
    DoCmd.OpenQuery "Query2"
End Sub

 

[Pat Hartman]

Doesn't anyone want to see the actual query? How do we even know that qryApdXYZ is an append query?

 

[pbaldy]

I'm wondering if we are talking about different things here.
Form references still work using a saved query

Colin, try

CurrentDb.Execute "Query2"

In my experience, you'll get a "Too few parameters..." error.

 

[Mark_]

I seem to have a problem with the DoCmd.OpenQuery "qryApdXYZ" method not resulting in the append query doing the job. So I think that I am going to have to specify the recordset for the append query.

Can anyone give me some guidelines or point me to a good tutorial for how to do this?

What do you expect and what do you get? Is there an error message? To many/few records?

We would need to see both the query and your data to give a better answer.

 

[Galaxiom]

Colin, try

CurrentDb.Execute "Query2"

In my experience, you'll get a "Too few parameters..." error.

Definitely. Execute is a method of the database and as such, objects belonging the Application are not within its scope.

Parameters can be appended using VBA prior to Execute and is the most efficient and effective way to run a query that requires parameters.

 

[The_Doc_Man]

I'll second the theme that <DAO database>.Execute requires that the query must be fully self-contained before execution. You build the query string and execute that. For MOST named queries, you can do an Execute - but if your string involves ANY type of string substitution, you need a separate build step vs. execute step.

Using DoCmd.RunQuery or .OpenQuery is talking to Access, which "finishes" the query before sending the result to the engine. Using .Execute BYPASSES Access and sends the string to the engine in one fell swoop. (Or, if you did it wrong, in one swell foop!)

 

[stevekos07]

Thanks for all the input guys, but in this case the solution was too simple. I misspelt the query name in the command line! I was wondering why I didn't get an error.

I have learned quite a bit though by reading your replies, so this wasn't a wasted question. Cheers!

 

[isladogs]

Colin, try

CurrentDb.Execute "Query2"

In my experience, you'll get a "Too few parameters..." error.

Yes of course that's definitely true.
That was why I didn't mention it!
DoCmd.RunSQL "Query2" won't work either

 

[pbaldy]

Then why contradict my statement:

The Execute method...can't handle form references in the query.

As the OP was clearly trying to run a saved query.

 

[isladogs]

Then why contradict my statement:
As the OP was clearly trying to run a saved query.

The link in post 2 covered all 3 methods.
I was originally referring to that link as I thought you were
The OP talked about append queries and recordsets (SQL statements)
That's why I discussed the other methods & asked if we were talking about different things.
Clearly we were at cross purposes.

 

[Galaxiom]

Curiouser ... and I'm sorry to contradict again....
You can certainly use OpenQuery, RunSQL or Execute to run any action query
But you can also include form references in both query & SQL.

For example:

CurrentDb.Execute "UPDATE Table2 SET Table2.Amount = " & [Forms]![Form2].[txtAmount] & "" & _
        " WHERE (((Table2.Gender)='" & [Forms]![Form2].[cboGender] & "'));"
        
   CurrentDb.Execute "INSERT INTO Table2 ( Gender, Color, Amount )" & _
        " SELECT '" & [Forms]![Form2].[cboGender] & "' AS Gender, 'Green' AS Color, " & [Forms]![Form2].[txtAmount] & " AS Amount;"

The only part you were correct about is that OpenQuery can run an action query with form references.

Neither of your examples includes a form reference in the SQL command string passed to Execute. The value from the form is concatenated into the command string so the form reference is never seen by Execute.

Queries with form references included definitely CANNOT be in Executed unless their values are appended as parameters, in which case they are not actually form references but parameter names that could be anything.

RunSQL can only run an action command string. It can't run a saved query whether it includes form references or not.

 

[isladogs]

The only part you were correct about is that OpenQuery can run an action query with form references.

Neither of your examples includes a form reference in the SQL command string passed to Execute. The value from the form is concatenated into the command string so the form reference is never seen by Execute.

Queries with form references included definitely CANNOT be in Executed unless their values are appended as parameters, in which case they are not actually form references but parameter names that could be anything.

RunSQL can only run an action command string. It can't run a saved query whether it includes form references or not.

If you read post #6 in conjunction with post #4, all the examples I posted were valid working methods of running a query or running SQL for which form references formed a part. The semantics on how this information is used isn't the point. Each example I gave works.

I never suggested RunSQL worked with a saved query and in fact have already stated it can't.