ADO VBA INSERT INTO not working

I've got the following function that returns a syntax error message.  What's up with it?
Private Sub MakeHolidaysLocal()
'Make the SQL Server Data local
'(Commitment_Holidays on Server, Commitment_Holidays_Local is a local Access table)
  Dim rstHolidays As adodb.Recordset, rstHolidaysLocal
  
  CurrentProject.Connection.Execute "DELETE * FROM Commitment_Holidays_Local"
  CurrentProject.Connection.Execute "INSERT INTO Commitment_Holidays_Local" _
                                    & "SELECT Date FROM [Commitment_Holidays];"
End Sub

Open in new window

schmir1Asked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
Also, does Commitment_Holidays_Local have only ONE field? If not, your sub-select won't work ... you'd have to do something like this:

CurrentProject.Connection.Execute "INSERT INTO Commitment_Holidays_Local(FieldToInsertInto)" _
                                    & " SELECT [Date] FROM [Commitment_Holidays];"
0
 
Rey Obrero (Capricorn1)Commented:
change this

  CurrentProject.Connection.Execute "INSERT INTO Commitment_Holidays_Local" _
                                    & "SELECT Date FROM [Commitment_Holidays];"


to

  CurrentProject.Connection.Execute "INSERT INTO Commitment_Holidays_Local" _
                                    & " SELECT [Date] FROM [Commitment_Holidays];"
0
 
schmir1Author Commented:
I still get a syntax error with this:
  CurrentProject.Connection.Execute "INSERT INTO Commitment_Holidays_Local" _
                                    & "SELECT [Date] FROM [Commitment_Holidays];"

This one worked great:
  CurrentProject.Connection.Execute "INSERT INTO Commitment_Holidays_Local([Date])" _
                                    & "SELECT [Date] FROM [Commitment_Holidays];"

Thanks for your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.