469,616 Members | 1,599 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,616 developers. It's quick & easy.

Function to Append DATE to output tablename for Make Table Query

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
Nov 12 '05 #1
4 8024
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

Nov 12 '05 #2
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
Nov 12 '05 #3
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

Nov 12 '05 #4
"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



Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Bob | last post: by
2 posts views Thread by John | last post: by
6 posts views Thread by Mark | last post: by
1 post views Thread by David Barger | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.