以下は、動作保証をしないので、自己責任で確認のこと。プログラム等その他により生じたいかなる損害等その他について、責任を負いませんので、予めご了承してください。

（予定）Accessファイルの解析 （テーブル・クエリ）

（予定）SQLViewerの基礎

（予定）リンクの設定解除

（予定）ADO接続・DAO接続のプログラム関数の自動生成用ツール（アクセス版）

【SQLの整形処理】

Access VBAを使用（EXCEL VBAの場合は、Option Compare Databaseを削除）

Option Compare Database

Option Explicit

'Dictionary

Public SQLDIC As Scripting.Dictionary 'データ用

Public SQLIndex As Scripting.Dictionary '処理用

'整形用配列

Public SQLValue() As String '値用配列

Public SQLVIew() As String '表示用配列

'試験用

Public Sub test()

Dim SQL As String

'テスト用のSQL

SQL = "'Comment"

SQL = SQL & "Select t1.d1 as F1,t2.d2 as F2 from test as t1,test2 as t2 ;"

Call SQL_Shaping(SQL)

End Sub

Public Function SQL_Shaping(ByVal SQL As String, Optional ByVal DICindex As Integer = 0) As Boolean

'SQL整形処理

Dim a As Double 'カウント変数

Dim a2 As Double 'カウント変数

Dim c As Double 'カウント変数

Dim i As Double '位置用変数

Dim i2 As Double '位置用変数

Dim sData As String '文字列データ用変数

Dim hData() As String '文字列データ用配列

Dim item As Variant 'アイテム用変数

Dim KEYposition() As String 'キーワードの位置情報用配列

'生成

Set SQLDIC = New Scripting.Dictionary

Set SQLIndex = New Scripting.Dictionary

'リセット

SQLDIC.RemoveAll

SQLIndex.RemoveAll

'初期化

Erase SQLValue

Erase SQLVIew

'-------------------------------------

'キーと値の設定

'キーと内容の選択

Select Case DICindex

Case 0: Call DIC_NO_001

Case Else

End Select

'-------------------------------------

'データの設定

For Each item In SQLDIC

Call setSQLIndex(SQL, SQLDIC(CStr(item)))

Next

c = 1

For a = 1 To Len(SQL)

If SQLIndex.Exists(CStr(a)) = True Then

ReDim Preserve KEYposition(c)

KEYposition(c) = a

c = c + 1

End If

Next

i2 = 1

c = 1

For a = 1 To UBound(KEYposition)

i = KEYposition(a)

If c > 1 Then

'予約語以外

sData = ""

Erase hData

sData = Mid(SQL, _

i2 + Len(SQLIndex(CStr(i2))), _

Len(SQL) - (i2 + Len(SQLIndex(CStr(i2)))) + 1 - Len(Mid(SQL, i)))

Select Case SQLIndex(CStr(i2))

Case "'", "/"

Call setPreserve(c, SQLIndex(CStr(i2)) & sData, 1)

c = c + 1

Case Else

hData = Split(sData, ",")

If UBound(hData) > 0 Then

For a2 = LBound(hData) To UBound(hData)

If a2 <> UBound(hData) Then

Call setPreserve(c, hData(a2) & ",")

Else

Call setPreserve(c, hData(a2))

End If

c = c + 1

Next

Else

Call setPreserve(c, sData)

c = c + 1

End If

End Select

End If

'予約語

Select Case SQLIndex(CStr(i))

Case "'", "/"

Case Else

Call setPreserve(c, SQLIndex(CStr(i)))

End Select

c = c + 1

i2 = i

Next

';がない場合用

Call setPreserve(c, Mid(SQL, i2 + Len(SQLIndex(CStr(i2)))))

Debug.Print "----------------------------SQL"

For a = 1 To UBound(SQLValue)

sData = SQLValue(a)

If sData <> "" Then

Debug.Print Trim(SQLValue(a))

End If

Next

Debug.Print "----------------------------VIEW"

For a = 1 To UBound(SQLVIew)

sData = SQLVIew(a)

If sData <> "" Then

Debug.Print Trim(SQLVIew(a))

End If

Next

End Function

Private Function setSQLIndex(ByVal sender As String, ByVal key As String)

'追加処理

Dim i As Double

Dim i2 As Double

Do

i = InStr(i2 + 1, sender, key, vbTextCompare)

If i = 0 Then

Exit Do

Else

SQLIndex.Add key:=CStr(i), item:=CStr(key)

' Debug.Print i & ":" & key

End If

i2 = i

Loop

' Debug.Print Mid(sender, 1, i2 - 1)

End Function

Private Sub setPreserve(ByVal index As Double, _

ByVal sender As String, _

Optional ByVal Sindex As Integer = 0)

'値用と表示用の配列へデータを維持する処理

Select Case Sindex

Case 0

ReDim Preserve SQLValue(index)

SQLValue(index) = sender

ReDim Preserve SQLVIew(index)

SQLVIew(index) = sender

Case 1

ReDim Preserve SQLVIew(index)

SQLVIew(index) = sender

Case 2

ReDim Preserve SQLValue(index)

SQLValue(index) = sender

End Select

End Sub

Private Sub DIC_NO_001()

'基礎

SQLDIC.Add key:=CStr("/"), item:="/"

SQLDIC.Add key:=CStr("'"), item:="'"

SQLDIC.Add key:=CStr("Select"), item:="Select"

SQLDIC.Add key:=CStr("from"), item:="from"

' SQLDIC.Add key:=CStr(","), item:=","

SQLDIC.Add key:=CStr(";"), item:=";"

End Sub

■応用例

Dictionaryの使用例なので、メモリーが足りないとデータの文字列が途中で切れてしまい、切れた部分が保持できないことから、あまり文字数を保持できないことがよくわかる。このため、Dictionary単体で入れ子を作るのは、KEYとしてデータ量に注意をしながらとなる。主にクラスでコレクションとの組み合わせが応用となるが、Dictionaryを単純に複数用いて、複数の動的配列と組み合わせることでも複雑な処理は可能なものの、可読性が落ちることに注意が必要となる。

注：Dictionaryにおけるキーの有無について、エラートラップによる処理は余計な情報を保持しかねないので注意が必要。

■資料

VBAのヘルプファイルやMSDNに文法やテクニックがある。アルゴリズムは古典。

©Hirotoshi Takano