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 "MyTableCreated OnMMDDYY" 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 = "MyTableCreated On" & Format(Now(), "mmddyy")
Set dbs = CurrentDb
strSlq = "CREATE TABLE " & Trim(strTableNa me) & " (FirstField TEXT,
SecondField TEXT)"
dbs.Execute (strSlq)
Set dbs = Nothing

End Sub
"Mo Ka" <m-********@earthl ink.net> wrote in message
news:HA******** *********@newsr ead3.news.atl.e arthlink.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-********@earthl ink.net> wrote in message news:<HA******* **********@news read3.news.atl. earthlink.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(ByV al strTableToBacku p As String)
Dim strNewName As String
strNewName = strTableToBacku p & "_" & Format(Date, "mmddyyyy")
DoCmd.CopyObjec t "", strNewName, acTable, strTableToBacku p

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*****@ihates pam.bum> wrote in
news:%b******** ***********@fe2 .texas.rr.com:

This will give you a table named "MyTableCreated OnMMDDYY" 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 = _
"[MyTableCreatedO n_" & 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 = "MyTableCreated On" & Format(Now(), "mmddyy")
Set dbs = CurrentDb
strSlq = "CREATE TABLE " & Trim(strTableNa me) & " (FirstField
TEXT, SecondField TEXT)"
dbs.Execute (strSlq)
Set dbs = Nothing

End Sub
"Mo Ka" <m-********@earthl ink.net> wrote in message
news:HA******** *********@newsr ead3.news.atl.e arthlink.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