Need help with VBA Execute Statement

[verdes]

I am using Access 2016.

I used the Query Wizard to create an append query that works correctly. I saved the query with the name pendingAdd_qry.

In an Event Procedure on one of my forms, I want to run that query, pendingAdd_qry.

My code gets an error on the Execute statement and I just can't figure out how to fix it. Here is the code I have in the event:

Dim rowsadded As Long

Set dbs = CurrentDb
CurrentDb.Execute "pendingAdd_qry", dbFailOnError
Set db = Nothing
rowsadded = dbs.RecordsAffected
MsgBox rowsadded

I tried to copy the SQL from the query to a string variable and I get an error on that too. tI doesn't want to pick up the "Select" portion of the append query.

Here is the Sql for the query:
INSERT INTO pending_tbl ( pendingDistrict, pendingDio, pendingBlanksneeded, pendingQuad )
SELECT district_tbl.districtID, district_tbl.districtDio, 4 AS Expr1, [currentyear] AS Expr2
FROM district_tbl
ORDER BY district_tbl.districtName;

currentyear is a global variable.

Any help you can give me is appreciated.
Thanks

 

[Ranman256]

You don't need all that vb code to run a query. It's 1 command:

Docmd.openquery "qaMyApdQry"

 

[MarkK]

I don't see how the SQL parser will understand that "[currentyear] As Expr2" refers to a global variable in VBA. I think you need to run a function in a standard module, something like...

public function GetCurrentYear as Long
   GetCurrentYear = currentyear [COLOR="Green"]'this is your global variable, re-exposed by the function call[/COLOR]
end function

Then you would do...

SELECT districtID, districtDio, 4 AS Expr1, GetCurrentYear() AS Expr2
FROM district_tbl
ORDER BY districtName;

...because I think the SQL parser will know how to run that function, but I don't think it can read global variables in a VBA standard module.

 

[MarkK]

Also, this code has problems and should be re-written...

Set [COLOR="DarkRed"]dbs[/COLOR] = [COLOR="Olive"]CurrentDb[/COLOR]
[COLOR="Blue"]CurrentDb[/COLOR].Execute "pendingAdd_qry", dbFailOnError
Set [COLOR="Indigo"]db[/COLOR] = Nothing
rowsadded = [COLOR="DarkRed"]dbs[/COLOR].RecordsAffected
MsgBox rowsadded

Try...

With CurrentDb
   .Execute "YourQuery", dbFailOnError
   MsgBox .RecordsAffected
End With

 

[The_Doc_Man]

If you use the "Set dbs = CurrentDB" syntax, then variable DBS must be declared explicitly as being of type DAO.Database.

MarkK - .Execute doesn't work the same for .ADO vs. .DAO and my question is to know which order the libraries are declared wherein data type "database" is defined. Because as far as I recall, when the declaration is ambiguous, first match wins. If ADO is first, the .Execute won't behave.

On the other hand, MarkK is absolutely right about the query content. Verdes, the rule for using the DAO Database .Execute function (which IS legal and works like a champ) is that it is sent to the DBEngine (Ace, for Ac2016) as a string and THEN evaluated. But the controls as indicated by bracketed names are not IN the context of Ace. They are in the context of Access.

So either...

1. Build a public function because Ace SQL can see those and execute them

2. Dynamically build the string and substitute the control's value into it... THEN use the .Execute method on the result.

3. Use DoCmd.RunSQL on the query because that delays sending the SQL to the Ace engine until all elements in the string have been parsed in Access context.

The difference between DoCmd.RunSQL and the DAODB.Execute is minuscule for small tables and simple SQL. Where .Execute shines is (a) for really large tables, it is faster by a small amount, and (b) if the query encounters an error, .Execute rolls back the query so no harm is done, and (c) It is easy to get back the number of records affected by the operation including "none" and (d) it integrates nicely with trap handler code since an error in the SQL syntax or execution will signal an appropriate trap.

 

[verdes]

Thank you everyone!
I needed that info on the global variable from MarkK and I did make a macro and called it from the vba that The Doc Man suggested.
Everything is working fine now. Thank you, so much!