Make Table action

[Guest]

I am using the code below to make a new table named with the date and
"Batch". In my sql string it ignores the variable and just names the table
"tmpBatch".

Is there a way to use the variable as the new table name?


tmpBatch = Format(Date, "yyyymmdd")
tmpBatch = tmpBatch & " Batch"
'Makes temp table to store last batch
sql = "SELECT [MOVEX BATCH FILE].* INTO tmpBatch from [MOVEX BATCH FILE]"

DoCmd.RunSQL sql

Thank you.

Cindy

 

[John Spencer]

sql = "SELECT [MOVEX BATCH FILE].* INTO [" & tmpBatch & "] FROM [MOVEX
BATCH FILE]"

 

[Guest]

Thank you John.

Is there also a way to shown the number of records in the table that was
created? Do I need to open the record set and get the count?

sql = "SELECT [MOVEX BATCH FILE].* INTO [" & tmpBatch & "] FROM [MOVEX
BATCH FILE]"

I am using the code below to make a new table named with the date and
"Batch". In my sql string it ignores the variable and just names the
table
"tmpBatch".

Is there a way to use the variable as the new table name?


tmpBatch = Format(Date, "yyyymmdd")
tmpBatch = tmpBatch & " Batch"
'Makes temp table to store last batch
sql = "SELECT [MOVEX BATCH FILE].* INTO tmpBatch from [MOVEX BATCH FILE]"

DoCmd.RunSQL sql

Thank you.

Cindy

 

[John Spencer]

I'm not sure it will work, but try the recordsaffected property of the
database object.
Currentdb().RecordsAffected

Thank you John.

Is there also a way to shown the number of records in the table that was
created? Do I need to open the record set and get the count?

sql = "SELECT [MOVEX BATCH FILE].* INTO [" & tmpBatch & "] FROM [MOVEX
BATCH FILE]"

I am using the code below to make a new table named with the date and
"Batch". In my sql string it ignores the variable and just names the
table
"tmpBatch".

Is there a way to use the variable as the new table name?


tmpBatch = Format(Date, "yyyymmdd")
tmpBatch = tmpBatch & " Batch"
'Makes temp table to store last batch
sql = "SELECT [MOVEX BATCH FILE].* INTO tmpBatch from [MOVEX BATCH
FILE]"

DoCmd.RunSQL sql

Thank you.

Cindy