Increment temp tables

[wailingrecluse]

Hi all

I have multiple users across a network using a db and as a solution to a problem i've faced, I am trying to do the following.

If a user saves a new record, I would like access to check if tempTable exists, if it does then check for tempTable1, then tempTable2 and so on.

When it reaches a table that does not exist, I would like it to create the table and save the record into that table. Essentially allowing each user to have a seperate tempTable.

When they have finished with it, I would like access to delete the tempTable relating to their record and the data in it.

At the moment, I have one perm holding table and when the save button is pressed, the data is copied into that table and held until another button is pressed, where it deletes from holding table. The issue i have is that if more than one user at a time is using the db, then more than 1 record will be copied into the holding table and I do not want this.

I'm sure I know how to create a table and how to delete, but its the checking to see where a table can be created and incrementing this by one which i'm not sure of.

Any help will be greatly appreciated.

WR

 

[pbaldy]

If the application is split, as it should be for a multiuser application, and the temp table is in the front end, you would only need one temp table. Is it split, with each user having their own copy of the front end?

 

[wailingrecluse]

Hi there

Thanks for the reply.

No, I haven't split the db - never attempted that before.

Do you think this will solve my problem?

If so, can you recommend any good tutorials to do this? and also, does this require something to be installed on the users local machines?

Thanks again

 

[pbaldy]

I think it will solve your problem, and help avoid corruption. Here's more info and a free tool to automate the distribution of front ends (which you will see should be on each user's PC):

http://www.granite.ab.ca/access/splitapp/index.htm

 

[wailingrecluse]

Hi there

Thanks for the reply.

No, I haven't split the db - never attempted that before.

Do you think this will solve my problem?

If so, can you recommend any good tutorials to do this? and also, does this require something to be installed on the users local machines?

Thanks again

Hi again, sorry - if my understanding is correct, the BE would house the tables and data and the FE would hold all the forms, queries etc?

if this is correct, i'm not sure if this does solve my issue:

When the user clicks the save button, I have an append query which stores the current record into a holding table as well as the main table.

When the user clicks the email button, the data is written to an xml file and then deleted from the holding table.

If each FE user has access to the queries, pressing the save button will still have the same effect ie more than one record being saved into the holding table?

Am i mis-understanding?

 

[pbaldy]

You put the holding table in the front end (main data tables in the back end, as you mentioned). Since each user has their own copy of the front end, no user puts their data in anyone else's holding table.

 

[wailingrecluse]

You put the holding table in the front end (main data tables in the back end, as you mentioned). Since each user has their own copy of the front end, no user puts their data in anyone else's holding table.

Ok great, that makes perfect sense!

I'll make a couple of backups before I try this tonight and I will let you know how I get on.

Thanks a lot for your help!

WR

 

[pbaldy]

Backups are always a good idea. Good luck!

 

[c_smithwick]

On another note, for your future reference, creating and deleting temp tables leads to some serious database bloat even in a short time period. I have resolved a similar problem myself by declaring a user defined type in VBA to hold the record's data and perform operations with the data before storing it in a table. This works even if you have multiple users using the same Front End as each user will be running a separate program instance on their machine and hence any volatile variables will be unique to the instance of the program they are running. Something similar to the below:

Public Type RECORD
  lngOrder As Long
  dblPO As Double
  lngPR As Long
  strTCWO As String
  lngVID As Long
  strShop As String
End Type
 
Public Function someFunction()
 
Dim rcTemp as RECORD
Dim rstlData as DAO.Recordset
 
rcTemp.lngOrder = 50
rcTemp.dblPO = 450067894
rcTemp.lngPR = 11123697
rcTemp.strTCWO = "N55439"
rcTemp.lngVID = 3602
rcTemp.strShop = "Main"
 
'do something with this records data and
 
'store it permanently in a table
Set rstData = CurrentDb.OpenRecordset("tblData")
With rstData
    .AddNew
    ![OrderNmbr] = rcTemp.lngOrder
    '.....  etc., etc.
    .Update
End With
Set rstData = Nothing
 
End Function

 

[wailingrecluse]

Backups are always a good idea. Good luck!

Hi mate

Worked a treat - although I did have to re-write a couple of queries to get the data pulled from the local temp table instead of the back end table

Thanks a lot for your help.

WR