Excel VBA Access SQL Statement

Question

I'm trying to add data into an access DB from Excel, I wan to add many values in one go.

The issue is that Excel reports that the SQL is missing the trailing semi-colon (;) Here is the extract of the code with the SQL that fails.

This is the built SQL Statment

INSERT INTO ThisTable (FirstName, LastName) VALUES ('John','Smith'),('Ringo','Star'),('Chris','Jones');

' --- Test Access Creation --- '
Sub Example2()
    'the path to create the new access database
    Dim strPath As String
    'an DAO object
    Dim db As DAO.Database
    
    Dim command As String
    Let command = "INSERT INTO ThisTable (FirstName, LastName) " & _
                "VALUES " & _
                "('John','Smith')," & _
                "('Ringo','Star')," & _
                "('Chris','Jones');"
    
    Debug.Print command
    
    strPath = "A:\NewDB"
    Set db = DAO.OpenDatabase(strPath)
    'Set db = DAO.CreateDatabase(strPath, DAO.dbLangGeneral)
    'db.Execute "CREATE TABLE ThisTable " _
            & "(FirstName CHAR, LastName CHAR);"
            
    db.Execute command
                
    
    Dim rst As DAO.Recordset
    Set rst = db.OpenRecordset("Select * FROM ThisTable")
    
     'Begin row processing
    Do While Not rst.EOF
        
        Debug.Print Trim(rst.Fields(0)) & " " & Trim(rst.Fields(1))
        rst.MoveNext
       
    Loop
    
    db.Close
    
End Sub

I may be wrong, but I don't think MS Access supports VALUES. You have to use multiple UNIONs instead. — Jonathan Willcock
– Jonathan Willcock, Commented Jun 28, 2021 at 13:53
MS Access does support VALUES clause but only for one record. @JonathanWillcock, could write up an answer showing the UNION approach. — June7
– June7, Commented Jun 28, 2021 at 18:07

June7 · Accepted Answer · 2021-06-28 19:54:30Z

2

Use UNION query to add multiple records with literal strings in one INSERT action.

    Let command = "INSERT INTO ThisTable (FirstName, LastName) " & _
                  "SELECT FirstName, LastName FROM (" & _
                      "SELECT 'John' AS FirstName, 'Smith' AS LastName FROM ThisTable "  & _
                      "UNION SELECT 'Ringo', 'Star' FROM ThisTable " & _
                      "UNION SELECT 'Chris', 'Jones' FROM ThisTable)" & _

answered Jun 28, 2021 at 19:54

June7

21.6k8 gold badges28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jonathan Willcock Over a year ago

Do you actually need the FROM ThisTable? It is so long since I used MS Access, but I thought you can simply do SELECT 'x', 'y'

June7 Over a year ago

I tested without and the UNION seems to require a table or query reference.

Collectives™ on Stack Overflow

Excel VBA Access SQL Statement

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related