My previous article explored using plain SQL to connect and work with other Access databases:

This time, I thought I’d show you how you can create SELECT statements that can bind directly to an Excel Workbook to work with the data without the need of Linked Table or advanced VBA coding.

Linked Table Limitation

Yes, it is true, you used to be able to directly edit Excel documents via linked tables, but that ability was lost/removed in Access 2003 and 2002:

This is why turning towards using a direct SQL Query can be so valuable as it allows you to still edit a Workbook directly without the need of VBA automation or some other workaround.

Basic SELECT Statement

Below is the basic syntax to perform a SELECT query on an Excel Workbook:

SELECT *
FROM [Excel 12.0 Xml;HDR=NO;IMEX=0;Database=C:\YourFullPath\YourWorkBook.xlsx].[WorkSheetName$];

Practical Usage Example

Here’s a concrete example:

SELECT *
FROM [Excel 12.0 Xml;HDR=NO;IMEX=0;Database=C:\Demos\Contacts.xlsx].[Sheet1$];

Retrieving a Specific Cell or Range

What if, instead of getting an entire sheet, you only wanted to retrieve a specific range or cell.  Well, it can be done quite easily!  You simply need to add a range definition to the sheet name, as illustrated below:

SELECT * 
FROM [Excel 12.0 Xml;HDR=NO;IMEX=0;Database=C:\YourFullPath\YourWorkBook.xlsx].[WorkSheetName$XX:XX];

Return a Specific Range

SELECT *
FROM [Excel 12.0 Xml;HDR=NO;IMEX=0;Database=C:\Demos\Contacts.xlsx].[Sheet1$C5:G17];

Return a Specific Cell

The same principle is required for a single cell, in that you still have to supply a range!  So you would do:

SELECT *
FROM [Excel 12.0 Xml;HDR=NO;IMEX=0;Database=C:\Demos\Contacts.xlsx].[Sheet1$C5:C5];

Applying a Where Clause

Yes, you can apply where clauses here!

SELECT * 
FROM [Excel 12.0 Xml;HDR=YES;IMEX=0;Database=C:\Demos\Contacts.xlsx].[Sheet1$]
WHERE [ID] = 13;

Basic Append Query

Below is the basic syntax to perform an UPDATE query on an Excel Workbook:

INSERT INTO [Excel 12.0 Xml;HDR=YES;IMEX=0;Database=C:\YourFullPath\YourWorkBook.xlsx].[WorkSheetName$] 
('Field1Name', 'Field2Name', 'Field3Name', ...)
VALUES (1, 'SomeValue', 'SomeValue');

Practical Usage Example

Here’s a simple example

INSERT INTO [Excel 12.0 Xml;HDR=NO;IMEX=0;Database=C:\Demos\Contacts.xlsx].[Sheet1$]
( Company, [Last Name], [First Name] )
VALUES ('McDonalds', 'Kempczinski', 'Chris');

Basic Update Query

Below is the basic syntax to perform an UPDATE query on an Excel Workbook:

UPDATE [Excel 12.0 Xml;HDR=YES;IMEX=0;Database=C:\YourFullPath\YourWorkBook.xlsx].[WorkSheetName$] 
SET [FieldName] = 'Value'
WHERE [FieldName] = 'Condition';

Practical Usage Example

Here’s a simple example

UPDATE [Excel 12.0 Xml;HDR=YES;IMEX=0;Database=C:\Demos\Contacts.xlsx].[Sheet1$] 
SET [FirstName] = 'Christopher'
WHERE [FirstName] = 'Chris';

Basic DELETE Query

This operation cannot be performed and you will receive an error like:

Explanation of The Parameters

In case you were wondering, IMEX refers to ‘Import Export mode’ and has 3 possible values: 0,1 or 2.
HDR refers to Header and has 2 possible values: Yes or No.

A Workaround For Linked Tables

As you have seen above, to make queries directly editable, we are controlling the connection used by specifying IMEX=0.

Well, in reality we can do the same thing to a Linked Table to an Excel Worksheet.

The same issue exists with Excel Linked tables.  If you examine the connection string you will get back something like:

Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=C:\Demos\Contacts.xlsx

where, by default, Access creates the Linked Tables with an IMEX=2 which doesn’t allow editing.  Thus, the workaround, is to change the IMEX value of the Linked table to 0.  For this I created a very simple procedure:

'---------------------------------------------------------------------------------------
' Procedure : MakeExcelLinkedTableEditable
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Update Excel Linked Tables to make them Editable
'               Change IMEX to 0
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding  -> none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sTable    : Name of the Excel Linked Table to make editable
'
' Usage:
' ~~~~~~
' Call MakeExcelLinkedTableEditable("Sheet1")
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2022-02-26              Initial Public Release
'---------------------------------------------------------------------------------------
Sub MakeExcelLinkedTableEditable(ByVal sTable As String)
    On Error GoTo Error_Handler
    Dim db                    As DAO.Database
    Dim tdf                   As DAO.TableDef

    Set db = CurrentDb
    Set tdf = db.TableDefs(sTable)
    'Only IMEX=0 is editable!
    tdf.Connect = Replace(Replace(tdf.Connect, "IMEX=1", "IMEX=0"), "IMEX=2", "IMEX=0")
    tdf.RefreshLink

Error_Handler_Exit:
    On Error Resume Next
    If Not tdf Is Nothing Then Set tdf = Nothing
    If Not db Is Nothing Then Set db = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: MakeExcelLinkedTableEditable" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub

So now, you can create a Linked Table to an Excel Worksheet, run this procedure, and it becomes editable.  You can create, queries, forms and be able to work with the data.

Connection Caching

One quick comment regarding working with Excel in this manner.  While playing around with all of this, trying to figure out what worked and didn’t, I found out the hard way that Microsoft Access does some type of connection caching.  So, I spent some serious time with valid code that wouldn’t work because Access continued to use an old connection I had previously tried out.  Moral of the story, learn from my mistake, close and restart Access when you modify the connection string!

A Few Resources on the Subject

I’d love to provide links to Official documentation on all of this, I have searched, yet found nothing. Lots and lots of posts, but nothing from Microsoft explicitly covering IMEX, HDR. You can find mention here and there, but never a complete authoritative official article, here are the values and what they do. I also found numerous discussions pointing to now long gone MS KB articles.

Fellow MVP Karl Donaubauer just made me aware of his post on the subject, so check that out.  Heck, I never knew he had a website with such information! I’ve provided a translated link as his site is natively German.