MakeTable Query with Variable user defined Name

[Dinzdale40]

I need to figure out how to have a make table query run that asks the user for the name. It is making a table in another database for a different purpose and so it important that the table name change every month. I am trying to keep it so that if the table from the first month doesn't get used it will not be overwritten next month.



I've done this kind of thing before but I had the make table queries using a set name creating an excel file that was later taken via vba and formatted/resaved to my variable user defined file name.

Any help would be most appreciated. I was thinking there was some way to put the sql into VB, but am unsure of how to use the variables if i do that.

Here is an example of my sql for the make table query.

SELECT [Current Month].ID, [Current Month].[Period Ending], [Current Month].[Prj CC], [Current Month].SAC, [Current Month].[Project #], [Current Month].[CIP #], [Current Month].[Project Description], [Current Month].[Task Phase], [Current Month].[Task Name], [Current Month].[Total Hrs], [Current Month].[EST Cmp Date], [Current Month].[Bus Unit] INTO 2011_02 IN 'F:\Ref\Test\TargetDB.accdb'FROM [Current Month];

[Dinzdale40]

This worked. Figure I would post it here if anyone else needed it.

 
Private Sub Command0_Click()
 
Dim strSQL As String
Dim varYearMonth As Variant
 
varYearMonth = InputBox("Enter Table Name YYYY_MM")
If varYearMonth = "" Then Exit Sub
 
strSQL = "SELECT [Current Month].ID, [Current Month].[Period Ending], [Current Month].[Prj CC], [Current Month].SAC, [Current Month].[Project #], [Current Month].[CIP #], [Current Month].[Project Description], [Current Month].[Emp CC], [Current Month].[Task Phase], [Current Month].[Task Name], [Current Month].[Total Hrs], [Current Month].[EST Cmp Date], [Current Month].[Bus Unit], [Current Month].Discretionary, [Current Month].Month, [Current Month].[Project Class] INTO " & varYearMonth & " IN 'F:\Ref\Test\TargetDB.accdb' FROM [Current Month];"

DoCmd.RunSQL strSQL
 
End Sub