Opening or Linking Text or Excel as Data Sources in Microsoft Access

Access is a phenomenal tool for working with many data sources. You may have long known that you can easily import or export data in a wide array of formats. However, we had a scenario where we needed to process data from external data sources. The processing runs frequently so importing data would bloat the front-end file quickly. Furthermore, in this situation, creating a 2nd copy of Access file as a temporary database to avoid the bloating was not an viable option. What choices did we have? That’s where we noticed a dearth of information regarding opening or linking to non-relational data sources. So, let’s do a deep dive on this subject.

This blog series aims to fill a gaping gap in Microsoft’s documentation on opening or linking to text or Excel as the data source in Access applications. Connectionstrings.com and other sites provides some breadcrumbs. You also can usually discover some of the behavior by using Access’ wizards and then examining the objects after. However, seconding-guessing is not terribly useful. Hence, this series of articles will discuss about how to use Excel and Text files as data sources without necessarily importing or exporting the data.

Please note that the series would not be possible without the help of Dan Haught and Jim Ferguson, who co-authored Microsoft Jet Database Engine Programmer’s Guide. Even though the book is long ago out of print, it is still a valuable book if you work with DAO a lot.

Access and non-relational data sources

Access has always supported using Indexed Sequential Access Method (ISAM) as a potential data source, which enables it to import, link or export to non-relational data sources such as text files or Excel spreadsheets and even other file formats such as Exchange, dBASE or FoxPro. DAO makes it possible because it has the ISAM drivers that can work with those formats. However, I’m going to focus on text files and Excel spreadsheets as they are more common sources where we need Access to work with. If all you’ve ever done is import data using External Data tab via Access, it can seem magical but in reality, all of Access’ features is available to us as programmers at the level of DAO.

So let’s forget that wizards exist. Let’s forget about the shiny icons on the ribbon. Let’s forget that there’s linked tables. Let’s just use VBA code to work with data sources, out of the thin air. Can we? Absolutely. First, we will look at the difference between linking and opening a data source.

Linking vs. Opening

To help with the terminology, we need to make a distinction between linking vs. opening. Linking means that we create a linked table which is now an Access “table” that we can use just like any other Access table. As such, it’s a permanent object defined within the Access application. In contrast, opening is when we access the data source directly by using DAO’s OpenDatabase method. By its nature, opening a data source will usually done via VBA code as you will see shortly.

Setting up the samples and code

Because the series is about understanding the nitty-gritty details of how Access interacts with those data sources, we will first focus on opening instead of linking here. So, how do we open an Excel spreadsheet? Surely, it’s not a database? Let’s set up a minimum example to start with. In a folder, C:\Links, we’ll create an Excel spreadsheet and an Access database file, named Products.xlsx and Sample.accdb, respectively:

The Products.xlsx is a simple spreadsheet with only one sheet with this contents:

With this, let’s add VBA code in the Sample.accdb. In a standard module:

Public Sub OpenExcel()
    Dim db As DAO.Database
    
    Set db = DBEngine.OpenDatabase(vbNullString, False, False, "Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=C:\Links\Products.xlsx")
    
    Dim tdf As DAO.TableDef
    For Each tdf In db.TableDefs
        Debug.Print tdf.Name
        
        Dim fld As DAO.Field
        For Each fld In tdf.Fields
            Debug.Print fld.Name,
        Next
        Debug.Print
        
        Dim rs As DAO.Recordset
        Set rs = tdf.OpenRecordset
        Do Until rs.EOF
            For Each fld In rs.Fields
                Debug.Print fld.Value,
            Next
            Debug.Print
            rs.MoveNext
        Loop
    Next
End Sub

If we run the code, we should get the following output in our immediate window:

Products      Count         
Apples         3            
Bananas        5            
Figs           8            

So even though Excel isn’t a “database” (and if you’re using Excel as a database, shame on you 😉 ), we still can pretend it is a “database” and iterate through its “tables” and “records” using familiar DAO objects. All the magic comes from this connection string:

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

But it doesn’t have to be magical. The series will dissect the parts of the connection strings for Excel and text files drivers. So let’s start with comparing to ODBC connection string which you perhaps are more familiar with.

Data Source Types

If you’ve linked to ODBC data sources, you probably noticed that ODBC connection strings usually look something like this:

ODBC;DRIVER=...;SERVER=...;DATABASE=...;

Wondered why it always started with ODBC? That first part is what DAO uses to identify the type of source. However, because Excel isn’t an ODBC database, we have to use Excel 12.0 Xml to indicate that DAO needs to use a particular Excel driver. The rest of connection string syntax then is influenced by which driver we are using in the first part of the connection string.

Here’s a partial list of possible data source types that MS Access will recognize:

1. Excel 8.0: 97-2003 xls files
2. Excel 12.0: xlsb files
3. Excel 12.0 Xml: xlsx files
4. Text: Any text files

There are more data sources, including SharePoint, Exchange, or FoxPro, but I’m not going to cover those. Even so, it demonstrates that Access is capable of working with those sources just like a database and that can enable us to use DAO to read the contents. Note that when we use non-relational data sources, not all operations may be fully supported. For instance, you may not be allowed to edit an existing row even though you can insert a row.

However, one challenge with opening or linking to a data source means that you can’t just import the data in, then edit the schema after to suit your needs. Therefore, you may find that the default schema you get while linking won’t be what you want. For that reason, an in-depth understanding of the connection string will be important. Therefore, you will learn more about the details of the connection strings for both Excel and text files in next few articles.

Conclusions

You saw that as an alternative to importing the external data, we can choose to link or even open an external data source using DAO. The ability to link or open an external data source is vastly underused. However, the ability to link or open should not be discounted as this can open up new scenarios such as avoiding the bloating as an effect of importing, especially in a locked down environment. In the next article, I will discuss the different parameters used by the Excel driver to construct a valid connection string. In the following articles, I will look at the text file driver and the connection parameters. In the final article, we’ll put it all together.