Protocol re: update/append queries and temp tables in a database

[Guest]

Hi,

I have a live database application that is updated with new data from other
databases periodically (every 4 months or so). To transfer data and update
tables in the live database, I import tables, design and run append and
update queries, and also create some temp tables. I prefix the names of these
objects with "_" to indicate that they were created as temporary objects.

My question is that should I leave these objects in the live database after
I'm done transferring the data, or should they be deleted? Or better still,
after all the data transfer, create a new database file and import only the
relevant objects?

I'm not sure of the pros and cons of either approach, and will appreciate
any pointers on this issue. BTW, the live database has the objects invisible
to the users, and they use forms to view/edit/add data, and run reports. So,
I don't have to worry about the users seeing all the temporary queries etc.
Also, space is not an issue as yet. The database is about 7MB, and I started
with 5 MB about a year ago. If there are other facts that I did not mention
re: database, which would be helpful in your answer, please ask me.

Thanks for your thoughts.

-Amit

 

[Jeff Boyce]

Amit

JOPO (just one person's opinion)...

What you are doing sounds reasonable and logical. I wondered, as I read
your description, if the imports are always the same sources, same fields,
same (kind of) data? If so, you might not need to (re-)build "temp" tables
each time. Instead, you could create semi-permanent tables you use for
import processing.

If you are concerned about potential bloat from the imported and temporary
data, you can regularly Compact your database (backup first). There are
other approaches involving using a separate .mdb file as your
import/transfer database, but it lacks the straight-forward simplicity of
your current design.

Or perhaps I didn't understand what you are already doing...

 

[Guest]

Amit

JOPO (just one person's opinion)...

What you are doing sounds reasonable and logical. I wondered, as I read
your description, if the imports are always the same sources, same fields,
same (kind of) data? If so, you might not need to (re-)build "temp" tables
each time. Instead, you could create semi-permanent tables you use for
import processing.

If you are concerned about potential bloat from the imported and temporary
data, you can regularly Compact your database (backup first). There are
other approaches involving using a separate .mdb file as your
import/transfer database, but it lacks the straight-forward simplicity of
your current design.

Or perhaps I didn't understand what you are already doing...

Hi Jeff,

Thanks for the response. No, you understood the question correctly. The
updates are similar enough (in the sense that the same tables are updated),
but since the data sources are formatted differently, the temp tables and
queries are designed every time, and can't be re-used. (I suppose they
*could* be re-used, if I spent more time designing them, but for the current
situation, the time spent on that design would not justify the time saved in
transfer, as the data transfer is not that complex.)

I do compact the database periodically (every 2-3 months). Thanks for the
reminder

Cheers,

-Amit