Ad-hoc Connection Strings and Heterogeneous Queries for MS Access

Heterogeneous queries is the reason why connection strings, especially ad-hoc connection string are important. In previous articles of the series, you saw how you could customize the connection parameters for connecting to Excel and text files. In the case of the text files, you can also describe the schema of the text file’s structure using either schema.ini or saved specifications. In the first article, you also learned the difference between linking and opening a data source.

Heterogeneous queries instead of VBA code

You saw in the previous articles sample code of opening such data source using the DAO’s OpenDatabase method.

Set db = DBEngine.OpenDatabase(vbNullString, False, False, "Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=C:\Links\Products.xlsx")

This might leave you with the impression that the only way to open a data source is via code. But that needs not be the case! You can actually open an arbitrary data source using only Access query. Here’s a sample syntax you can run in an Access query:

SELECT * 
FROM [Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=C:\Links\Products.xlsx].[Sheet1$];

Generally speaking, the connection string you put in the OpenDatabase‘s 4th parameter is the one you’d prefix the “table” with. Therefore the general syntax would be:

FROM [<complete connection string>].[<name of the table>]

You can use the OpenDatabase method and iterate over TableDefs to find the valid names of the table. You can then use that to fill in the 2nd part of the name.

Why open instead of link?

One advantage of opening as opposed to linking is that you can change the connection string at runtime. You also do not have to deal with the required cleanup such as deleting the no-longer-needed linked objects. It’s purely transient which would be perfect for moving data from one source to another source without writing any VBA code.

Here’s one possible scenario. Suppose we want to create text files that is a output from a view on our SQL Server database. You saw from previous articles that we could write VBA code to loop over the DAO recordsets and writing the content one by one. However as an alternative, we can just instead create an Access query with this SQL:

INSERT INTO [Text;DATABASE=C:\Links\].[products.csv;] (Products, Count)
SELECT Products, Count
FROM [ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=myServer;DATABASE=myDatabase;].[vwProducts];

Because both the destination and source are not Access’ source, this is what we call “heterogeneous query”. Note that even if the vwProducts was a linked table, it would still be a “heterogeneous” query. That is because we are still mixing different data sources in a single query.

More importantly, by using a heterogeneous query, we avoid the need to create temporary objects within our Access application. Creating a temporary object can cause the Access application to bloat. This is the case even with importing or by linking or by using recordsets in VBA. A bloated file may in turn require compacting & repairing. However, when you use a heterogeneous query to directly transfer data from one data source to another, you avoid all that bloating. Consequently, it makes it ideal for scenarios where your Access application needs to generate several files without maintenance on the application itself.

Constructing the ad-hoc connection string

By now, you can see why it’s valuable to understand the parameters used in the connection string. It is especially important to control the destination (e.g. path for text files or the range for Excel sheet). With those non-relational data sources, what constitutes a “database” and “table”s in such data source may not be intuitive. You can use the last 3 articles as a reference for help with constructing the connection string and the schema information to ensure that the layout comes out right. That said, there is also a shortcut you can use to help you find the connection string.

You can use the External Tab and either “Import Text” or Import Excel” and choose the link option. It is usually the third option on the wizard as shown.

After you’ve gone through the wizard and saved the new linked table, you can then inspect the connection string via the VBA immediate window with this code:

?CurrentDb.TableDefs("<name of linked table>").Connect

This can provide you hints on how to construct the connection string and you can then customize. Most of the time you will find yourself customizing the path or the table name so it usually work enough as a technique during your development. You can then create a heterogeneous query accordingly and delete the linked table.

Conclusions

In the series, you learned the difference between linking and opening. You then saw how Excel and text files can be used as if they were a DAO.Database objects with “tables”. With the 2nd article, you learned about connection parameters for a Excel workbook. In the 3rd article, you saw the need for having schema information to describe a text file. The 4th article described how to use schema.ini. In the 5th article, you saw how the MSysIMEXSpecs and MSysIMEXColumns can be used as an alternate to the schema.ini method.

Finally, we put it all together in constructing a heterogeneous query as an example of a low-code solution. We do not have to write large amount of VBA code just to push data from one source to another source. I think you will agree it’s far easier to modify an Access query by adjusting the path or table name than it is to write large and complex VBA routine to read and write data. More importantly by using a heterogeneous query, it becomes much easier to handle the changes in the structure on the either side. A new column added? No problem, just add the new column to the query and we’re done.

However, as you see, this requires a good understanding of the construction of the connection string. For that reason, it was necessary to study the intricacies of the connection string in depth as we did from 2nd to 5th articles. Though we can use the linked table wizard to gives us the hint on the connection strings. But it’s only hints. Therefore it’s good to know how to precisely control the output. I hope you agree that investing some effort in understanding how connection strings work will pay for itself in saved labor.