Alternative to temporary tables 1

[MrMerlotti]

I need to extract data from a number of tables - for example Code, Desc, DefaultValue, Current period + 1. The data is then displayed in a datasheet form - the user can change (some) of the values and then append the records another table, say TblActCosts.

I could select the records to a temporary table, bind the temporary table to the form and then append the records to TblActCosts after the user has made any adjustments and then clear the temporary table. BUT...I don't like using Temporary Tables, they bloat the application etc. If I use a recordset - changes are either reflected in the underlying tables or the recordset is not updatable. I don't know how (or if it's possible) to link a datasheet form to a multi-dimensionsal array.

Any help on the approach would be much appreciated, or if this has been covered before - does anybody knows where?

 

[SeeThru]

The only way I could think of would be to capture data to an array, and write the values in to a form.

Then do the reverse to update.

You will probably find you need to limit the number of records returned for update - and show that many 'rows' on the form, hiding ones not used.

Not a very pretty solution, but would work in the rows to update never exceeds 10 or so - more than that would ger unmanageable.

Access has never done temporary tables well - time to look at SQL!

SeeThru
Synergy Connections Ltd - Telemarketing Services

http://www.synergyconnections.co.uk

 

[Ed2020]

Could you use a recordset and wrap the updates in a transaction, so they are only saved once all changes have been made and confirmed?

I have also read about using disconnected recordsets, although I've never had cause to actually use them. It may be worth Googling for information on them...

Ed Metcalfe.

Please do not feed the trolls.....

 

[Ed2020]

Access has never done temporary tables well

I'd take that a step further and say Access has never done temporary tables *at all*!

Ed Metcalfe.

Please do not feed the trolls.....

 

[PHV]

Have a look here:

http://msdn.microsoft.com/archive/en-us/dnaraccess/html/msdn_harness.asp

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MrMerlotti]

If Access has never done temporary tables - this can't be a first time data requirement?! How are people doing this?

I reckon a disconnected recordset is the answer. Create the recordset, append the required fields , bind the recordset to a form, allow the user to make the changes and then insert the lot into the required table.

Problem is - I don't know this will work.....has anybody done this before?

 

[MrMerlotti]

Sorry PHV - posted this before I saw your response - had a quick look and it seems ideal...

Will let you know.

Thanks

 

[SeeThru]

To be honest, I normally allow editing one record at a time (so easy to pull into an unbound form), or have used a table that I fill with data and then run a SQL delete to clear it.

I know this causes bloat, but there are a couple of modules where it seemed the only wat to do it....

I'm going to watch this thread!

SeeThru
Synergy Connections Ltd - Telemarketing Services

http://www.synergyconnections.co.uk

 

[gwinn7]

Other ideas...

Just create a temporary database and store your temp tables there. Link to them and delete the database when your app closes.

Otherwise, you can look at DBMS systems like PostgreSQL, which don't cost anything in licensing, easy to use, and has a lot of the power of Oracle. We use Access as a front-end to PostgreSQL here and we save a lot of time, money, and effort.

Or, is it really that critical that you do not bloat the app size? I mean, so what, if you are not constrained by disk size?

Gary
gwinn7

 

[JoeAtWork]

Here is an example of binding a recordset to a listbox, I'm not sure I ever did it for a form but it's probably the same thing.

    Set rsList = New ADODB.Recordset
    
    rsList.CursorLocation = adUseClient
    
    'Code for setting up the ADO Command object with 
    'parameters, etc., left out
    rsList.Open adoCmd, , , adLockReadOnly
    
    Set Me.lstInvOrder.Recordset = rsList

Note that the CursorLocation must be set to client side.

 

[Ed2020]

If Access has never done temporary tables - this can't be a first time data requirement?! How are people doing this?

The same way that you're doing it. In Access-world when most people refer to "temporary tables" they mean the data is temporary, not the table. You can always create and drop tables at runtime, but these aren't really temporary tables either...

Ed Metcalfe.

Please do not feed the trolls.....

 

[jordanking]

Disconnected recordsets are the way to go:

here is a link to look at:

http://support.microsoft.com/kb/184397/en-us

you open an ado recordset, set the connection to null, bind the recordset to the form, allow user changes, then reopen recordset and batch update against appropriate table or append to another table. This is ideal because it allows you to perform all your data integrity/business logic validation without placing heavy locking on underlying tables. This will not "bloat" your application at all and can be applied to client/file and client/server split operations.

Try it and let me know if you run into any trouble

.....
I'd rather be surfing