Naming a table created with a make table query from a field in the table?

[AllanN]

Hope someone can help.

I need to be able to use a variable to "name" a table created by running a "Make Table" query. The variable is a field in the both the original source table, the query and the newly created table.

We have mutiple stores who create "batches" of invoices etc. Each batch when closed is exported as an Excel spreadsheet and emailed to our accounts office.

My application creates a new unique "batch number" for each new batch for each store based on a store code taken from a set up table and combining it in a string with Day of Month, Day of Week and todays date/time. Thus multiple batches can be created by a store in the same day but each has a unique number.

I want to use this batch number to "name" an new table I create with a Make Table Query. At the momement I have to nominate a name for the new table in the Query ie. "XXBATCH" where XX is the two letter store code, then email this table as an XXBATCH.XLS file. Thus each emailed file has the same name?? This causes problems at the receiving end with overwrites etc.

If the above cannot be done can anyone suggest an alternative way of renaming either the newly created table with a variable or the export table (perhaps using DOS batch file?)

Appreciate your support. Great site by the way. Very helpful.

 

[Emohawk]

Hi Allan
You'll have to write your make table query in code as you can't do this with a normal make table query.

Something like this should work.

Dim MySQLString, BatchCode as String

'Insert your process here where you set your BatchCode value

MySQLString = "SELECT * INTO " & BatchCode & "BATCH FROM MyTable"

DoCmd.RunSQL MySQLString, False

Hope this helps.

BTW

How is the Sunshine Coast? I'm a BrisVegas lad working in London... Missing the sun, sand and surf... We are supposed to be in summer but I think I saw the sun for about two days.

 

[Rich]

You need to get out of the office more Em, we've had the sun here for three days now

 

[Emohawk]

I must have missed those "sunny" days...

 

[AllanN]

Em,

Thanks.....I just knew I would have to learn code sooner or later. See I'm just a learner at Access and have only just conquered using the built in macros.

Oh well...onward and upward. Coding here I come.

Really crappy day here in Paradise. Brilliant sunshine, a "cool" 18 degrees - middle of winter - and the surfs been ragin for the past few days. 2- 4 metres and rockin'. Ahh Surfers..beautiful one day...perfect the next.

Cheers
Al