はじめてのAccessかいはつ

はじめに

令和にAccess開発することになった…。
Accessは社内システムで使ってるけど開発したことないな…と思っていたらお鉢が回ってきたので記録。

Accessとは

Access を使用すると、コードの記述やデータベースの専門的な知識がなくても、データベースを構築できます。
優れたデザインのテンプレートを使用して、すばやくデータベースを構築できます。クエリを使用して、必要なデータを簡単に見つけることができます。簡単なデータ入力のフォームをすぐに作成できます。グループ化されたサマリー レポートにデータをまとめることができます。数十個のウィザードが用意されているので、簡単に始めて、生産性を向上させることができます。
=> Access のトレーニング Access のクイック スタート データベースを作成する

SQLを記述することなくデータを取ってこれる／フォームが作れる／でもあくまでスタンドアローンで作業する場合用…というイメージだったんだけど、結構セッション数取れることを知って驚いてる。

1つの Access データベースのサイズ制限は2gb で、同時ユーザー数が255を超えることはできません。
=> Access データベースを SQL Server に移行する

大きな会社であっても、1部署での閉じた作業であれば、255のセッションが取れるなら結構便利。
ただ、データベースサイズが2GBなのはネック。
Accessでは複数のデータテーブルを作成すると、すべて同じデータベースファイルに保存され、そのファイルのデータサイズが2GBを超えることはできないらしい。これは過去のMS仕様（1ファイルは2GBまで）を引き継いでいるためなんだとか。

そのため、大規模になった／データ容量が不足した場合は、SQLServerに移行するように勧められている。

2GBの制限を超えてAccessの資産、UIを活用したい場合（備考録）

• 複数のAccessファイルにデータを分割してリンクテーブルで参照する

Accessには他のAccessファイルのテーブルを参照する「リンクテーブル」という機能があります。これを使い、ツール本体にはデータを格納せず、複数の別ファイルにデータを分割して格納し、本体からリンクテーブルで参照することで、2GB以上のデータを扱うことができます。
=> Accessで2GB以上のデータベースを扱う方法

• 別のデータベースにデータを格納しAccessから接続する
  Access を SQL Server に接続する
  ※ODBCで接続しているようなのでMySQLなどでも接続できそう。
  ※.netなどで別システム組んだ方がよさそうですけど…

環境

• Microsoft Windows [Version 10.0.19041.804] 64bit
• Microsoft Access for Microsoft 365 32bit

Officeが32bitの理由

64bitになるとほとんどのVBAなどが使用できなくなるので非推奨なんだとか。

Office 2013 には、インストールできるバージョンが 2 つあります (32 ビット版と 64 ビット版)。 その他のほとんどのアプリケーション (特にサード パーティ製のアドイン) とより互換性のあるため、ほとんどのユーザーの方に 32 ビット版をお勧めします。 ただし、特に複数のブロックで構成される情報やグラフィックスを使用している場合は、64 ビット版を検討してください。
迷走するMS MicroSoft Office 2019または新規、再インストールすると64Bitになってしまう件

いろいろ互換性の問題もあるかも？
64bitほどのデータを扱うことは少ないので、開発に合わせて64bit/32bitを使い分けるのはアリなんだと思います。

私は今回64bitをアンインストールして、32bitで再インストールしました。
ただ、teamsは64bitじゃないと動かなかったので、別途インストールしなおしました。
古のoffice（Excel,Word,Access…）以外は64bitのほうが安定している気がします。

VBAの記述方法

配列

' 配列宣言 => 0から開始の10領域
Dim A(9) As Integer
' => A(0) # OK

' 1から開始の10領域
Dim A(1 To 10) As Integer
' => A(0) # NG
' => A(1) # OK

' 多次元配列
Dim A(3,4) AS Integer
' => [1,0,0] # A(0,0) = 1
'    [0,0,0]
'    [0,1,0] # A(2,2) = 1
'    [0,0,0]

' 範囲指定も可能
Dim A(3, 2 to 5) AS Integer
' => [1,0,0] # A(0,2) = 1
'    [0,0,0]
'    [0,1,0] # A(2,4) = 1
'    [0,0,0]

複数行を1列に

本来、Visual Basic では、各命令は行単位で区切られるべき。
でも配列に定数を設定する場合、複数行にしたくない（その書き方をしたくない）場合がある。そんな時は、改行でなく:(コロン)で区切る。

Dim 区分(4)

区分(0) = "A" : 区分(1) = "B-C" : 区分(2) = "-CC" : 区分(3) = "D-Z"  

Excel操作

Accessの集計結果をExcelにレポートとして出したい、ということもあります。
同じVBAですが、名前空間が違うので、データのアクセス方法に違いがあります。

