INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)
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:

CODE -->

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

CODE -->

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:

CODE -->

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:

CODE

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

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

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 

Have fun.

---- Andy

There is a great need for a sarcasm font.

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

(OP)

Quote:

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:

CODE -->

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:

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 

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

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

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

Have fun.

---- Andy

There is a great need for a sarcasm font.

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

(OP)

Quote:

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

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

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?

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

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

(OP)

Quote:

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:

CODE -->

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:

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:

CODE

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

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

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

What I would try is this:

CODE

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 

And see if this simple Insert would work.

Have fun.

---- Andy

There is a great need for a sarcasm font.

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

(OP)

CODE -->

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:

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

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

Have fun.

---- Andy

There is a great need for a sarcasm font.

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

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

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

(OP)

Quote:

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:

CODE -->

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

and after

CODE

CurrentDb.Execute strSql, dbFailOnError 

Both ways leave me with an empty IpelaOrd_tbl table.

Quote (dhookom (Programmer)2 Oct 17 16:06 When are you opening the table for viewing? Have you tried requerying the 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

CODE

CurrentDb.Execute strSql 

and then after the line:

CODE

CurrentDb.Execute strSql, dbFailOnError 

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

Thank you both

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

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

Try this:

CODE --> vba

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

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

(OP)

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


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:

CODE

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

and this is the result:

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

(OP)
Changed the query to:

CODE

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:

CODE

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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close