Trouble w/ DoCmd.TransferText

[jrcase45]

I am having difficulty getting my VBA to update a table with a .csv file that is updated overnight. The csv filename changes daily to reflect the current date. I'm using strings in the DoCmd.TransferText command to reflect the table name and file path. The csv file i am importing is comma delim. My code is attached below. Any help on this would be appreciated.

Thanks.

Private Sub Command0_Click()
Dim Path As String, FileDate As String, D As String, M As String
Dim tblName As String

DoCmd.OpenQuery "POINT DELETE"
'Delete Contents From POINT table

tblName = "POINT"
Path = "P:\GlobalCreditResearch\Daily Data Files\POINT\"
D = Right(0 & Day(Date), 2)
M = Right(0 & Month(Date), 2)
'Puts month and date into 2 character formats

FileDate = Year(Date) & M & D & "_MR2_CASEJO2_TickerReturn.csv"
'Adjusts filename to reflect current date

Path = Path & FileDate
'Range1 = Year(Date) & M & D & "_MR2_CASEJO2_TickerRetu!B9:G1476"

DoCmd.TransferText acImportDelim, , tblName, Path, "False"

End Sub

 

[modest]

Instead of storing the month day and year in separate variables, for something like this you can use one function call.

E.g,
Format(Now,"YYYYMMDD") or Format(Now,"YYMMDD"), depending on the format you want.