Function to Append DATE to output tablename for Make Table Query

[Mo Ka]

Hi all,

Is there a function (or other method) to automatically append the system
date to the output tablename of a make table query such that the new one
does not overwrite an older table?.

I need these for stats but want to keep all previous tables created by
the same query and I do not want the user to have to enter the date the
table was created.

Output table name BASETABLENAME_[mmddyy]

Thanks in advance.

Maur

[Pat]

This will give you a table named "MyTableCreatedOnMMDDYY" I choked on
getting Access to take an underscore or bracket in the name table SQL. If
there's a simple way to do this, I'd be happy to read about it.

Sub NameTable()

Dim strTableName As String
Dim strSql As String
Dim dbs As DAO.Database
Dim rs As DAO.Recordset

strTableName = "MyTableCreatedOn" & Format(Now(), "mmddyy")
Set dbs = CurrentDb
strSlq = "CREATE TABLE " & Trim(strTableName) & " (FirstField TEXT,
SecondField TEXT)"
dbs.Execute (strSlq)
Set dbs = Nothing

End Sub
"Mo Ka" <m-********@earthlink.net> wrote in message
news:HA*****************@newsread3.news.atl.earthl ink.net...

Hi all,

Is there a function (or other method) to automatically append the system
date to the output tablename of a make table query such that the new one
does not overwrite an older table?.

I need these for stats but want to keep all previous tables created by
the same query and I do not want the user to have to enter the date the
table was created.

Output table name BASETABLENAME_[mmddyy]

Thanks in advance.

Maur

[Pieter Linden]

Mo Ka <m-********@earthlink.net> wrote in message news:<HA*****************@newsread3.news.atl.earth link.net>...

Hi all,

Is there a function (or other method) to automatically append the system
date to the output tablename of a make table query such that the new one
does not overwrite an older table?.

I need these for stats but want to keep all previous tables created by
the same query and I do not want the user to have to enter the date the
table was created.

Output table name BASETABLENAME_[mmddyy]

Thanks in advance.

Maur

Public Sub BackupTable(ByVal strTableToBackup As String)
Dim strNewName As String
strNewName = strTableToBackup & "_" & Format(Date, "mmddyyyy")
DoCmd.CopyObject "", strNewName, acTable, strTableToBackup

End Sub

[Mo Ka]

Thank you Pat and Pieter!

Mo

Mo Ka wrote:

Hi all,

Is there a function (or other method) to automatically append the system
date to the output tablename of a make table query such that the new one
does not overwrite an older table?.

I need these for stats but want to keep all previous tables created by
the same query and I do not want the user to have to enter the date the
table was created.

Output table name BASETABLENAME_[mmddyy]

Thanks in advance.

Maur

[Bob Quintal]

"Pat" <no*****@ihatespam.bum> wrote in
news:%b*******************@fe2.texas.rr.com:

This will give you a table named "MyTableCreatedOnMMDDYY" I
choked on getting Access to take an underscore or bracket in
the name table SQL. If there's a simple way to do this, I'd
be happy to read about it.

The square brackes are your friends. :)
examples
strTableName = _
"[MyTableCreatedOn_" & Format(Now(), "mmddyy") & "]"
or
strTableName = _
"[My Table Created On " & Format(Now(), "medium date" ) & "]"
even
strTableName = _
"[My Table, (" & Format(Now(), "mmddyy" & ")]"

I haven't succeeded with embedded [], however.

Bob Quintal


Sub NameTable()

Dim strTableName As String
Dim strSql As String
Dim dbs As DAO.Database
Dim rs As DAO.Recordset

strTableName = "MyTableCreatedOn" & Format(Now(), "mmddyy")
Set dbs = CurrentDb
strSlq = "CREATE TABLE " & Trim(strTableName) & " (FirstField
TEXT, SecondField TEXT)"
dbs.Execute (strSlq)
Set dbs = Nothing

End Sub
"Mo Ka" <m-********@earthlink.net> wrote in message
news:HA*****************@newsread3.news.atl.earthl ink.net...

Hi all,

Is there a function (or other method) to automatically append
the system date to the output tablename of a make table query
such that the new one does not overwrite an older table?.

I need these for stats but want to keep all previous tables
created by the same query and I do not want the user to have
to enter the date the table was created.

Output table name BASETABLENAME_[mmddyy]

Thanks in advance.

Maur