Analyzing the key population query some more In part 3 of our ODBC tracing series, we are going to take a further insight into Access managing keys for ODBC linked tables and how it sorts and groups the SELECT queries together. In the previous article we learned how a dynaset-type recordset is in fa [...]
UPDATE: Added some more clarifications around the meaning of SQLExecute and how Access is handling the prepared queries. Thanks, Bob! What's Access doing when we browse and look at records in a ODBC linked table? In the second part of our ODBC tracing series, our focus will turn to the impact the re [...]
This is a six-part series of articles on ODBC tracing to help Access developers troubleshoot and work with Access when developing an application that uses ODBC data source(s), usually but not exclusively SQL Server. The series aims to demonstrate how to use the ODBC tracing to monitor ODBC SQL state [...]
SQL Server usually comes with useful sample databases to demonstrate new features and some possible design. I usually find sample databases far more useful as a playground for developing generic components against an existing database to use in other projects. I find that using a empty or too small [...]
I've always found Itzik Ben-Gan's excellent chart on the logical SQL processing immensely helpful in reasoning about the querying performance. Even though the chart was made for SQL Server, it still is applicable to any database engine that follow SQL Standard, which also includes Access database en [...]
Since Access 2010, Access has supported Attachments data type which on the surface seems like a convenient feature for storing small images or files. However, a quick google search will usually show that they are best avoided. This all boils down to the fact that an Attachments data type is actually [...]
Bug Causes Microsoft Office 365 Build 2105 Breaks Access Applications UPDATE: The bug may be due to the vbWatchDog crashing on 64-bit builds. We initially reported that this was an issue affecting the 2105 build in general. However, further findings and additional reporting has indicated that [...]
Writing Readable Code for VBA - Try* pattern Lately, I've been finding myself using the Try pattern more and more. I really like this pattern because it makes for much more readable code. This is especially important when programming in a mature programming language like VBA where the error h [...]
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 Exce [...]
Connection String Parameters for Saved Specifications Access provides a second method of describing text files' schema by using system tables MSysIMEXSpecs and MSysIMEXColumns to save the specifications. In the previous article, I covered how schema.ini can be used to describe a text file's s [...]
Connection String Parameters for Schema.ini Making a schema.ini to open or link text files from Access is one of two possible methods that can be used to work with data contained in the files from Access. In the previous article, we looked at the text file's connection string parameters. We a [...]