Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Wanet Telecoms Ltd on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using a temp table vs. array?

Status
Not open for further replies.

Apollo6

Technical User
Jan 27, 2000
418
US
I have frmStandard that displays a regimen of drugs for a disease. The drugs,units, and cycles populate into a listbox on frmStandard from a query against tblRegimens.

On frmStandard, I have a button that opens a second form, frmAlternate, that also has a listbox that is populated by a macro that runs a make-table query against the listbox in frmStandard and pulls the data from a "temp" table.

The reason for this is to allow the user to add/delete/modify the data in the frmAlternate listbox without altering the standard regimen data.

My problem comes when multiple users are using the forms and try to build an alternate regimen at the same time. The macro fails for one user because another user may be in the frmAlternate which has a lock on the "temp" table.

How can I get the above functionality from my application without causing conflict when multiple users are on the same forms? I was possibly thinking I need to build some type of array vs. a temp table, to populate the frmAlternate listbox but not sure how to go about this. Any suggestions or help would be great.
 
Hi!

As long as your going to use code you can just create your temp table in code. The advantage to this is you can use variables in the code to add the user name or the current time to the name of the temp table. That would make each table unique no matter how many user are on at the same time.

hth


Jeff Bridgham
bridgham@purdue.edu
 
You could probably build the array, but if you have several users and there is a lot of data to be stored, it could cause the system to slowdown. One thing you could do is get the SQL for your query that creates the table and put it in an Call statement in VBA and have the table name created dynamically.

You could have it created for each user that uses it, like temp1, temp2, temp3 etc. This, of course means you would have to change the way you access the tables since now you have several tables with different names. You would have to make any forms or reports be accessed through code so you can change their datasource property.

Don't know if that helps much, but for what it's worth, that is my 2 cents.
 
Of course, if you split the app properly, you can create the temp table in the FE, thus avoiding the conflict.

Then, again, creating temp tables in Ms. A. will ALWAYS cause ye olde bloat, and you will need to compress the ap-p periodically, so -while creating the array may be somewhat more of a chore, it can also relieve the necessity of compacting the app as often.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places
 
Options, options...When I create temp tables, I do it in a temp database, so the bloat gets blown away each session.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks
Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.
 
Okay, one more option. ADO allows creating in memory recordsets which can be sorted and filtered as needed and then destroyed when no longer needed.
 
options upon options upon options ... but each has it's pros and cons. Creating temp databases itself uses resources (mostly TIME) as well as the destruction and both 'exercise the hardware a bit. ADO is at least suspected of a dgreee of sloth itself. These are not critisims, as each DOES have it's "pros", nerely the observation that the many paths do exist.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places
 
All are great options... Thanks for the ideas, I'll let y'all know what turns out.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top