DoCmd: Copy Table

[Joe]

Hi,

I trying to copy a table and stamp a date at the end. I have the following
code that I been working on but I get a Compile error. Please help

Private Sub CopyTable()

Dim strTableName As String
Dim strDate As String

strDate = Format(Date - 1, "mmddyy")
strTableName = "SA DAILY PORTFOLIO"

DoCmd.CopyObject (strTableName &""& strDate,acTable,strTableName)

End Sub

 

[fredg]

Hi,

I trying to copy a table and stamp a date at the end. I have the following
code that I been working on but I get a Compile error. Please help

Private Sub CopyTable()

Dim strTableName As String
Dim strDate As String

strDate = Format(Date - 1, "mmddyy")
strTableName = "SA DAILY PORTFOLIO"

DoCmd.CopyObject (strTableName &""& strDate,acTable,strTableName)

End Sub

It works OK for me.
Just as a matter of curiosity, what is the purpose of the &""& in your
code?
strTableName & strDate
is all you should need to get "SA DAILY PORTFOLIO062608"

 

[Joe]

Hi Fred...

I wanted to add a space after the table.

SA DAILY PORTFOLIO 062708

I still get the compile error. I'm not sure what i'm doing wrong.

 

[fredg]

Hi Fred...

I wanted to add a space after the table.

SA DAILY PORTFOLIO 062708

I still get the compile error. I'm not sure what i'm doing wrong.

I should have caught this before.
You are missing the first argument of the CopyObject method.
It asks for the Destination Database into which you wish to place the
new table.
As you are using the same database the code is in all you need do is
place a comma there as place holder. And also no parenthesis are
needed.
V
DoCmd.CopyObject , strTableName &" "& strDate, acTable, strTableName

 

[Klatuu]

The problem is the first argument of the CopyObject method is the database
name. Since you are not including it, you need to do one of two things.
Either put a , before the other arguments:
DoCmd.CopyObject (NewName:=strTableName & " " & strDate,
SourceObjectType:=acTable, SourceObjectName:=strTableName)

Also, you have to have a space on either side of the concatenation character &
And, you were not including a space, but a null string.
Here is how I would do it:

Private Sub CopyTable()
Dim strTableName As String
Dim strNewTable As String

strTableName = "SA DAILY PORTFOLIO"
strNewTable = strTableName & Format(DateAdd("d", -1, Date), " mmddyy")
DoCmd.CopyObject (, strNewTable, acTable, strTableName)

End Sub

Now, two points.
1. Spaces in names are bad
2. Multiple identical tables is really bad. There need be only one table
named tblSaDailyPortFolio with a field in it for the date. Doing it any
other way only cause problems and makes building an application more
difficult.