Posted on 2008-11-18
Last Modified: 2013-11-27
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

Question by:schmir1
    LVL 119

    Expert Comment

    by:Rey Obrero
    change this

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


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

    Accepted Solution

    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];"

    Author Comment

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now