Insert Into Select From linked table

Question

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?

Andre · Answer 1 · 2015-08-18T11:50:09.437

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

score 0 · Answer 2 · answered Aug 17 '15 at 19:00

0

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

answered Aug 17 '15 at 19:00

Coding Duchess

6,445
20
113
209

That doesn't produce a valid string. Fixed it by putting single quotes instead. – David G Aug 17 '15 at 19:09

score 0 · Answer 3 · answered Aug 17 '15 at 19:11

0

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

answered Aug 17 '15 at 19:11

David G

2,315
1
24
39

Actually I'm surprised that the two double quotes were the problem - this should work. But using single quotes is definitely more readable. – Andre Aug 17 '15 at 19:19
Yes, the double quotes gave a statement that looked valid in the immediate window. Not sure what was wrong. – David G Aug 17 '15 at 19:23

Insert Into Select From linked table

3 Answers3

Linked