By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,462 Members | 2,394 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,462 IT Pros & Developers. It's quick & easy.

Function to Append DATE to output tablename for Make Table Query

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
"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.