CODEWITHSUNDEEP
ms-accessvba
David G
David G

Reputation: 2347

Insert Into Select From linked table

I am trying something that has been done hundreds of times - except my code does not compile. Using this as a reference How to do INSERT into a table records extracted from another table I came up with the following sub, that is supposed to clear a table and refill it from another linked table (The idea is to liberate the linked table so it doesn't have a .laccdg file in its name for long)

Private Sub Form_Open(Cancel As Integer)
    Dim rsDocs As Recordset
    Dim sqlQuery As String

    DoCmd.RunSQL "DELETE * FROM [Docs]"

    sqlQuery = "INSERT INTO [Docs] (Numero, Description, [ID Symix], Groupe, [ID Sami])" & _
                " SELECT [Unité] & "" "" & [Numéro Document] AS Numero, Description, [ID Symix], [Groupe Source], [ID Doc Sami]" & _
                " FROM [Documents]"
        Debug.Print sqlQuery
    Set rsDocs = CurrentDb.OpenRecordset(sqlQuery)
    CurrentDb.Execute sqlQuery
    rsDocs.Close
    Set rsDocs = Nothing
End Sub

I get an error on the execute line. The sql statement is wrong. Can someone see where it falters? Is the use of "" "" to join two fields together acceptable in this situation?

Also, clearing the table prompts a message asking if i am sure i want to do this. Will setting DisplayAlerts to False have negative consequences? Or should it be harmless if I put it back to True right after?

Upvotes: 1

Views: 824

Answers (3)

Andre
Andre

Reputation: 27634

A general hint for debugging queries you design in VBA:

  • Do a Debug.Print of the SQL (you already have that)
  • Create a new query, close "Add tables"
  • switch to SQL view and paste your sql from the debug window
  • either execute the sql, or switch to design view.

Usually you will get a more specific error message from the query editor than what you get from VBA.

DisplayAlerts in Access is: DoCmd.SetWarnings True/False
But it is better to avoid it by using DB.Execute instead of DoCmd.RunSQL

Upvotes: 2

David G
David G

Reputation: 2347

Instead of 2 sets of double quotes, which didn't work, or 1 set of double quotes, which don't produce a valid string, use 1 set of single quotes:

sqlQuery = "INSERT INTO [Docs] (Numero, Description, [ID Symix], Groupe, [ID Sami])" & _
            " SELECT [Unité] & ' ' & [Numéro Document] AS Numero, Description, [ID Symix], [Groupe Source], [ID Doc Sami]" & _
            " FROM [Documents]"

Upvotes: 0

Coding Duchess
Coding Duchess

Reputation: 6899

No need for extra set of double quotes

"INSERT INTO [Docs] (Numero, Description, [ID Symix], Groupe, [ID Sami])" & _
            " SELECT [Unité] & " " & [Numéro Document] AS Numero, Description, [ID Symix], [Groupe Source], [ID Doc Sami]" & _
            " FROM [Documents]"

Upvotes: 0

Related Questions