INSERT records using SQL

Here is a demonstration of how to update a closed workbook with records from the active workbook.

Download these two compressed files.

Here is the code contained in InsertRecordsIntoClosedExcelWorkbook.Xls

Sub InsertValuesIntoClosedExcelWorkbook()
'' ***************************************************************************
'' Purpose  : Inserting records into a closed workbook
'' Written  : 10-Jun-2005 by Andy Wiggins, Byg Software Limited
''
Dim lStr_Conn As String
Dim lStr_Sql As String

    lStr_Conn = ""
    lStr_Conn = lStr_Conn & "ODBC;"
    lStr_Conn = lStr_Conn & "DSN=Excel Files;"
    lStr_Conn = lStr_Conn & "DBQ=C:\Demo\TARGET.xls;"
    lStr_Conn = lStr_Conn & "DefaultDir=C:\Demo;"
    lStr_Conn = lStr_Conn & "DriverId=790;"
    lStr_Conn = lStr_Conn & "MaxBufferSize=2048;"
    lStr_Conn = lStr_Conn & "PageTimeout=5;"

    '' Method 1
''    lStr_Sql = ""
''    lStr_Sql = lStr_Sql & " INSERT INTO LocalTable"
''    lStr_Sql = lStr_Sql & " VALUES (555,5)"

    '' Method 2
    lStr_Sql = ""
    lStr_Sql = lStr_Sql & " INSERT INTO LocalTable"
    lStr_Sql = lStr_Sql & " SELECT *"
    lStr_Sql = lStr_Sql & " FROM `" & ThisWorkbook.FullName & "`.MyTable"

    With ThisWorkbook
        .Activate
        .Sheets("Source").Select
    End With

    With ActiveSheet.QueryTables.Add(Connection:=lStr_Conn, Destination:=Range("A1"))
        .CommandText = lStr_Sql
        .Name = "INSERT query demo"
        .Refresh
    End With

End Sub

There are two demonstrations shown here. The first (Method 1) has been commented out. It shows how to INSERT one new record into the target table in the closed workbook.

The second demonstration (Method 2) shows how to INSERT multiple records into the target table in the closed workbook.