Automatic Append Query not working

[robertsa123]

Hi all,

If someone could help me with my code i'd be very grateful. I am trying to run a loop that appends weekly data from an import table into a new master table. I would like to first append weeks 1-9 and then a seperate bit of code to add weeks 10-52.

The problem is that the macro only adds week 9's data and nothing else. It seemed to work last week but doesn't now. The data is definately in the import table so there's something wrong with the code.

If you could have a look and paste the correct code that'd be great.

Thanks very much!


My code:




Sub AutoAppend()

'This macro automatically appends data from the imported table into the new master table
Dim i As Integer
Dim mySQL As String

'First 9 weeks
For i = 1 To 9
mySQL = "INSERT INTO [Master Data] ( METRIC_GROUP, METRIC_NAME, ACTUAL_TARGET , METRIC_UNITS, METRIC_VALUE, YEAR_WEEK, AREA, SITE )"
mySQL = mySQL + " SELECT [2009 Import Table].[METRIC_GROUP], [2009 Import Table].[METRIC_NAME],"
mySQL = mySQL + " [2009 Import Table].[ACTUAL_TARGET],[2009 Import Table].[METRIC_UNITS],"
mySQL = mySQL + " [2009 Import Table].[2009_Wk0" & i & "] AS [METRIC_VALUE],"
mySQL = mySQL + "'2009_Wk0" & i & "' AS [YEAR_WEEK],"
mySQL = mySQL + "'Sinter Plant' AS AREA, 'Port Talbot' AS SITE"
mySQL = mySQL + " FROM [2009 Import Table];"
Next i

Debug.Print i, mySQL
'DoCmd.RunSQL mySQL
'DoCmd.SetWarnings False

End Sub

[SoftwareMatt]

At the moment the run parts is commented out (I take it you have doien that to devug) but you need to move it into the loop as follows:

'This macro automatically appends data from the imported table into the new master table
Dim i As Integer
Dim mySQL As String

'First 9 weeks
For i = 1 To 9
mySQL = "INSERT INTO [Master Data] ( METRIC_GROUP, METRIC_NAME, ACTUAL_TARGET , METRIC_UNITS, METRIC_VALUE, YEAR_WEEK, AREA, SITE )"
mySQL = mySQL + " SELECT [2009 Import Table].[METRIC_GROUP], [2009 Import Table].[METRIC_NAME],"
mySQL = mySQL + " [2009 Import Table].[ACTUAL_TARGET],[2009 Import Table].[METRIC_UNITS],"
mySQL = mySQL + " [2009 Import Table].[2009_Wk0" & i & "] AS [METRIC_VALUE],"
mySQL = mySQL + "'2009_Wk0" & i & "' AS [YEAR_WEEK],"
mySQL = mySQL + "'Sinter Plant' AS AREA, 'Port Talbot' AS SITE"
mySQL = mySQL + " FROM [2009 Import Table];"

DoCmd.RunSQL mySQL

Next i

DoCmd.SetWarnings False