Can you add the date/time to a newly created Table?

[Douglas Lovatt]

Is it possible to have the Date/Time appear as part of the name of a table? I don't want to create and rename a table I want to run a Query that will create a Table that will have the name + date or just the date.

Example

"Todays file 20070308 11:10:34 AM"

or
"2007080308"

Or
"2007080308 11:10:34"

 

[The_Doc_Man]

You can do so but only by creating the table through VBA. You would have to build the name string dynamically and then perform the .Add function on the TableDefs collection. (Or something like that.)

This is a rather advanced function that should not be attempted by the squeamish or by folks with limited VBA experience or comfort levels.

Two other issues come to mind.

First, forget about any punctuation the table names. Second, pick a format that doesn't include blanks, either.

Access does really well but there is no need to tempt your fate by picking a name that many systems cannot honor. Not to mention that the table's creation date is already a part of the table, as part of its properties in the TableDefs("tablename") object. So if all you wanted to know was the date and time of creation, it is there. If you had another purpose to the choice of table names, perhaps you should rethink it. Arbitrarily created table names for tables that otherwise contain similar data are a step down the path of de-normalization. You would only need ONE table and include the date of data entry as a field.

 

[Douglas Lovatt]

The Doc Man
Thank You
My end goal was to transfer the table to a speadsheet, in a folder with the previous days spreadsheets. The date in the table name was two fold, create daily versions and have historic data.

Thank you again

 

[The_Doc_Man]

Here is what I'm thinking. You can store all of your data in a single table with a date of data entry as one field. Build a QUERY that selects "date of data entry" = yesterday (really, DateSub of 1 day from today's date, look up DateSub in Access help.)

OK, once you have that query, it will give you only yesterday's data. (Of course, you can make it give you ANY day's data...). You can export the query as though it were a table. When you do the "Analyze it with Excel" function you can save the result and at that time, give it a name.

Keeping separate tables bars you from doing longer-term analysis. Also, though it will take a while, you are building into your database the absolute certaintly that it will eventually reach one of the system limits - the number of tables you can have in a database.

Keeping a single field with the date inside, you limit yourself to the max size of an Access database in general, which is like 2 Gb. Your historic data resides in the main table. You want any specific dates? They're only a query away.

Learn to step away from the spreadsheet sometimes. It is thinking outside of the box. Or in this case, the flat file.