Dim fileName As String
fileName = "C:\text.xls"
Set xls = GetObject(fileName, "excel.sheet.5")
' excel.sheet.5なんだろ…読み取り専用か、xls拡張子で開く用なのかな…

' セル移動
xls.Application.GoTo "r1c1"
' => A1セルを選択

' アクティブセルに設定
Dim A(1 to 10, 1 to 5)  As Integer
For i = 1 To UBound(A, 1)
    For j = 1 To UBound(A, 2)
        Debug.Print i, j
        xls.Application.GoTo "r" & i & "c" & j
        xls.Application.ActiveCell.Value = A(i,j) ' 値設定
    Next j
Next i

テンプレートをつかう

    ' カレントフォルダに置いているExcelテンプレートを開く
    orgFile = Application.CurrentProject.Path & "\テンプレート.xlsx"
    newFile = Application.CurrentProject.Path & "\" & [新規ファイル名] & ".xlsx"
    FileCopy orgFile, newFile
 
    ' 読み取り専用ファイルにして、編集用に非表示
    Set ExApp = CreateObject("Excel.Application")
    Set xls = ExApp.Workbooks.Open(newFile, ReadOnly:=True)

    ' アプリ非表示
    xls.Application.Visible = False

    ' 最初に開いたウィンドウを表示
    xls.Application.Windows(1).Visible = True

    ' アプリのアラートを無視
    xls.Application.DisplayAlerts = False
    
    ' 保存しているファイルは削除し、App上にのみデータがあるようにする
    Kill newFile

名前を設定したり、定義したりする

    'シート名を設定
    For i = 1 To xls.Worksheets.Count
        tmp = xls.Sheets(i).Name
        If Left(tmp, 3) = "yyyy" Then
            xls.Sheets(i).Name = Replace(tmp, "yyyy", [年度] & "年度")
        End If
    Next idx

    ' 名前の定義を追加する
    For i = 1 To xls.Worksheets.Count
        With xls.Sheets(i)
            If i > 1 Then
                col = .cells(4, .Columns.Count).End(-4159).Column
                .Columns(col).Copy Destination:=.Columns(col + 1)
                col = col + 1
            Else
                col = 4
            End If
            .Columns(col).ClearContents
            
            xls.Names.Add Name:=.Name & "_定義の名前_" & i & "", RefersTo:="=" & .cells(2, col).Address(External:=True)
        End With
    NEXT i

ちょっとしたこと

    ' クリップボード削除
    xls.Application.CutCopyMode = False

データ取得

Accessへのアクション

DoCmd オブジェクトのメソッドを使用すると、Visual Basic から Microsoft Office Access のアクションを実行できます。 アクションによって、ウィンドウを閉じる、フォームを開く、コントロールの値を設定する、などのタスクを実行します。
DoCmdオブジェクト(Access)

なるほど。

DoCmd.Hourglass True ' Accessでカーソルを砂時計表示に
DoEvents             ' ほかの処理へ…

' Excelの操作とかする

DoCmd.Hourglass False ' Accessでカーソルを通常のマウスポインターに戻す
DoEvents

SQLを発行する

' SQL組み立て（普通にSQLしてもいいのですが、あとから条件だけ変えて…とかする時に便利）
strSelect = "項目1,項目2,項目3, 項目1 & 項目2 AS 項目12"
strFrom = "Tテーブル"
strWhere = "項目1 = '" & str項目1 & "'" & _
           " AND 項目2 = '" & str項目2
strOrder = "項目1 ASC"
strSQL = "SELECT " & strSelect & " FROM " & strFrom & " WHERE " & strWhere & " ORDER BY " & strOrder
Set dt1 = db.OpenRecordset(strSQL)

' データアクセス
Do Until dt1.EOF
    'Debug.Print ("ActiveCell=" & xls.Application.ActiveCell.Address(External:=True))
    If Not IsNon(dt1![項目1]) Then
         xls.Application.ActiveCell.Value = dt1![項目1]
    End If
    dt1.MoveNext
    col = col + 1
    If Not dt1.EOF Then
        .Columns(xls.Application.ActiveCell.Column).Copy Destination:=.Columns(xls.Application.ActiveCell.Column + 1)
         xls.Application.ActiveCell.offset(0, 1).Select      ' 追加列選択
    End If
Loop

QRecordCount = dt1.RecordCount  ' 末尾までいかないと利用できない

' 初めに移動
If dt1.RecordCount > 0 Then
    dt1.MoveFirst
End If

' クローズ
dt1.Close

Accessの開発で困ったとき

アプリでなく開発画面を起動

• *.mdbを[Shift] + Enter

デバッグ

コマンド	キー
[F8]	ステップイン
[Shift] + [F8]	ステップオーバー

TODO

また何かあったら書き足します。