Table names in make-table query

[Emjaysea]

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?

 

[John W. Vinson]

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]

 

[Emjaysea]

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.

 

[fredg]

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.

You don't need to create a Table to export.
Just create an everyday Select query and export that.
If you need criteria, use the same criteria you would use in a
MakeTable Query.

Look up Transfer Database, TransferSpreadsheet, and TransferText in
VBA help.

Now if you wish to give us some more specific details you'll get a
more specific reply.

 

[John W. Vinson]

You don't need to create a Table to export.
Just create an everyday Select query and export that.

Seconded! Thanks Fred.

John W. Vinson [MVP]

 

[Klatuu]

"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.