Dynamic table name

[SBCUser666]

I have an Access 2003 query that does a SELECT.. INTO table_name.
I would like the table_name to be a fixed value plus todays date.
Example: tbl_EXTRACT_070709
Can this be done?

 

[ajetrumpet]

i wonder if this would work:

SELECT whatever INTO tbl_EXTRACT & cstr(date())

 

[SBCUser666]

tbl_EXTRACT & cstr(date()) gives me an error when I save the query. Error is Query input must contain at least one table or query.

[tbl_EXTRACT & cstr(date())] gives me a table name of tbl_EXTRACT & cstr(date())

(tbl_EXTRACT & cstr(date())) gives me a puncuation error when saving query

"tbl_EXTRACT & cstr(date())" gives me a syntax error when saving query.

I can't figure out how to encapsulate part A and B of the file name and still have the function resolved.

 

[Leon123456]

You can't do this within a query, but you could create a query on the fly in code:

Function dynamictablename()

Dim tblname As String
Dim sql As String

tblname = "tbl_EXTRACT" & Format(Now(), "ddmmyy")
sql = "SELECT dummytable.* INTO " & tblname & " from dummytable;"
DoCmd.RunSQL (sql)


End Function

I've simplified this just to pull everything out of a table called 'dummytable', so you will need to replace the '*' with your named fields.

HTH

Leon

 

[SBCUser666]

I see what you are doing. The function would end up in a Module. How would I 'run' the module?

 

[SOS]

I see what you are doing. The function would end up in a Module. How would I 'run' the module?

You don't run a module. You run procedures within a module and therefore you would just call:

Call dynamictablename

from some event.

 

[SBCUser666]

OK, this is where I get lost. Where do I put the Call dynamictablename statement?

On the On Click event for a command button? Or in the Private Sub it opens when I click on the ... on the On Click line? I tried the latter and I get a error saying Compile Error: Expected variable or Procedure, not module.

 

[SOS]

I tried the latter and I get a error saying Compile Error: Expected variable or Procedure, not module.

Make sure you didn't name your module the same name as the procedure.

 

[SBCUser666]

OK, got it.

Command button has On Click sub that does Call MCFSQuery

Module MCFSMismatch has a Public Function called MCFSQuery that contains your code.

Since the query code is long it took awhile to get the line continuation all set up. And since the filename I wanted had spaces in it I had to figure out where to put the [ and ] around it.

Many thanks!!!!

Next step is to create Excel sheet from the table the above process creates. I think I have that using the TransferSpreadsheet method.

 

[gemma-the-husky]

in general anywhere you need to repeat the same process on different files/tables there ought to be a way to automate it

so - store the file name in a variable, and either load that into a query, or into a sql string - maybe store a folder name somewhere, and test all the files in that folder to see if the extension os one you want - or use a file picker to maually select the file.

bring the table into a standard table in your database, and then run a set routine to process that table

its just a matter of finding the best way to structure all this

and you will most likely find you can reuse most of the code in a future applciation, with not too much effort.

 

[Atomic Shrimp]

Next step is to create Excel sheet from the table the above process creates. I think I have that using the TransferSpreadsheet method.

Your database is going to get cluttered up with many different export tables, all with the same structure, and different names. That's not generally a good thing - I think you're making this far more convoluted than it needs to be.

You could just bypass the creation of the custom-named export table altogether - just pass the name of the query to TransferSpreadsheet, instead of a table, and tell it to name the output file with a date suffix, so something like:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "[YourQueryName]", "Extract" & Format(Date(), "yyyymmdd"), True

You'll need additional code to handle the possibility of trying to export twice on the same day (or just include the hour, minute and second in the filename)

Unless there's some really good reason to create multiple, similar export tables (and there never is) as an intermediate step, don't do it!

 

[SBCUser666]

This whole process is done once a quarter to create Excel sheets that workers use for their own business process. So, bottom line, the Access part is just a report generator. The same 12 tables are created every time. Since I can apparently create the Excel sheets directly I will skip creating the tables. I may be back when I start on that process.

 

[Atomic Shrimp]

Cool. As a general rule, if you can get a query to show you the data you want, you shouldn't store it in a table, but just use the query as if its results were a table.

 

[SBCUser666]

OK, lost again.

The On Click event does Call MCFSExport

MCFSExport is a Public Function in Module MCFSMismatch.

MCFSExport is DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, MCFSQuery, "MC - FS Mismatch " & Format(Now(), "ddmmyy"), True

MCFSQuery is another Public Function in Module MCFSMismatch

Now get Run-time error 2342 A RunSQL action requires an argument consisting of an SQL statement.

Contents of MCFSQuery:
Public Function MCFSQuery()
Dim sql As String
sql = "SELECT [4A - MC Persons].Worker AS [MC Worker] FROM [4A - MC Persons];"
DoCmd.RunSQL (sql)
Exit Function
End Function

In Debug if I hover over sql the popup box shows a good SELECT statement

So, where do I store the SELECT statement???

 

[Atomic Shrimp]

Your MCFSQuery function is running the SQL, but not actually doing anything with the results.

I would just create a query, switch to SQL view, paste in your SQL, save it as qryWhatever, then your transferspreadsheet code is just:


DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryWhatever", "MC - FS Mismatch " & Format(Now(), "ddmmyy"), True

 

[SBCUser666]

Thanks again! I had tried this but did not put the " around the query name. Since it worked this time I had to find where the XLS file went. Found it in my userid Documents folder. I figured it would default to the folder the Access DB is in. I will fix where it goes.

P.S. I watched paint dry and grass grow.

 

[SOS]

Also, you can't use RunSQL with a SELECT query. It is only good for Append, Update, and Delete queries (Action queries).

 

[Atomic Shrimp]

Thanks again! I had tried this but did not put the " around the query name.

If you're passing it a query object you've defined in code, I think you dispense with the quotes, otherwise, it's looking for the name of a table or query passed as a quoted string (I had to look it up to remind myself)

Since it worked this time I had to find where the XLS file went. Found it in my userid Documents folder. I figured it would default to the folder the Access DB is in. I will fix where it goes.

I tend to include a single-record options table in my database apps, particularly if they have to do something like exporting or importing data - you can include as many fields as you like in your options table to store things like file export paths, and just pull them out of the table with code like:
DLookup("[YourExportPathFieldName]","tblOptions","ID=1")

so your line of export code would then be:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryWhatever", DLookup("[YourExportPathFieldName]","tblOptions","ID=1") & "\MC - FS Mismatch " & Format(Now(), "ddmmyy"), True

P.S. I watched paint dry and grass grow.

Cool, cheers.