Access

Access (like SQLite) stores everything that belongs to a database solution in a single file, typically with the extension .accdb. This approach allows to easily deploy a database by just copying the *.accdb file to the computer where it is needed. Unlike SQLIte, however, an *.accdb not only stores data but also reports and UI elements.

Office Access Connectivity Engine (ACE)

Before Access 2007, the default database engine was JET. With Access 2007, Microsoft introduced(?) the Office Access Connectivity Engine (fileformat: .accdb).

Because JET is deprecated and not even available for 64-bit Windows, the Access development team decided to copy the JET source and adapt it for their own purposes (See Access 12's new data engine for more details).

As per this link, this copied engine was given the odd name Access Control Entry. However, I believe ACE stands for Access Connectivity Engine.

Apparently, ACE is fully backward compatible to JET.

As far as I understand it, ACE can be installed without Office using Microsoft Access Database Engine.

TODO

Debug queries with JETSHOWPLAN.

Query Access into Excel

' PATH=c:\lib\runVBAFilesInOffice;%PATH%
' set VBAMODDIR= y:\2016-03-Entwicklung-Ren�\prototypes\01\VBAModules\
'
' runVBAFilesInOffice -excel %CD%\queryAccessIntoExcel %VBAMODDIR%common\File %VBAMODDIR%Database\SQL %VBAMODDIR%Database\ADOHelpers -c main c:\temp\sql.sql
'
' Runs the select statement in the given file and writes
' the returned values into an excel sheet.
'

sub main(accDB as string, fileWithSelectStatement as string) ' {

  dim sqlText as string
  sqlText = slurpFile(fileWithSelectStatement)
  sqlText = removeSQLComments(sqlText)

  dim con as ADODB.connection
  set con = openADOConnectionToAccess(accDB)

  dim rs as ADODB.recordSet
  set rs = con.execute(sqlText)

  range("A1").copyFromRecordset rs

  activeWorkbook.saved = true

end sub ' }

Github repository about-Access, path: /scripts/queryAccessIntoExcel.bas

Runtime Version

Microsoft provides a royality-free Runtime version of Access that allows to open existing Access databases, but not to modify or create databases.

When such a runtime version is started with MSACCESS.EXE, a message box with Can't find the database you specified, or you didn't specify a database at all. is shown.

Access

Office Access Connectivity Engine (ACE)

TODO

Query Access into Excel

Runtime Version

See also