Re: Table names in make-table query
- From: Klatuu <Klatuu@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 3 Aug 2007 08:50:00 -0700
"They are exported on a monthly basis so need to have a new table name for
their use."
No, you do not need a new table each month. You are thinking in Excel and
trying to speak Access.
You said it is only a source to feed other applications their data. Once
that is done, the data becomes useless. Now, I can see where you may need to
be able to reproduce a specific data feed. That can be more easily
accomplished using one table than it can with changing the name each month
and saving each month's data in a different table.
The way to do this is to link to the external data you will be importing if
you can rather than import it directly. Once you have linked to it, use an
append query to copy the data into the Access table. Add a field that will
contain an identifier that will tell you what you need to know about that
paricular batch.
Now you can use a query to retrieve any batch of data in the table. You
don't have to hunt for a specific table name and create or modify existing
queries to use the data. All you need to do is answer a prompt that will ask
for the batch identifier.
Another issue with make table queries is you don't know for sure you are
going to get the data type you need in a given field. the import wizards
make assumptions (sometimes incorrectly) on what a data type should be. It
also uses the database default on how large a text field should be. Unless
you change the default it is 255 characters. Doesn't really waste any
database space, but does eat up memory. when you load a record into memory,
it uses the amount of memory required to accomodate the maximum length of the
field.
A rule of thumb is if you are using a make table query in a production
environment, you are probably not doing it the best possible way.
--
Dave Hargis, Microsoft Access MVP
"Emjaysea" wrote:
Thanks - these tables are just being used to provide raw data to other.
programs and are exported directly into their databases. They are not
allowed access, even limited, to mine. They are exported on a monthly basis
so need to have a new table name for their use.
"John W. Vinson" wrote:
On Thu, 2 Aug 2007 12:40:00 -0700, Emjaysea
<Emjaysea@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
I want to be prompted for the table name that a make-table query will create
rather than entering it into the query - so I can use the query in a macro
that will create and output tables with different names. Is this possible?
No, not in a macro.
It can be done in VBA code.
But I really must ask... WHY? Storing data - uncontrollable, user entered data
at that - in table names is a Very Bad Idea. Are you assuming (perhaps based
on exprerience with dBase or other programs) that you must create a new table
in order to create reports? If so that assumption is *wrong*. What use will be
made of these tables once you create them?
John W. Vinson [MVP]
- References:
- Re: Table names in make-table query
- From: John W . Vinson
- Re: Table names in make-table query
- Prev by Date: Re: PK - To AutoNumber or Not To AutoNumber - That is the Question! :-
- Next by Date: RE: Data Normalization
- Previous by thread: Re: Table names in make-table query
- Next by thread: Re: PK - To AutoNumber or Not To AutoNumber - That is the Question! :-
- Index(es):
Relevant Pages
|
