Strange! Getting error 2486 when function is called from a query ??? (1 Viewer)

lana

Registered User.
Local time
Today, 15:39
Joined
Feb 10, 2010
Messages
92
Hi there,
I have an annoying problem and hope someone could help me.
I created a function which deletes a query and then creates a new one with CreateQueryDef.
When I use this function from the immediate window, it works perfectly with no error. But when I call this function from a field in a query, it gives error 2486 "you can't carry out this action at present time" on this line :
DoCmd.DeleteObject acQuery, "Sales_Query_1"


How come it works when called from somewhere else? I tried calling this function from a form and it works with no error.

''''''''''''''''''''''''''''''''''''''''''''
Function Sales_Values(code)
Dim sql_text As String
Dim rst As Recordset
Dim qry As QueryDef
DoCmd.DeleteObject acQuery, "Sales_Query_1"
sql_text = "sql statement"
Set qry = CurrentDb.CreateQueryDef("Sales_Query_1", sql_text)
Set rst = CurrentDb.OpenRecordset("Sales_query_2")
sales_values = rst.Fields(2).Value
rst.Close
Exit Function

'''''''''''''''''''''''''''''''''''''''''''

Appreciate any help.
Cheers
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:09
Joined
Oct 29, 2018
Messages
21,357
Hi. I'm sorry I don't understand. Why would you want to create a query within a query? Can you please show us exactly how you were calling the function within a query? Thanks.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:09
Joined
Feb 19, 2013
Messages
16,553
perhaps your Sales_Query_1 is open and therefore cannot be deleted
 

lana

Registered User.
Local time
Today, 15:39
Joined
Feb 10, 2010
Messages
92
Thanks for the reply.

The query is not open. The function works when called from a form or ...

I need the result of Sales_Query_2 which needs Sales_Query_1. Query one has a parameter (Account Code) which changes every time, that's why I need to create it each time.

1584288345010.png
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:09
Joined
Feb 19, 2013
Messages
16,553
OK - it doesn't look like your table is normalised (all those accountX_ID's) which always makes managing queries more difficult.

Your function is not fully declared as type which I guess would be either double or currency i.e.

Function Sales_Values(code as string) as double

however I suspect what you are trying to do in your function means it can't be run in a query - unless perhaps the query only returns a single row. The reason is code takes a while to run (even if only a few milliseconds) so it is possible it is trying to execute the delete before the processing of the previous row has finished with rst. By not declaring types (such as double or string) the code reverts to the variant datatype - variants take longer to execute because the code has to look at the data to determine the type.

Only thing I can suggest is to put

DoEvents

before your delete line which forces the code to wait until all other processing has been completed.

In principle what you are trying to do should be doable in a single query. If you provide some example data and the outcome you require from that data, we can probably help you to do that.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:09
Joined
Oct 29, 2018
Messages
21,357
I agree. You might be able to use a subquery rather than a function.
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:09
Joined
Jan 23, 2006
Messages
15,361
You have shown us HOW you have done something. I'd like you to describe to us in simple, plain English WHAT it is you are doing. It seems that deleting a query and creating a new query is a little on the rare side. There may be some options if readers knew exactly WHAT had to be accomplished.
As has been said above, some sample data of the "input" and sample "output" based on that "input" would help with context.
 

Cronk

Registered User.
Local time
Today, 22:09
Joined
Jul 4, 2013
Messages
2,770
One such option would be rather than delete a query and creating a new one, replace the sql of the existing query
Code:
set qry = db.querydefs("Sales_Query_1")
qry.sql=sql_text 
set qry = nothing

The other apparent odd thing is that the recordset is opened on another query Sales_query_2, not the one created. Maybe there the two queries are joined.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:09
Joined
Feb 28, 2001
Messages
26,996
I am guessing on this but I think part of the problem is that this is a case when JET or ACE goes semi-asynchronous on you. (It is rare.) We had a thread over a year ago about whether Access is multi-threaded. The answer is generally no, but ACE, being a separate process/task, is capable of executing code in parallel. It does allow parallel operation with MSACCESS.EXE when it has things to clean up that no longer impinge on your query. I think this might be one of those cases.

You are diddling with the MSysObjects table when you do things like querydef deletion and creation. I'm not 100% on this but it is possible that you are running into an asynchronous lock left over from a prior iteration of whatever the query is doing. You say this is being called by a query. How many records are implied by that calling query? If it is more than one, then I think I am right - you are thrashing the MSys tables and stepping on object locks because of ACE being its own process. But I have to admit it's no more than an educated guess.

I'll let the other members chime in on this one because I'm not at all sure. The DoEvents solution might work but if this query that runs your code has a lot of records to consider, you might be in for a really big slowdown.
 

lana

Registered User.
Local time
Today, 15:39
Joined
Feb 10, 2010
Messages
92
Thank you all for the replies.

I tried DoEvents and declaring "Function Sales_Values(code as string) as double " but it did not work.
The problem starts at the first record. There are say 20 records in the table for now.
I did this method several times with no problem.

I explained in the attached file what I need to do. If you have time please have a look.
Many many thanks.
 

Attachments

  • Accounts_Query.pdf
    302 KB · Views: 164

lana

Registered User.
Local time
Today, 15:39
Joined
Feb 10, 2010
Messages
92
Hi guys,

I tried what CRONK suggested and voila!!! it works like a charm.

Thank you so much. GOD bless.
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:09
Joined
Jan 23, 2006
Messages
15,361
Glad you have things resolved.
 

Users who are viewing this thread

Top Bottom