Using .Execute to run SQL and nothing happens

[bakershawnm]

Hi,

I have searched and found this thread:
thread705-1374927
however it didn't really answer my question.

I have the following Query named Qty_Yield_Select_Record:

INSERT INTO intertbl2b ( Assy, WO, IDate, PartTotal, MarkTotal, LeadTotal, SolderTotal, BrdCount, PartFail, MarkFail, LeadFail, SolderFail, Side, FC, BrdFail, RecType )
SELECT SPC_Data.Assy, SPC_Data.WO, SPC_Data.IDate, SPC_Data.PartTotal, SPC_Data.MarkTotal, SPC_Data.LeadTotal, SPC_Data.SolderTotal, SPC_Data.BrdCount, SPC_Data.PartFail, SPC_Data.MarkFail, SPC_Data.LeadFail, SPC_Data.SolderFail, SPC_Data.Side, SPC_Data.FC, SPC_Data.BrdFail, SPC_Data.RecType
FROM SPC_Data INNER JOIN intertbl2a ON (SPC_Data.Side = intertbl2a.Side) AND (SPC_Data.SN = intertbl2a.SN) AND (SPC_Data.IDate = intertbl2a.LastOfIDate) AND (SPC_Data.WO = intertbl2a.WO) AND (SPC_Data.Assy = intertbl2a.Assy)
WHERE (((SPC_Data.RecType) Like "*R"))
ORDER BY SPC_Data.Assy;

and the following code:

Dim lclcnxn As New ADODB.Connection

lclcnxn.ConnectionString = "Provider='Microsoft.Jet.OLEDB.4.0';Data Source='C:\offline\KPI Reports.mdb';Persist Security Info='False'"
lclcnxn.Open

lclcnxn.Execute "DELETE * FROM intertbl2a"
lclcnxn.Execute "DELETE * FROM intertbl2b"
lclcnxn.Execute "DELETE * FROM CustRptAOI;"

lclcnxn.Execute "[QTY_Last IDATE by SN]"

lclcnxn.Execute "QTY_Yield_Select_Record"

lclcnxn.Execute "QTY_Yield_Oppo_Level"
.

The first .Execute is an append query that fills the table that the second .Execute uses. It works correctly.

The second .Execute produces nothing. However if I run the query manually it works correctly. Why does it not work in my code?

Thanks

 

[HarleyQuinn]

Is the second query a select query?

If so you'll need to return the results to something, a recordset for example.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[bakershawnm]

The second query is the one listed above the code.

I figured out where the problems was. When executing it using the .Execute the Like statement prevented it from collecting records. So I took a look at the data and changed the Like "*R" to ="IR".

Still don't know why it would work when executed manually and not using the .Execute though

 

[PHV]

In ADO the wildcar is %, not *:
WHERE SPC_Data.RecType Like '%R'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bakershawnm]

Thanks very much PH. That will help a lot.