Rename Table with Date

[Guest]

Is there a way to rename a table imported via VBA to the current date? And
the same the following day? A user here wants to keep a record of the
imported report for about 3-4 days...any suggestions welcome!

Thanks in advance!!

 

[Lynn Trapp]

any suggestions welcome!

My first suggestion is Don't Do It. You should NOT put data (in this case a
date) in a table name and/or field name. Rather, simply add a date field to
the table and query for all data that falls within the date range.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Guest]

While importing the table you can rename it

docmd.TransferDatabase acImport,"Microsoft Access" ,"Mdb to import from"
,acTable,"Name of the table to import",Format(date,"ddmmyyyy")

 

[Guest]

Thanks!!!

While importing the table you can rename it

docmd.TransferDatabase acImport,"Microsoft Access" ,"Mdb to import from"
,acTable,"Name of the table to import",Format(date,"ddmmyyyy")

 

[Guest]

Appreciate the word of caution Lynn...

There is no data interaction taking place with these tables named as the
current date. They are only being kept for a couple of days and are
permanently deleted after that. A copy of the most current table is renamed
"today" whenever the user needs to run a comparison against an Excel
spreadsheet that is linked to the database.

Would you still caution against it when used as explained above?

 

[Lynn Trapp]

Would you still caution against it when used as explained above?


Yes, I would. Even though it may appear harmless in your situation, you are
ALWAYS better off to use good database design principles. You never know
when your user may decide to change the specs on you (trust me, they do it
all the time) and want to keep data for a longer period. If you design it
correctly from the beginning, then you will have fewer headaches when those
changes come your way.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Guest]

Thanks Lynn, appreciate your insight.

Yes, I would. Even though it may appear harmless in your situation, you are
ALWAYS better off to use good database design principles. You never know
when your user may decide to change the specs on you (trust me, they do it
all the time) and want to keep data for a longer period. If you design it
correctly from the beginning, then you will have fewer headaches when those
changes come your way.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[John Griffiths]

Jumping in at a late stage.

Why don't you get the best of both worlds.

Use one table as recommended with a data field using "Date()" but (here's
the science bit) create a view (MS Query) called "Today".

SELECT <columns less date-field>
FROM <single source table here>
WHERE <date-field> = Date()

Of course you can then create one for "Yesterday"
SELECT <columns less date-field>
FROM <single source table here>
WHERE <date-field> = Date()-1

Any use? John

 

[Guest]

Thank you John for contributing to my options