Query works when run manually but not when using CurrentDb.Execute

[irethedo]

I have a query (IpelaOrd_qry) that I am using to load data into a table (IpelaOrd_tbl)
but I am doing something wrong as it is not working as I would hope...

At first I set this up as a select query as shown in the code below:

SELECT Ipela_tbl.ID, Ipela_tbl.PartNum, Ipela_tbl.Desc, Ipela_tbl.Type, Count_tbl.Count
FROM Count_tbl, NewData_tbl INNER JOIN Ipela_tbl ON NewData_tbl.[Ordered Item] = Ipela_tbl.PartNum
WHERE (((Count_tbl.Count)<=[NewData_tbl].[Ordered Quantity]));

and I was using the following to execute this query

strSq1 = "IpelaOrd_qry"
CurrentDb.Execute strSql

but because this is a select query I was getting a run time error 3065

So then I changed it so that I would delete the contents of the table first and changed this to an
update query with the following:

INSERT INTO IpelaOrd_tbl ( PartNum, Type, [Count] )
SELECT Ipela_tbl.PartNum, Ipela_tbl.Type, Count_tbl.Count
FROM Count_tbl, NewData_tbl INNER JOIN Ipela_tbl ON NewData_tbl.[Ordered Item] = Ipela_tbl.PartNum
WHERE (((Count_tbl.Count)<=[NewData_tbl].[Ordered Quantity]));

and I use the following code to first delete the contents of the table and then run the update query:

 strSql = "Delete * From IpelaOrd_tbl" CurrentDb.Execute strSql strSq1 = "IpelaOrd_qry" 
CurrentDb.Execute strSql

Now the problem is that if I run the query manually it will load the data into the table as I would expect
but when I use the following code it doesn't load any data into the table.

Why the difference and how do I correct this?

Thanks

 

[Andrzejek]

So what you are trying to do is to run the code like this: (correct?)

strSql = "Delete * From IpelaOrd_tbl" 
CurrentDb.Execute strSql 

strSq1 = "INSERT INTO IpelaOrd_tbl ( PartNum, Type, [Count] ) " & vbNewLine _
  & " SELECT Ipela_tbl.PartNum, Ipela_tbl.Type, Count_tbl.Count " & vbNewLine _
  & " FROM Count_tbl, NewData_tbl INNER JOIN Ipela_tbl ON NewData_tbl.[Ordered Item] = Ipela_tbl.PartNum " & vbNewLine _
  & " WHERE (((Count_tbl.Count)<=[NewData_tbl].[Ordered Quantity]))"

CurrentDb.Execute strSql

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[irethedo]

So what you are trying to do is to run the code like this: (correct?)

CODE
strSql = "Delete * From IpelaOrd_tbl"
CurrentDb.Execute strSql

strSq1 = "INSERT INTO IpelaOrd_tbl ( PartNum, Type, [Count] ) " & vbNewLine _
& " SELECT Ipela_tbl.PartNum, Ipela_tbl.Type, Count_tbl.Count " & vbNewLine _
& " FROM Count_tbl, NewData_tbl INNER JOIN Ipela_tbl ON NewData_tbl.[Ordered Item] = Ipela_tbl.PartNum " & vbNewLine _
& " WHERE (((Count_tbl.Count)<=[NewData_tbl].[Ordered Quantity]))"

CurrentDb.Execute strSql

Thanks for the reply Andy

When I run the query manually, of the code that you posted:

INSERT INTO IpelaOrd_tbl ( PartNum, Type, [Count] )
SELECT Ipela_tbl.PartNum, Ipela_tbl.Type, Count_tbl.Count
FROM Count_tbl, NewData_tbl INNER JOIN Ipela_tbl ON NewData_tbl.[Ordered Item] = Ipela_tbl.PartNum
WHERE (((Count_tbl.Count)<=[NewData_tbl].[Ordered Quantity]));

the results look like this:

but when I run the code that you posted, (or the code that I posted in my initial post) like this:

strSql = "Delete * From IpelaOrd_tbl" 
CurrentDb.Execute strSql 

strSq1 = "INSERT INTO IpelaOrd_tbl ( PartNum, Type, [Count] ) " & vbNewLine _
  & " SELECT Ipela_tbl.PartNum, Ipela_tbl.Type, Count_tbl.Count " & vbNewLine _
  & " FROM Count_tbl, NewData_tbl INNER JOIN Ipela_tbl ON NewData_tbl.[Ordered Item] = Ipela_tbl.PartNum " & vbNewLine _
  & " WHERE (((Count_tbl.Count)<=[NewData_tbl].[Ordered Quantity]))"

CurrentDb.Execute strSql

Then the results look like this:

Not sure why it works when I run it manually but not when I run it with the vba code...

very strange

 

[Andrzejek]

Are you getting any errors?
Do you have any [tt]On Error Resume Next[/tt] statements in your code?
Or any error handlers?


Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[irethedo]

Are you getting any errors?
Do you have any On Error Resume Next statements in your code?
Or any error handlers?
Have fun.

There is no On Error Resume next Statements until much later in the code in a separate area of the code

