Solved .Filter Recordset "No Current Record" Error / UpSerting in VBA (1 Viewer)

[raziel3]

Another approach to saving this aggregate data is to just save the aggregate calc and identifier to a different table that can join to primary table.

You mean just use the 'Make Table' action query?

table can be permanent and all or some records would be deleted and replaced

Isn't that what my code is doing? Can you explain a little more please.

Can I change the title to include "UPSERTING using VBA". Is that ok with the admins?

 

[MajP]

You can do two make table queries even if the data is not updateable.
Do a make table from items. You can do that in the current database or as @moke123 in a side database. The benefit of the side database you do not have to account for bloating of the current database..
Now you do not update tblItems since you can quickly link to the temp table.

If you do it in the local database then you might not want to constantly add and delete or you will bloat the database. You would have to update the temptable. Now you added a level of complexity. The side database is nice because you save the temptable, but "blow it away" when done. No concern about bloat.

To see this done

Solved - Does It Affect My DB in Any Way If I Delete Temporary Tables after Creating and Using?

Hello, I need to create a temp table for my one subroutine to work. I have a sub on form load even which checks if temp table exists or not. If it doesn't, then it creates it. Should I delete the temp table while closing the form or let it remain for next time usage? Will creating and then...

www.access-programmers.co.uk

But bottom line do not store this calculated value in the main data table. Store it in another table.

 

[moke123]

If interested in going the Temp database route, This contains the procedures I use. Note that I use empty Template Tables in the main database to create the temp tables in the temp database.

 

[June7]

Your code is not deleting records or table so my suggestion is something different.

If forum allows you to edit thread title, go ahead.

 

[raziel3]

Just to be clear.

tblITEMS is not related to anything in my db. I did use a 'Make Table' query initially to create it from the Items query only because I was unable to do a Crosstab Query and now that it was created I decided to keep pushing data into it yearly. So query Items group by year and I fill tblITEMS with the new results. The only use of tblTEMS is to provide a source for my Crosstab Report.

Does that still mean I should keep that table external?

 

[raziel3]

@MajP I notice that you created a whole new recordset instead of using .Filter method. Any reason why?

 

[MajP]

As per MS.

In many cases, it's faster to open a new Recordset object by using an SQL statement that includes a WHERE clause.

Not a big deal here, but out of habit I avoid applying the filter to the RS. IMO the code is a little cleaner without it.

 

[MajP]

Does that still mean I should keep that table external?

You do not have to. What is nice about that is you do not have to worry about bloat caused by repeated adding and deleting. But you only have to worry about repeated adding and deleting if you are not doing what you already are doing. You are not deleting. You add once then update. However, that code is a little more complicated. In some cases it could be real complicated depending on what has to be updated.
Instead og adding the new records and updating the existing records like you are doing, it can be simpler to delete the existing table and make a brand new table with a make query. Or you could delete all the records and import all the records. No need for an update in either case. This however, will bloat your db. If doing this method you can then do a compact and repair or do the "side-database". However since you have done the work to do the update and add, you are not bloating the db. You would not gain anything by pushing this into a seperate db, unless you want to choose one of the other methods of doing this.