By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,272 Members | 1,437 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,272 IT Pros & Developers. It's quick & easy.

Creating a Temp db

P: n/a
Hi:

I would like to know how to create a temp DB to store the data in a table while I do
something else with the table.

Specifically, how do I
create the temp
remove the temp

I want to be certain that these are not linked or anything.

Thanks

John Baker
Nov 13 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
John Baker wrote:
Hi:

I would like to know how to create a temp DB to store the data in a table while I do
something else with the table.

Specifically, how do I
create the temp
to create...
Help: CreateDatabase

to get your temp table in there...
Help: TransferDatabase, CopyObject

to link to a table in there...
Help: CreateTableDef + associated object properties

remove the temp


Help: Kill

Optionally after error: Help: Close Method :-)
Nov 13 '05 #2

P: n/a
John Baker <Ba******@Verizon.net> wrote in message news:<hp********************************@4ax.com>. ..
Hi:

I would like to know how to create a temp DB to store the data in a table while I do
something else with the table.

Specifically, how do I
create the temp
remove the temp

I want to be certain that these are not linked or anything.


Tony Toews has provided an excellent sample of how you can do this at:
http://www.granite.ab.ca/access/bloatfe.htm

It solves a common problem that new developers encounter when they create
temporary tables in their database app - the .mdb file will "bloat" or
increase in size because of unreclaimed disk space used by the temp
tables. The solution he provides solves this problem.
Nov 13 '05 #3

P: n/a
I actually have a different solution, which is also a question:

The way I do it is the following:
in my database, I made a table with the fields and datatypes set up
the way I want. Whenever I need the table, I write the values to it,
then read the necessary values from it. When i'm all finished, I
delete all the proper values from the table. This way I'm not
creating and removing objects from the database - it's just one table
that I write values to and delete when I'm done. Also, when there is
an error that occurs with writing to the table or using values from it
or deleting values from it, I can go in there and figure out by what
is there, which user it was that was using it, and what they were
doing when the problem occured.

Any comments on doing it this way as opposed to actually creating a
table or database and then deleting it?
mi****@execpc.com (almish) wrote in message news:<95**************************@posting.google. com>...
John Baker <Ba******@Verizon.net> wrote in message news:<hp********************************@4ax.com>. ..
Hi:

I would like to know how to create a temp DB to store the data in a table while I do
something else with the table.

Specifically, how do I
create the temp
remove the temp

I want to be certain that these are not linked or anything.


Tony Toews has provided an excellent sample of how you can do this at:
http://www.granite.ab.ca/access/bloatfe.htm

It solves a common problem that new developers encounter when they create
temporary tables in their database app - the .mdb file will "bloat" or
increase in size because of unreclaimed disk space used by the temp
tables. The solution he provides solves this problem.

Nov 13 '05 #4

P: n/a
"user_5701" wrote

in my database, I made a table with the
fields and datatypes set up the way I want.
Whenever I need the table, I write the
values to it, then read the necessary values
from it. When i'm all finished, I delete all
the proper values from the table. This way
I'm not creating and removing objects from
the database - it's just one table that I write
values to and delete when I'm done. Any comments on doing it this way as
opposed to actually creating a
table or database and then deleting it?


Access does not recover the space used by deleted Records until you perform
a Compact. It also doesn't recover the space used by a deleted Table until
you perform a Compact.

The approach of Creating a Temporary Database in which Temporary Tables are
created and used, then the entire Temporary Database deleted is to avoid
"bloat" from the phenomena described in the first paragraph here (and it is
a good one -- if you do lots of adds and deletes, the growth ("bloat") can
be substantial. By avoiding the unrecovered space problem, the need to
frequently Compact production systems can be minimized.

Conversely, if you habitually and frequently Compact, then your way is
simpler.

Larry Linson
Microsoft Access MVP
Nov 13 '05 #5

P: n/a
us*******@hotmail.com (user_5701) wrote:
I actually have a different solution, which is also a question:

The way I do it is the following:
in my database, I made a table with the fields and datatypes set up
the way I want. Whenever I need the table, I write the values to it,
then read the necessary values from it. When i'm all finished, I
delete all the proper values from the table. This way I'm not
creating and removing objects from the database - it's just one table
that I write values to and delete when I'm done. Also, when there is
an error that occurs with writing to the table or using values from it
or deleting values from it, I can go in there and figure out by what
is there, which user it was that was using it, and what they were
doing when the problem occured.

Any comments on doing it this way as opposed to actually creating a
table or database and then deleting it?


Another problem is what if multiple users need to work with this temporary data at
the same time? Is there some means of keeping each persons data separate? If so
then what happens if the process is somehow cancelled or terminates abnormally. That
users records could still be in that table causing complications the next time they
run the process.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 13 '05 #6

P: n/a
Tony Toews wrote:
Another problem is what if multiple users need to work with this temporary data at
the same time? Is there some means of keeping each persons data separate? If so
then what happens if the process is somehow cancelled or terminates abnormally. That
users records could still be in that table causing complications the next time they
run the process.


I do things on a SQL Server with temp data, mostly for selection and use
the user's login name and workstation to identify temp data as belonging
to a particular logged in user. I never assume that the last process to
use this data cleaned itself up due to abnormal termination and the like
(or lazy programmer <g>) so I put all the cleanup code in first.

--
Pretentious? Moi?
Nov 13 '05 #7

P: n/a
Trevor Best <nospam@localhost> wrote:
Another problem is what if multiple users need to work with this temporary data at
the same time? Is there some means of keeping each persons data separate? If so
then what happens if the process is somehow cancelled or terminates abnormally. That
users records could still be in that table causing complications the next time they
run the process.


I do things on a SQL Server with temp data, mostly for selection and use
the user's login name and workstation to identify temp data as belonging
to a particular logged in user. I never assume that the last process to
use this data cleaned itself up due to abnormal termination and the like
(or lazy programmer <g>) so I put all the cleanup code in first.


Yup, that almost works in almost all situations. One, admittedly rare, situation
would be if the user terminates the process abnormally and then moves to another
system or no longer uses the database.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 13 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.