I did have DoCmd.SetWarnings False earlier in the code but I just placed a
DoCmd.SetWarnings True just before this code and tried it again but no change in result...
(the table does not get loaded with data like it does when the query is performed manually

thanks

 

[dhookom]

I always execute with code like:

CurrentDb.Execute strSql, dbFailOnError

Like "Count", "Type" is also a reserved word and shouldn't be used as a field name or at least enclosed in []s. See this link to Allen Browne's page.

Is [Ordered Quantity] required in IpelaOrd_tbl?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[irethedo]

dhookom (Programmer)1 Oct 17 14:44
I always execute with code like:

CODE --> VBA
CurrentDb.Execute strSql, dbFailOnError

Like "Count", "Type" is also a reserved word and shouldn't be used as a field name or at least enclosed in []s. See this link to Allen Browne's page.

Is [Ordered Quantity] required in IpelaOrd_tbl?

Thanks Duane-

[Ordered Quantity] is not required in IpelaOrd_tbl, I use it to set up a count in my table to
include one record for each one that is ordered.

I changed "Type" to "SysType" and "Count" to "Counts" so now my code looks like this:

 strSql = "Delete * From IpelaOrd_tbl"
 CurrentDb.Execute strSql
 
strSq1 = "INSERT INTO IpelaOrd_tbl ( PartNum, SysType, Counts ) " & vbNewLine _
  & " SELECT Ipela_tbl.PartNum, Ipela_tbl.SysType, Count_tbl.Counts " & vbNewLine _
  & " FROM Count_tbl, NewData_tbl INNER JOIN Ipela_tbl ON NewData_tbl.[Ordered Item] = Ipela_tbl.PartNum " & vbNewLine _
  & " WHERE (((Count_tbl.Counts)<=[NewData_tbl].[Ordered Quantity]))"

CurrentDb.Execute strSql, dbFailOnError

and here are the results of this for table IpelaOrd_tbl:

Also, If I remove the following two lines of code:

 strSql = "Delete * From IpelaOrd_tbl"
 CurrentDb.Execute strSql

then I receive a Run-time error '3065' Cannot execute a select query

Incidently, If I create the same code in the following query:

INSERT INTO IpelaOrd_tbl ( PartNum, SysType, Counts )
SELECT Ipela_tbl.PartNum, Ipela_tbl.SysType, Count_tbl.Counts
FROM Count_tbl, NewData_tbl INNER JOIN Ipela_tbl ON NewData_tbl.[Ordered Item] = Ipela_tbl.PartNum
WHERE (((Count_tbl.Counts)<=[NewData_tbl].[Ordered Quantity]));

and run it manually, it gives me the results that I want:

The million dollar question is, "Why does this query work manually but now with the same vba script?"

Thanks again

 

[Andrzejek]

Run-time error '3065' Cannot execute a select query" - hmmm, interesting.

What I would try is this:

strSql = "Delete * From IpelaOrd_tbl"
 CurrentDb.Execute strSql

strSq1 = "INSERT INTO IpelaOrd_tbl ( PartNum, SysType, Counts ) " & vbNewLine _
  & " [blue]VALUES ('ABC100', 'My Sys Type', 99) [/blue]"

CurrentDb.Execute strSql, dbFailOnError

And see if this simple Insert would work.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[irethedo]

 strSql = "Delete * From IpelaOrd_tbl"
 CurrentDb.Execute strSql

strSq1 = "INSERT INTO IpelaOrd_tbl ( PartNum, SysType, Counts ) " & vbNewLine _
  & " VALUES ('ABC100', 'My Sys Type', 99) "

CurrentDb.Execute strSql, dbFailOnError

Produces the following results:

 

[Andrzejek]

Did you have anything in this table before you run the code?

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

When are you opening the table for viewing? Have you tried requerying the table?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[irethedo]

Andrzejek (Programmer)2 Oct 17 16:05
Did you have anything in this table before you run the code?

Hi Andy-
I have tried both ways (with contents in the table and no contents in the table) before these two
lines of code:

Checking after the following code:

strSql = "Delete * From IpelaOrd_tbl"
 CurrentDb.Execute strSql

and after

CurrentDb.Execute strSql, dbFailOnError

Both ways leave me with an empty IpelaOrd_tbl table.


Hi Duane-
I have placed breaks in my code and have verified both ways (with contents in the table and no contents in the table before)
both before and after the

CurrentDb.Execute strSql

and then after the line:

 CurrentDb.Execute strSql, dbFailOnError

Not sure how to requery the table... Please advise

Thank you both

 

[dhookom]

irethedo,
You have two Execute statements so you need to confirm that you are opening the table after the second Execute.

Try this:

strSql = "Delete * From IpelaOrd_tbl"
 CurrentDb.Execute strSql

strSq1 = "INSERT INTO IpelaOrd_tbl ( PartNum, SysType, Counts ) " & vbNewLine _
  & " VALUES ('ABC100', 'My Sys Type', 99) "

CurrentDb.Execute strSql, dbFailOnError 
MsgBox "Number of records in IpelaOrd_tbl: " & DCount("*","IpelaOrd_tbl")

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[irethedo]

MsgBox message = Number of records in IpelaOrd_tbl: 0

I just created the following query which is the same as in the VBA code(without the delete)
but I startetd with an empty table:

 INSERT INTO IpelaOrd_tbl ( PartNum, SysType, Counts )
SELECT 'ABC100' AS Expr1, 'My Sys Type' AS Expr2, 99 AS Expr3;

and this is the result:

 

[irethedo]

Changed the query to:

 SELECT Ipela_tbl.PartNum, Ipela_tbl.SysType, Count_tbl.Counts INTO IpelaOrd_tbl
FROM Count_tbl, NewData_tbl INNER JOIN Ipela_tbl ON NewData_tbl.[Ordered Item] = Ipela_tbl.PartNum
WHERE (((Count_tbl.Counts)<=[NewData_tbl].[Ordered Quantity]));

And the code to:

 strSq1 = "Ipela_Ord_qry"
DoCmd.OpenQuery strSq1, acViewNormal, acReadOnly
MsgBox "Number of records in IpelaOrd_tbl: " & DCount("*", "IpelaOrd_tbl")

and the results are what I am looking for:

Thanks for your help with this guys!