Make Table Query - table name a variable

[Bonnie]

Hi,

You can tell when I'm working - I'm asking for another suggestion:

I would like to run a make table query but name the resulting file the date
plus a sequential number? Any ideas?

Thanks in advance,

Bonnie

 

[vvariety]

Hi,

You can tell when I'm working - I'm asking for another suggestion:

I would like to run a make table query but name the resulting file the date
plus a sequential number?  Any ideas?

Thanks in advance,

Bonnie

Hi Bonnie,

I have a export function that works similar

1. Create the make table query with some additional fields, Today's
Date, Seq Number, File Name then run it.
2. Create a module that will loop through each of the records and
update the seq. number until the end of recordset
3. use an update query to combine the fields todays date and seq
number into the file name.

Hope this helps

 

[Bonnie]

I really don't have to change anything in the file - just the name of the
table. How do you change the file name with an update query? Did you
misunderstand my issue?

 

[John W. Vinson]

Hi,

You can tell when I'm working - I'm asking for another suggestion:

I would like to run a make table query but name the resulting file the date
plus a sequential number? Any ideas?

Thanks in advance,

Bonnie

I'd suggest...

DON'T.

MakeTable queries are VERY rarely necessary.
Storing data in a tablename is *very* bad design and makes searching for data
very difficult.
Having multiple tables with the same structure in your database is very
non-normalized.

You can do just about anything with a Select Query selecting a subset of
records from a table - based on a date field, say - that you can with a table
created by a make-table query.

Could you explain what these tables are, and what you're trying to accomplish?

 

[Bonnie]

Hi John,

I have a couple of girls that key data for a customer - I run it through
some clean up and validation checks, dedup, etc., format it for the customer
and send them the final results. Though I prefer to send a csv or flat
file, they want an mdb so I would like to make a final table and using
transfertext, export it to an external mdb to send to my customer. I usually
(csv or txt usually) name the file with date & sequential number so nobody
overwrites last weeks work and everyone has an automatic backup. I can
always rename but I thought if I could generate with a make table query it
would be one step. Explaining it makes me think I should use a static name
for the files and zip to a dated zip file instead.

Sounds a little convoluted now that I put it on paper!

Thanks,

Bonnie

 

[John W. Vinson]

Explaining it makes me think I should use a static name
for the files and zip to a dated zip file instead.

You can use the CreateDatabase method to create a new .mdb file (see the VBA
help), and export to that file if you wish. You can also construct a tablename
or database name using a syntax like

Dim strDBName As String
strDBName = "Export" & Format(Date, "yyyymmdd")

to create a string containing "Export20080404" which can then be used in the
TransferDatabase and/or CreateDatabase calls.

 

[Bonnie]

Yeah - that's what I was looking for. I'll give it a go.

Thanks!

Bonnie