Run-time error 3073 Operation must use an updateable query (1 Viewer)

bulbisi · Sep 14, 2011

Hello all,
it looks like i have an issue.
I wanted to avoid a kind of temporary table as the DB I'm creating is quite heavy and complex already.
Before:
1 Append Query to Table
1 Update Query to fill a new table from the previously created table
It worked like a charm but was way to heavy and slow to control (more than 22.000 records every week just for this table)
So I wanted to use a Select Query instead the first step
After:
1 Select Query
1 Update Query to fill a new table from the previous Select Query

Then of course I got an error 3073.

Note: The update query is running through VBA.
Note: I might try a nested Select into the update Query, but i really don't know how to use it.
Note: Access 2010

Code for Select Query:

Code:

SELECT T_CE_TOBE.DebtorNumber, Max(DateDiff("m",[EndOfMonthDate],DateAdd("m",[tblco.Ter],[tblCO.StsLIVDat]))) AS ResidualMonths
FROM T_CE_TOBE INNER JOIN tblCO ON T_CE_TOBE.DebtorNumber = tblCO.DebNum
GROUP BY T_CE_TOBE.DebtorNumber;

Code for Update Query (in VBA):

Code:

UpdateMonths = "UPDATE T_CE_TOBE LEFT JOIN Q_CE_TOBE_periods ON T_CE_TOBE.DebtorNumber = Q_CE_TOBE_periods.DebtorNumber SET T_CE_TOBE.ResidualMonths = [Q_CE_TOBE_periods].[ResidualMonths]"
DoCmd.RunSQL UpdateMonths

Any help is really appreciated

and thanks a lot in advance

PNGBill · Sep 14, 2011

You can delete the temporary table or all records in that table once it has served it's purpose.

Did you test the query first before putting it into your vba code ?

The code for your query doesn't look correct. I thought you needed Insert not Update when adding a new record to a table.

Update will change the value of a field in an exisitng record.
Insert appends a new record to a table.

bulbisi · Sep 15, 2011

well, i mis-explained, sorry
i use T_CE_TOBE to create a list for other filters (cannot update it straight to T_CE_TOBE so i have to play through another table or query)
and the result produced in the query is added in the corresponding record back to T_CE_TOBE.
So i didn't mean Append, but Update which is I do agree totally different.
My Select Query is working well, my Update Query not.
Anyway I stepped back now to the temporary table solution, and I'll see later what I should do with that
thanks anyway for your voice

Galaxiom · Sep 15, 2011

Note that you should avoid using temporary tables in either the Front End or the Back End. They cause bloating. Use a separate local database which can be separately compacted or created and thrown away.

If you want to see the discussion about this concept Google "Side End" at this site.

bulbisi · Sep 15, 2011

You are right so to remind it to me. I created another DB using a different place to store temporary tables. It worked well but due to the fact that the server here is pretty slow, I didn't want to do it again (connection to another DB is sometimes too slow). Moreover, I didn't know this project was going to be so big, managers added a lot of requests on the list.
I'll find a solution...
thanks for the idea

Galaxiom · Sep 15, 2011

Code:

Max(DateDiff("m",[EndOfMonthDate],DateAdd("m",[tblco.Ter],[tblCO.StsLIVDat])))

Storing a field called EndOfMonthDate is entirely unnecessary. Access can easily calculate the last day of any month using the "zeroth" of the following month.

bulbisi · Sep 15, 2011

this is right that would save me some size but actually, the EndOfMonthDate is not always the same month, so i do need to pick most of the info from an ODBC. Then only I fill the empty fields with the last month. Everything is done in a previous table.
Then I need the EndOfMonthDate to calculate how many months I have between a given date (StsLIVDat) and this EndOfMonthDate.
Well, I couldn't find an other way to do so.
I could eventually integrate my iif EndOfMonthDate is null then give LastDay instead, but it is a bit wasted here as I do need the table anyway for another purpose.
I'm now fighting with a historization through a recordset and that s another challenge (love it!)

PNGBill · Sep 15, 2011

I used to use Temp Tables a lot and the code did bloat.

We do use a permanent table as a temp table. One purpose only.

Records are added with the OperatorID as a field and when that operator performs the task it first deletes all records in that table for that operator dated earlier then one day ago and all records for that "customer" created by that operator at any time.

We calculate all Occurances of Late Fees (every 14 days) for a loan which may have been issued 10 years ago.
This data is used to produce a Statement as well as report the amount to charge now.

This has worked for a year or so and no Bloating has occured.

The purpose is the temp data takes a little work to create and it is needed for a few possible uses soon after. Hence, keeping it for a day.

Otherwise, a lot of better sql's have replaced temp tables.

bulbisi · Sep 16, 2011

interesting ...
Generally once my program is working smoothly, i do not touch it anymore. But in some cases when the files is taking too much space even compacted, I take a dive in it to see what I can improve.
In this special case, it takes about 300mb per compacted file, and we keep 1 copy every month...

Run-time error 3073 Operation must use an updateable query (1 Viewer)

bulbisi

Registered User.

PNGBill

Win10 Office Pro 2016

bulbisi

Registered User.

Galaxiom

Super Moderator

bulbisi

Registered User.

Galaxiom

Super Moderator

bulbisi

Registered User.

PNGBill

Win10 Office Pro 2016

bulbisi

Registered User.

Similar threads

Users who are viewing this thread