Recordset Not Updateable - From Query to Table

[Heatshiver]

I have a few complex queries that consist of several other queries, to produce certain totals. These do work and produce a number.

The totals these queries produce are to be committed to a table. Because of the enormity of these queries, I cannot put all the totaling queries into the Record Source of a form.

Instead I want to have forms pop-up that each have one total query for their Record Source. These forms are to remain invisible. I then have a main form appear that has table fields for all the totals produced by the total queries. I then have the table fields on the main form equal the total query fields produced on the invisible forms.

I have done this, but I run into two problems:

1) I cannot change the date on the total query forms. I want the main form to change the date of the total query forms so that it can update new totals as needed.

2) I cannot get the values from the total query forms to carry over to the main form.

These two problems give these two errors: 3348 (Cannot add record(s); joing key of table not in recordset.) & 3426 (Recordset not updateable.)

I've tried everything from changing the the dynaset to inconsistent updates, to attempting to add FKs into the query, to trying to load the fields via VBA in the main form and the subform in the main form.

Any help would be much appreciated.

 

[RainLover]

One or more of your queries is causing the problem.

You need to start at the first one and test it, then the next until you find the problem.

With such complexity I would think that your table designs are incorrect.

That is Relationships and Normalisation.

Best thing for you to do is to post a pic of your relationships so we can have a look.

 

[gemma-the-husky]

a total query is always non-updateable, though

ie - given that a total query gives you calculated totals of underlying records, it cannot in principle be updateable.

note that a union query is also always non updateable

some queries are non-updateable because of the nature of the joins used, etc - and these can often be redesigned to become updateable.

 

[RainLover]

Excessive coding is the sign of a poorly designed Database.

Can you do anything about the design or is someone else responsible.

 

[Heatshiver]

Thanks Pat! I had thought about separate tables before, but not for this situation. It makes a lot of sense, so I will give it a shot.

 

[Heatshiver]

I'd prefer not to add the calculated values either, but there is a chance the DB will need to be converted for use with MSSQL down the road, so I figure to be safe, this should be implemented for now.

What I decided to go ahead and do was turn my query into an append query to attach the values to a specific table. This leaves me with two more problems:

1) How do I specify which day the append query should use?

*2) The append query just hangs when running. It works without issue as a select query, but seems to just freeze Access when ran as an append query.

As for the day I assume I could add the date field from the main table and put in the day inputted from the form into the query criteria? Not sure about the freezing aspect though...

Thanks.

 

[RainLover]

Do you already have a Table with all the correct Data Types.

If not you may need to use a Make Table Query instead.

If the table is set up then drop one field from you query then test. Keep repeating until the query works. Naturally when it works you would have found the problem field.

 

[Heatshiver]

@RainLover - I tried testing the append query, but continue to hangs regardless.

Also tried copying the DB, and then creating a new DB with the data from the copy, as I saw that this sometimes worked for others with the same problem.

I even tried a unchecking spell-check options, but to no avail.

The query will almost finish if I run it on my desktop (8-core, 16GB RAM, SSD), but then states it could not find the database...

I will try a Make Table query, but in the meantime, any suggestions on different approaches?

 

[RainLover]

In my previous post I suggested that you should delete one field at a time from the query until it works.

Have you tried that idea.

 

[RainLover]

Are you using a Query or are you using a SQL Statement as Code in a Module.

 

[Heatshiver]

The query is made up of several other queries. The append query itself only contains two fields. I tried each one on their own, but it will just get to a point where it states the DB could not be found...

I am using the actual Query to do this. Not quite sure on how I would go about this if using VBA.

Thank you for the help.

 

[RainLover]

The query is made up of several other queries. The append query itself only contains two fields. I tried each one on their own, but it will just get to a point where it states the DB could not be found....

It would appear that the problem lies with one of the Sub Queries.

Suggest you go to the first and Test it. Keep going until you locate the problem.

Once you know the problem or at least the problem query you can post it here for further help.

Each query has a Data sheet View, a Design View which you are familiar with both. There is also a SQL view. This is what you would post here for further help. This SQL is what can be used within Code, but no need to worry about that until you become more competient.

 

[Heatshiver]

I had problems with them before being too complex (I was trying to fit them into one query).

I then had to go through them and fix any problems.

Should I go through them again even if they work while as select queries?

 

[RainLover]

How many queries are we talking about here.

 

[Heatshiver]

It consists of 9 queries.

 

[RainLover]

9 Queries appears to be excessive.

I did ask if you had any control over the Back End design.

Please advise what the situation is.

Finally what is your Back End. Is it Access or some other Database.

 

[Heatshiver]

I do have control over the back end design. The main problem is these are not my calculations but the companies. I have had a lot of problems getting them to work just because of their complexity.

I was able to get a workaround going today in which I use the select queries and populate one form that carries it's values to another, which is then committed to the table. I need to try the other queries to see if this works for the more complex ones though...

My back end is Access.

 

[RainLover]

It appears as though not enough thought has gone into the Table designs.

If you can't change them then you will just have tto live with it.

Temp tables as Pat suggested may be your only choice.

Unfortunately this Database is going to cause grief now and in the future.

 

[Heatshiver]

The actual problem is not the tables, but the queries. In most situations you would not store calculated values, but this is one that would be safest to do so.

The problem lies within the calculations, if they were simpler, then there would be more avenues in which to rely on and use. However, most options have been exhausted.

I am making headway by using separate forms to display the value and commit them to the table, but I still need to work out the kinks.

 

[RainLover]

Just a suggestion.

Bring the data into the front end without applying any calculation. Then run your query with the calculation. I believe this will be faster.

You have said that the query just stops. Have you timed this. If you have is the cut off exactly 30 seconds. If so I have another suggestion.