- Exports a table of data from Excel into a database, using an ADO connection to pass SQL strings.
Examples of where this function shines:
- Works well for archiving data from Excel to an Access database.
- Does not insert rows if all cells in the row are blank (avoids entering completely Null records in the database).
- Code is robust enough to support varying amounts of columns or rows.
- 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.
- Uses transaction processing which will roll back all transactions if the full update is not successful.
- 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.
- 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.
- 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.
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. To use it with an Access 2007 or higher format (*.accdb files), you must update the connection strings (at the very top of the code -- just uncomment the *.accdb line and remove the *.mdb line). If you wish to use another Database Management System (DBMS), see "Adapting this to a DBMS other than Access" below.
Workbook Setup Requirements:
- The structure of the workbook must be set up as shown in the following illustration
- Please Note:
- Cells A3:F3 are named "tblHeadings"
- Cells A4:F11 are named "tblRecords"
- 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:
- A reference must be set to the Microsoft ActiveX Data Objects Libary
- 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, _ sConnect As String '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") 'Set the database connection string here 'Private sConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & dbPath & "';" 'For use with *.accdb files sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";" 'For use with *.mdb files '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 sConnect '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:
- The database path, table name, column headings range and detail ranges are all assigned to variables for later use in the procedure
- The table's column headings are all joined into a string to be used later in the procedure.
- In the case of the example, the string is " (CustID,Type,DatePaid,DateStart,DateEnd,Amount)"
- A connection to the database is established
- 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:
- 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
- The connection to the database is closed
The End Result:
- 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:
- 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
- Specifically, this section of the above code:
'Open connection to the database cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & dbPath & ";"
- needs to be updated to reflect the proper OLE Database Provider engine from Microsoft.Jet.
- Specifically, this section of the above code:
- More information on a huge variety of OLE Database Providers can be found here.
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.
This article was also published as the January 2007 Office Newsletter at Cimaware.com, makers of ExcelFIX. (Unfortunately the history of these newsletters is no longer available on their site.)
A portugese translation of this article can also be found at MS Office Gurus - Brazil.