Access メモ – テックメモ

DBエンジン(JET)

JETはOS32bit専用(JET自体32bitのみ)なのでOS64bitで動かす場合、
32bitプログラムまたは32bitOfficeで動かす。(WOW64で動かす)
また、JETはaccdbを使えない。
(Accessから直接mdb/accdbを操作する場合は関係なく、64bitAccessからmdbを開くことも可能)

以前はOS標準で搭載されていたため、追加で再頒布可能コンポーネントをインストールする必要がなく便利だったが、現在は非推奨。

・OS64bit/64bitプログラム
利用できない。
・OS64bit/32bitプログラム(32bitOffice)
“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=xxx.mdb”
は利用できる。
“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=xxx.accdb”
は利用できない。

DBエンジン(ACE)

ACEはOS32/64bit(ACE自体が32/64bit存在する)でも使える。
mdb/accdbが使える。

・OS32bit/32bitプログラム(32bitOffice)
・OS64bit/32bitプログラム(32bitOffice)
・OS64bit/64bitプログラム(64bitOffice)
“Provider=Microsoft.ACE.OLEDB.12.0;Data Source=xxx.mdb”
“Provider=Microsoft.ACE.OLEDB.12.0;Data Source=xxx.accdb”
すべて利用できる。

パスワード付きのmd/accdbを開く場合、
“Provider=Microsoft.ACE.OLEDB.12.0;Data Source=xxx.mdb/accdb;” & “Jet OLEDB:Database Password=xxx”
OS64bit/32bit、OS64bit/64bitプログラムどちらでも利用できる。

OS標準で入らないので、Accessや再頒布可能コンポーネントなどが必要。
※32bitのOfficeが入っている場合、AccessDatabaseEngine_x64.exe /quiet

DBエンジン確認

(New-Object data.oledb.oledbenumerator).getElements() | select sources_name, sources_description

1	(New-Object data.oledb.oledbenumerator).getElements() \| select sources_name, sources_description

にて確認できる。32bitを確認したい場合、
%systemroot%\syswow64\windowspowershell\v1.0\powershell.exe
のPowerShellを起動する。

ADOとDAOの違い

DAOはJETエンジンを対象に開発され、Access専用。
ADOはAccess以外も対象。OLEDBを利用する。

VBA(ADO)での利用

Sub test()

' 参照設定しない場合
Dim cn1 As Object
Set cn1 = CreateObject("ADODB.Connection")

' 参照設定する場合(Microsoft ActiveX Data Objects X.X Library)
Dim cn2 As ADODB.Connection
Set cn2 = New ADODB.Connection

cn1.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\test.mdb;" & "Jet OLEDB:Database Password=abc;"

Dim rs As Object
Set rs = cn1.Execute("select * from tbl")

ActiveCell.CopyFromRecordset rs

If rs.State = 1 Then rs.Close
Set rs = Nothing
 
If cn1.State = 1 Then cn1.Close
Set cn1 = Nothing

End Sub

Sub test()

' 参照設定しない場合

Dim cn1 As Object

Set cn1 = CreateObject("ADODB.Connection")

' 参照設定する場合(Microsoft ActiveX Data Objects X.X Library)

Dim cn2 As ADODB.Connection

Set cn2 = New ADODB.Connection

cn1.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\test.mdb;" & "Jet OLEDB:Database Password=abc;"

Dim rs As Object

Set rs = cn1.Execute("select * from tbl")

ActiveCell.CopyFromRecordset rs

If rs.State = 1 Then rs.Close

Set rs = Nothing

If cn1.State = 1 Then cn1.Close

Set cn1 = Nothing

End Sub

取得方法の種類

'取得方法①
Do Until rs.EOF
  Debug.Print rs!xxx 'レコードセット !マーク フィールド名という順番で指定できる。
  rs.MoveNext
Loop
rs.MoveFirst

'取得方法②
For c = 1 To rs.Fields.Count
  w.Cells(1, c).Value = rs.Fields(c - 1).Name
Next c
w.Cells(2, 1).CopyFromRecordset rs

'取得方法③
Do Until rs.EOF
  Debug.Print rs.Fields("xxx") 'レコードセット files ("名称") あるいは (0～index) などで指定できる
  rs.MoveNext
Loop

'取得方法①

Do Until rs.EOF

Debug.Print rs!xxx 'レコードセット !マークフィールド名という順番で指定できる。

rs.MoveNext

Loop

rs.MoveFirst

'取得方法②

For c = 1 To rs.Fields.Count

w.Cells(1, c).Value = rs.Fields(c - 1).Name

Next c

w.Cells(2, 1).CopyFromRecordset rs

'取得方法③

Do Until rs.EOF

Debug.Print rs.Fields("xxx") 'レコードセット files ("名称") あるいは (0～index) などで指定できる

rs.MoveNext

Loop

OLEDBドライバがない

ExcelからAccess接続したいなと思ったら、インストール可能な ISAM ドライバが見つかりませんでしたと言われた。

どうやら以下を読むと、2016/32bit(サブスクリプション)がクイック形式で入っていて、ドライバが入らないということらしい。

https://blogs.technet.microsoft.com/officesupportjp/2016/09/06/not_available_access_odbc_oledb_outside_c2r_applications/

https://blogs.technet.microsoft.com/officesupportjp/2016/09/08/howto_c2r_or_msi/

MySQLへのエクスポート

DSNを作っておくと自動でエクスポートできるとのこと。

http://dev.mysql.com/downloads/connector/odbc/
からmsiファイルでインストール。

コントロールパネル＞管理ツール＞ODBCデータソース＞ユーザー DSN＞追加
Accessを起動してテーブルデザインをエクスポート先のMySQLと合わせる。
テーブルを選択し、
右クリック＞エクスポート＞ODBC データソース
(すでに存在しているテーブルに上書きはできない)