Append query fail with code break message

[robinatsel]

Running full Access 2016 (O365) on MS Windows 10 with linked MySQL database.
I have a simple append query that creates one record in Table A for each record in Table B. (Table B contains 189 records.)
When run directly it runs from beginning to end without problem.
When run as part of a sequence of events it stops, always after appending 50 records, with the message
... will reset the current code in break mode ...
Responding 'yes' only redisplays the message.
Responding 'no' allows the query to continue to end with records correctly generated BUT somehow prevents the Access window from closing other than with Task Manager.
There is no VBA code behind the event sequence.
I have tried deleting records in Table B on either side of the failure point, linking to a copy of the database, and also recreating Table B from scratch.
Any ideas anyone?

 

[JHB]

First backup your database, then:

• Try a "Compact & Repair".
• Import all into a new database.
• Also try a decompile. http://www.fmsinc.com/microsoftaccess/performance/decompile.asp

Do you have any timer running in the database?

 

[robinatsel]

Thanks JHB.
I have already tried your first two suggestions and do not have any timer running. I will look at the decompile option now.

 

[JHB]

..
When run as part of a sequence of events it stops, always after appending 50 records..

What events and where are they? Show them!
Only for error finding, can't you import the relevant tables to a local database link it to your database and the run the query to see if it stops after 50 records.

 

[JHB]

Just a thought, you do not have any table triggers put on your tables, do you?

 

[robinatsel]

No triggers

 

[robinatsel]

The event sequence is ...
From a continuous form for Table C - button on-click event runs macro.
Macro
Closes continuous form
Opens another form to add a new record to Table C
Form closes on exit
Opens continuous form again
On Load event of continuous form
Tests for new record loaded and if yes
Opens append query for Table B from Table A

I hope that this is clear and thanks again for your help.

 

[isladogs]

Sounds terribly convoluted and probably involves recordsets which you may be opening and not closing correctly.
Recordsets should be set to nothing after use.

Why does it have to be done one record at a time?
Why do you need to keep opening and closing forms?

Could the whole caboodle be replaced with two append queries each doing all required new records in one step?

 

[robinatsel]

To put it in context, this refers to the luncheon events management for a sports club.
The continuous form supports the update of past and future events in Table C and the button on-click opens up a form to add a new luncheon.
When a luncheon is added it is necessary to create a ticket application record in Table A for each club member listed in Table B. Table A records get update with ticket sales data in a separate function that also updates Table C with total sales data.

It was when I had everything happening in a single event that the problem started and I have ended up with the current situation in an effort to separate the different elements.
Thinking about your last comment I will try replacing the luncheon add form with an unbound form to set up tempvars and then run an append query to create the new luncheon with the collected values.
It just seems weird that the failure point is always immediately after 50 appends.

 

[isladogs]

It may be that is when it runs out of 'available memory'.
If you can streamline it as I suggested, I think it will no longer be an issue

 

[Pat Hartman]

There is no reason to open forms to add records. Records should be added using append queries that copy the records from tblA and append them to tblB. The query would use an argument that it gets from the form to control what records are selected and possibly a second argument that specifies the foreign key for the appended records.

 

[JHB]

...
It just seems weird that the failure point is always immediately after 50 appends.

It sound like something is counting your attempts, when a memory leak it would not stop each time at the same "place".

...There is no VBA code behind the event sequence.

But then you've macros, so in fact you've some code running.
Can't you post your database including the relevant tables/queries and with some sample data, (zip it).
Else post some screen dumps from all the relevant macros, (again zip it)!

 

[robinatsel]

Reply to Pat Hartman.
Thanks for your input.
The use of a form to add a record is specific to the new luncheon where data must be input by the user. I am in the process of changing this to a form with unbound fields that will be assigned to TempVars used by a new append query.
The creation of multiple luncheon ticket application records is already via an append query taking data from one table into another. It is this query that is failing after 50 appends.
Reply to JHB.
Thanks again for your suggestions.
The database is linked MySQL and contains real data so posting it is not an option.
I will try to move the failing query to a different activation point to eliminate current interactions. I know that it works as an independent function so maybe I can avoid the problem that way. I will post an update later tomorrow.

 

[Pat Hartman]

Why would you use unbound forms rather than a bound form? Access is a RAD (Rapid Application Development) tool. The whole point of using Access to create applications is to take advantage of its capabilities. When you use unbound forms, you are giving up the essence of Access and are left with only the baggage.

Access has been multi-user and had the ability to connect to ODBC datasources since its inception. In fact, what drew me to Access to begin with was it's ability to link to DB2 tables on our IBM mainframe and actually update them. I've been using Access since the mid-90's and most BE's are SQL Server, DB2, Oracle, and other RDBMS' rather than Jet or ACE. All the bad press "Access" gets is actually directed at Jet and ACE rather than Access, the RAD tool.

Occasionally when using linked ODBC tables, you might find that you need to create views or pass-through queries and sometimes even stored procedures to speed up slow processes. But if you understand client/server and define your forms correctly, you will find that bound forms work the vast majority of the time.

 

[robinatsel]

Problem solved !!!
On the continuous form for Luncheons display/update I have a button for 'Add New Luncheon'.
This has an 'On Click' event that ...
1. Opens an unbound form to collect the basic identifiers for a new luncheon
2. Runs an append query to add the new record
3. Closes and reopens the continuous form to display the new record with an 'On Load' event to run the (previously failing) append query for the Ticket Applications if required.

I know that this sounds convoluted but I still don't understand the true reason for the append query failure and can only assume that the circumstances led Access to identify that there was a count based 'break' in some non-existent VBA code behind the form. My solution has been no more than to separate the elements to ensure the completion of each before moving on to the next. Not elegant but it works!
One final comment. My original development in line with the concept described by Pat Hartman worked fine with a linked Access database. The problem only arose after migrating it to a hosted MySQL environment.

Thanks to all of you for your contributions.

 

[Pat Hartman]

Then you should have figured out what caused the problem. There are occasional differences although I haven't found any with bound forms. I have only had to adjust my code when running queries.