Macro Purpose:

1. Exports a table of data from Excel into a database, using an ADO connection to pass SQL strings.

Examples of where this function shines:

1. Works well for archiving data from Excel to an Access database.
2. Does not insert rows if all cells in the row are blank (avoids entering completely Null records in the database).
3. Code is robust enough to support varying amounts of columns or rows.
4. The entire database table structure does not have to be reproduced in Excel, providing that the Database Management System (DBMS) has default values, or can accept NULL values, for any omitted fields.
5. Uses transaction processing which will roll back all transactions if the full update is not successful.

Macro Weakness(es):

1. Care must be taken at the Excel end to ensure that all data will be valid for the following reasons: This procedure does not validate any of the Excel data against the table structure of the database.
2. Passes all values from Excel to the database as text strings, (not dates, values, etc...). This does not seem to cause a problem in Access, as it converts them to the proper format, but it is unknown if this is true of other DBMSs.
3. Not truly an issue with the code, but more with SQL in Access: you can not pass a variable into a field having a name which conflicts with a reserved name, even if fully qualified. i.e. A column heading of Date will cause an SQL INSERT statement to fail.

Versions Tested:
This function has been tested with Access & Excel 97, and Access & Excel 2003, and should also work with Access and Excel 2000 and 2002 (XP) without any modifications

Workbook Setup Requirements:

1. The structure of the workbook must be set up as shown in the following illustration

1. Please Note:
   
   1. Cells A3:F3 are named "tblHeadings"
   2. Cells A4:F11 are named "tblRecords"
2. The tables can be expanded or constricted, but the tblHeadings and tblRecords references must be changed to represent the appropriate rows for the procedure to work correctly

VBA Code Required:

1. A reference must be set to the Microsoft ActiveX Data Objects Libary
2. The code following should be placed in a standard code module:

Sub DB_Insert_via_ADOSQL()
'Author       : Ken Puls (www.excelguru.ca)
'Macro purpose: To add record to Access database using ADO and SQL
'NOTE:  Reference to Microsoft ActiveX Data Objects Libary required

    Dim cnt As New ADODB.Connection, _
            rst As New ADODB.Recordset, _
            dbPath As String, _
            tblName As String, _
            rngColHeads As Range, _
            rngTblRcds As Range, _
            colHead As String, _
            rcdDetail As String, _
            ch As Integer, _
            cl As Integer, _
            notNull As Boolean

    'Set the string to the path of your database as defined on the worksheet
    dbPath = ActiveSheet.Range("B1").Value
    tblName = ActiveSheet.Range("B2").Value
    Set rngColHeads = ActiveSheet.Range("tblHeadings")
    Set rngTblRcds = ActiveSheet.Range("tblRecords")

    'Concatenate a string with the names of the column headings
    colHead = " ("
    For ch = 1 To rngColHeads.Count
        colHead = colHead & rngColHeads.Columns(ch).Value
        Select Case ch
            Case Is = rngColHeads.Count
                colHead = colHead & ")"
            Case Else
                colHead = colHead & ","
        End Select
    Next ch

    'Open connection to the database
    cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & dbPath & ";"

    'Begin transaction processing
    On Error GoTo EndUpdate
    cnt.BeginTrans

    'Insert records into database from worksheet table
    For cl = 1 To rngTblRcds.Rows.Count

        'Assume record is completely Null, and open record string for concatenation
        notNull = False
        rcdDetail = "('"

        'Evaluate field in the record
        For ch = 1 To rngColHeads.Count
            Select Case rngTblRcds.Rows(cl).Columns(ch).Value
                    'if empty, append value of null to string
                Case Is = Empty
                    Select Case ch
                        Case Is = rngColHeads.Count
                            rcdDetail = Left(rcdDetail, Len(rcdDetail) - 1) & "NULL)"
                        Case Else
                            rcdDetail = Left(rcdDetail, Len(rcdDetail) - 1) & "NULL,'"
                    End Select

                    'if not empty, set notNull to true, and append value to string
                Case Else
                    notNull = True
                    Select Case ch
                        Case Is = rngColHeads.Count
                            rcdDetail = rcdDetail & rngTblRcds.Rows(cl).Columns(ch).Value & "')"
                        Case Else
                            rcdDetail = rcdDetail & rngTblRcds.Rows(cl).Columns(ch).Value & "','"
                    End Select
            End Select
        Next ch

        'If record consists of only Null values, do not insert it to table, otherwise
        'insert the record
        Select Case notNull
            Case Is = True
                rst.Open "INSERT INTO " & tblName & colHead & " VALUES " & rcdDetail, cnt
            Case Is = False
                'do not insert record
        End Select
    Next cl

EndUpdate:
    'Check if error was encounted
    If Err.Number <> 0 Then
        'Error encountered.  Rollback transaction and inform user
        On Error Resume Next
        cnt.RollbackTrans
        MsgBox "There was an error.  Update was not succesful!", vbCritical, "Error!"
    Else
        On Error Resume Next
        cnt.CommitTrans
    End If

    'Close the ADO objects
    cnt.Close
    Set rst = Nothing
    Set cnt = Nothing
    On Error GoTo 0
End Sub

How it works:

1. The database path, table name, column headings range and detail ranges are all assigned to variables for later use in the procedure
2. The table's column headings are all joined into a string to be used later in the procedure.
3. In the case of the example, the string is " (CustID,Type,DatePaid,DateStart,DateEnd,Amount)"
   
4. A connection to the database is established
5. For each row in the table, each field is evaluated, and joined into a string to be used in the procedure. If the field has a value, the value is added, otherwise the value of NULL is added. Three of the records from the example would be:
   

   Worksheet Row:	String Returned
   4	('A-01','Annual','09/04/04','09/04/04','09/03/05','1240')
   9	('B-03','prorate',NULL,'08/17/04','05/31/05','975')
   10	(NULL,NULL,NULL,NULL,NULL,NULL)

6. Each row is inserted into the database, one row at a time, unless it consists purely of Null values, in which case it is ignored
7. The connection to the database is closed

The End Result:

1. The following image shows the result of the procedure, where the Access table held no data (but did exist in the database) prior to the procedure being executed:

Adapting this to a DBMS other than Access:

1. In order to use this routine with a DBMS other than Microsoft Access, the Provider must be changed to match the DBMS that you want to use
   
   1. Specifically, this section of the above code:

                'Open connection to the database
                     cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                     "Data Source=" & dbPath & ";"
        

   2. needs to be updated to reflect the proper OLE Database Provider engine from Microsoft.Jet.

2. More information on a huge variety of OLE Database Providers can be found here.

Example Files:
A zipped file containing both an Access database and the Excel file are attached. You will need to update the file path in Excel file before running the example file.