Syntax Error: Insert Into statement in VBA

[Kimbertha]

Hello All:

It's been two years since I had my last serious Access challenge . . .

I am currently struggling with an Insert Into command in Visual Basic.

Here is the SQL from my query. This works just fine.

INSERT INTO [Data Summary] ( Store, Description, [Period 1], [Period 2], [Period 3], [Period 4], [Period 5], [Period 6], [Period 7], [Period 8], [Period 9], [Period 10], [Period 11], [Period 12], [Year to Date] )
SELECT "KS006" AS Store, KS006.F1 AS Description, KS006.[Period 1], KS006.[Period 2], KS006.[Period 3], KS006.[Period 4], KS006.[Period 5], KS006.[Period 6], KS006.[Period 7], KS006.[Period 8], KS006.[Period 9], KS006.[Period 10], KS006.[Period 111] AS [Period 11], KS006.[Period 12], KS006.[Year to Date]
FROM KS006
WHERE (((KS006.[Year to Date]) Is Not Null));


Here is what I built using the Macro Tools in Access 2010. This returns the "Syntax error in Insert Into" error message.


DoCmd.RunSQL "INSERT INTO [Data Summary] ( Store, Description, [Period 1], [Period 2], [Period 3], [Period 4], [Period 5], [Period 6], [Period 7], [Period 8], [Period 9], [Period 10], [Period 11], [Period 12], [Year to Date] )"
DoCmd.RunSQL "SELECT ""KS006"" AS Store, KS006.F1 AS Description, KS006.[Period 1], KS006.[Period 2], KS006.[Period 3], KS006.[Period 4], KS006.[Period 5], KS006.[Period 6], KS006.[Period 7], KS006.[Period 8], KS006.[Period 9], KS006.[Period 10], KS006.[Period 111] AS [Period 11], KS006.[Period 12], KS006.[Year to Date]"
DoCmd.RunSQL "FROM KS006"
DoCmd.RunSQL "WHERE (((KS006.[Year to Date]) Is Not Null))", -1




I've been poking around with the syntax and cannot get it right!

Regards,

Kimberly

[pbaldy]

In the interests of learning how to fish, here's a good method to use to debug the SQL:

http://www.baldyweb.com/ImmediateWindow.htm

I see a couple of issues offhand. You have to build a single string variable and execute it. You're executing 3 incomplete strings.