1

If I have a tool that writes data to Access but one of the tables is a linked table, how exactly is the data written to that linked table? Does the data get off-handed to Access and then within Access, Access handles the writing of the data? Or is a kind of link provided to the tool and then the tool writes directly to the table?

The reason for asking is that I've encountered times where some automated tools that I work with will or won't write to a linked table and I'm curious of how to ensure that data is always written.

1 Answers1

1

Usually, all data is written via the Access Database Engine, unless you're doing something weird.

In most situations, it works like this:

  • Tool submits a query via OLEDB/ODBC/DAO to the Access Database Engine (DAO360.DLL/ACEDAO.DLL)
  • Access Database Engine determines which data should be read/written to where (to the file/remote data source)
  • Access Database Engine uses the connect property of the linked table to try and open a connection if necessary, and creates a new query or multiple queries in the appropriate SQL dialect
  • Access Database Engine submits query/queries via the opened connection

However, this can go wrong:

  1. The tool doesn't actually use the Access Database Engine at all, but tries to directly write the file (via UCanAccess/MDBTools/proprietary driver), and those don't support linked connections at all
  2. The connection string in the linked table requires an ODBC driver, password, resource, or something else that's not present or not in the right place
  3. The linked table requires some sort of implicit authentication (e.g. SharePoint), and this doesn't happen
  4. The data source used by the linked table requires a specific lock type (e.g. dbSeeChanges for opening a Dynaset-type recordset on SQL server) which is not used by the tool

And of course, many, many more things can go wrong (in fact, I crashed Excel in an attempt to test reading a linked SharePoint lists while writing this answer).

Erik A
  • 31,639
  • 12
  • 42
  • 67