Re: Which is best - recordsets/VBA V SQL statements for updating data


Lyle Fairfield wrote: 
I think I'll withdraw and leave the field to you. Without a deep
understanding of what actually happens with the POS / Billing stuff I
shouldn't suggest anything and I won't. If you've analyzed the problem,
etc, etc and concluded a temp table is called for, then by all means go
for it.

Well, I couldn't think of any other way to do it. That isn't the same as 'there is no other way to do it'. I'm always looking for better ways of doing things.

Do you create your temp table within a Transaction and RollBack the
Transaction when you're done with it? That way the Temporary Table is
never saved and your DB is unmodified.

Not in this case, but I have used that technique elsewhere. I don't use it often either, but there are times where it comes in handy.

But a Report or Form cannot use
such a Temp Table; TTBOMK Access Objects can use only Tables etc that
appear in the DB window.

That hits the nail on the head for my POS form. The temp table is in a separate MDB and is permenantly linked. The table always exists, so it is in the DB Window. It has data added to it in the POS screen. Once the order has been finished and payment has been received, the data is Inserted from the temp table to the Billing table via an INSERT TO query and then deleted from the temp table. So, the temp table is really a Batch proccess collection. The MDB with the temp table in it is compacted when the app is open, each user has their own copy of the temp table MDB in their TEMP folder.

I did in a previous post in this thread state that my definition of temp table in this instance was the the data was temporary, ie the table was a place to temporarily hold data. This isn't the same as a table created, used and then deleted all in code. If I do this I always enclose it in a Transaction as per your above suggestion.

Since this discussion is getting OT I'll bring this back around to the original topic of this thread; I use SQL to move the data from the temp tabel to the Billing table, I do not walk the recordset and do separate inserts for each record.

--
Bri

.

Relevant Pages

  • Re: Which is best - recordsets/VBA V SQL statements for updating data
    ... moved to the Billing table. ... That is my opinion, but I am perfectly happy to be shown ... I was trying to point out that as a last resort a temp table could be ... Your response indicates that there is no ...
    (comp.databases.ms-access)
  • Re: access spell checker tool for runtime apps
    ... As an aside, I use temp tables in that way many times, and I do have a bloat problem with my main application, Instant Quote Pro. ... One thing I tried to do, but I'm not even sure this helps, is to offload temp tables such as the one you're referring to, to a local workspace.accdb file. ... using a temporary tables MDB to avoid significant bloating of the FE MDB. ... Tony Toews, Microsoft Access MVP ...
    (comp.databases.ms-access)
  • Re: CListCtrl, hide and edit
    ... bool CDialogWithList::CompareAndSwapString1(int pos, bool bAscending) ... CMyObjectInfo *temp; ... bool bNotDone = TRUE; ... So I don't use the sort mechanism in the list control at all. ...
    (microsoft.public.vc.mfc)
  • Re: temp db
    ... If the database is not in use, you can create a copy just by copying the MDB ... The purpose is to create a temp db for all the ... ' This subroutine illustrates how to use a temporary MDB in your app. ...
    (microsoft.public.access.formscoding)
  • Re: Sorting a CObArray?
    ... Derive a class from CObArray ... BOOL CSortObArray_DerivedClass::CompareAndSwap(int pos) ... SetAt(posNext, temp); ... CString in the CStringArrays. ...
    (microsoft.public.vc.mfc)