Solved Rename tablewith a week number (1 Viewer)

[Number11]

is it possible to rename a table ending with the current week number it was created and or

export the table via a query ending in the week number it was generated ?

 

[theDBguy]

Almost anything is possible, but why? Also, you export the data, not the table name. So, I'm not sure I understand your second question.

 

[The_Doc_Man]

Generally, you rarely export tables, you export queries. So store the week info in the table, then have the query filter for whatever week you want (but don't include the week ID in the SELECT clause, only in the WHERE clause.)

Renaming tables is possible but tends to break relationships AND/OR require you to rebuild new relationships.

 

[Number11]

Thanks for your pointers, i have now created a new table and have a vbs to rename it adding the current week number, and have also changed my code so when i export the table to excel it names the file with the current week number too so all sorted.

will share code in a bit

 

[Galaxiom]

will share code in a bit

You didn't take any notice of the advice. What you are doing is wrong.

 

[Pat Hartman]

It is very wrong to create a table in your database for each week.

As Doc suggested, use a query to select data for the specific week from your table. You would use code to concatenate the selected week to the table name in your TransferSpreadsheet method.

Sample code for creating the export file name might look like:

FileName = "YourTableName"& "_" & Me.txtYear & Me.txtWeekNum & ".XLSX"