VBAでのJetとAceのメモメモ

Summary

VBAでdBASE, CSV, TXT, ACESS, EXCElのファイルをテーブルとみなしてデーターをもってくるためのちょっとしたメモ

Readable files

一般的なエクセルファイルxlsx を読むにはAceが必要

Engine | dBase | Access | Text | Excel
------ +------ +------- +----- +-------
Jet    | .dbf  | .mdb   | .txt | .xls
       |       |        | .csv |
------ +------ +------- +----- +-------
Ace    | .dbf  | .mdb   | .txt | .xls
       | .ndx  | .accdb | .csv | .xlsx
       | .mdx  |        |      | .xlsm
       |       |        |      | .xlsb

Ace Engine があるかどうか

Ace(AccessDatabaseEngine)という名称だがAccess をインストールしていてもAce EngineをVBAからは使うことはできない

Can't use the Access ODBC driver or OLEDB provider outside Office Click-to-Run applications

Office の Click-to-Run アプリケーション以外の Access ODBC ドライバーまたは OLEDB プロバイダーを使用できない

Regeditで下記を確認できればVBAでAce が使える(32bit, 62bitどちらでもおなじ)

\HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Microsoft.ACE.OLEDB.xx.0

もしくは下記のVBAコードで確認

Sub foo()

    ''' The Ace Engine is the same place whether it is 32 bit OS or 64 bit OS
    ''' HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Microsoft.ACE.OLEDB.xx.0
    Dim wmi        As Object: Set wmi = CreateObject("Wbemscripting.SWbemLocator")
    Dim wmiSrv     As Object: Set wmiSrv = wmi.ConnectServer(".", "root\default")
    Dim stdRegProv As Object: Set stdRegProv = wmiSrv.Get("StdRegProv")

    Const HKLM  As Long = &H80000002
    Const Ace16 As String = "SOFTWARE\Classes\Microsoft.ACE.OLEDB.16.0"
    Const Ace15 As String = "SOFTWARE\Classes\Microsoft.ACE.OLEDB.15.0"
    Const Ace14 As String = "SOFTWARE\Classes\Microsoft.ACE.OLEDB.14.0"
    Const Ace13 As String = "SOFTWARE\Classes\Microsoft.ACE.OLEDB.13.0"
    Const Ace12 As String = "SOFTWARE\Classes\Microsoft.ACE.OLEDB.12.0"

    Dim v As Variant, SubKey As Variant
    For Each v In Array(Ace16, Ace15, Ace14, Ace13, Ace12)
        stdRegProv.EnumKey HKLM, v, SubKey
        If Not IsNull(SubKey) Then
            Dim arr As Variant: arr = Split(v, "\")
            Debug.Print arr(ArrLen(arr) - 1)
            Debug.Print arr(UBound(arr, 1))
            GoTo Escape
        End If
    Next v

    Debug.Print "Ace not exists"

Escape:
    Set wmi = Nothing
    Set wmiSrv = Nothing
    Set stdRegProv = Nothing

End Sub

Install Ace Engine

下記のホームページで

Microsoft Access Database Engine 2016 Redistributable

32bit, 64bitどちらでインストールしても同じ

AccessDatabaseEngine.exe
AccessDatabaseEngine_X64.exe

インストールするときは/quietオプションを付加する

Z:\Downloads>AccessDatabaseEngine_X64.exe /quiet

Jetで読めるファイルはJetで読む方が安定する

dbf mdb txt csv xlsに関してはProviderをJetにした方がいいかも

Con.Provider = "Microsoft.Jet.OLEDB.4.0"

まとめてみた

使いやすいように下記にまとめてみました！(AdoEXというファイルにまとめてます)

callmekohei's 便利VBAライブラリー (^_^)/

結論

まだまだ勉強不足で間違ってるかも・・・

ご指摘いただけると嬉しいです (^_^;;

おまけ01

下記のファイルをdiffしてAce Engineの場所を特定しました

レジストリの場所を特定するのに使ったファイル
https://gist.github.com/callmekohei/58ee9d4702a6cf1739a8b15c4d7ada2d

おまけ02

今回は使わなかったのですが、wow64の時のレジストリを取る方法

Function foo(ByVal osBit As Long) As Object

    On Error GoTo Escape

    Const strComputer As String = "."

    ''' Context Flags
    ''' https://docs.microsoft.com/en-us/windows/desktop/WmiSdk/requesting-wmi-data-on-a-64-bit-platform#context-flags
    Dim wmiCtx As Object: Set wmiCtx = CreateObject("WbemScripting.SWbemNamedValueSet")
    wmiCtx.Add "__ProviderArchitecture", osBit
    wmiCtx.Add "__RequiredArchitecture", True

    Dim wmi    As Object: Set wmi = CreateObject("Wbemscripting.SWbemLocator")
    Dim wmiSrv As Object: Set wmiSrv = wmi.ConnectServer(strComputer, "root\default", "", "", , , , wmiCtx)

    Set foo = wmiSrv.Get("StdRegProv")

Escape:
    Set wmiCtx = Nothing
    Set wmi = Nothing
    Set wmiSrv = Nothing
End Function

see also

How to view the system registry by using 64-bit versions of Windows

Requesting WMI Data on a 64-bit Platform

StdRegProv class

32bitのregeditのパス(wow64)

%systemroot%\syswow64\regedit.exe

64bitのregeditのパス

%SystemRoot%\system32\regedt32