AccessToSQLite

AccessDBからSQLiteへテーブルを移行する方法

AccessDBのテーブル定義をCREATE文にして出力します。

Sub ExportTableDefinitionsWithComments()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim sql As String
    Dim output As String
    Dim firstField As Boolean
    Dim comment As String

    Set db = CurrentDb

    For Each tdf In db.TableDefs
        If Left(tdf.Name, 4) <> "MSys" And Left(tdf.Name, 1) <> "~" Then
            sql = "CREATE TABLE " & tdf.Name & " (" & vbCrLf
            firstField = True

            For Each fld In tdf.Fields
                If Not firstField Then sql = sql & "," & vbCrLf
                comment = GetFieldDescription(fld)
                sql = sql & "  " & fld.Name & " " & AccessTypeToSQL(fld.Type, fld.Size)
                If comment <> "" Then
                    sql = sql & " -- " & comment
                End If
                firstField = False
            Next fld

            sql = sql & vbCrLf & ");" & vbCrLf & vbCrLf
            output = output & sql
        End If
    Next tdf

    ' 出力をファイル or イミディエイトウィンドウ
    Debug.Print output
End Sub

Function AccessTypeToSQL(accessType As Integer, size As Integer) As String
    Select Case accessType
        Case dbBoolean: AccessTypeToSQL = "BOOLEAN"
        Case dbByte: AccessTypeToSQL = "TINYINT"
        Case dbInteger: AccessTypeToSQL = "SMALLINT"
        Case dbLong: AccessTypeToSQL = "INTEGER"
        Case dbSingle: AccessTypeToSQL = "REAL"
        Case dbDouble: AccessTypeToSQL = "DOUBLE"
        Case dbCurrency: AccessTypeToSQL = "NUMERIC(19,4)"
        Case dbDate: AccessTypeToSQL = "DATETIME"
        Case dbText: AccessTypeToSQL = "TEXT(" & size & ")"
        Case dbMemo: AccessTypeToSQL = "TEXT"
        Case dbGUID: AccessTypeToSQL = "GUID"
        Case Else: AccessTypeToSQL = "TEXT"
    End Select
End Function

Function GetFieldDescription(fld As DAO.Field) As String
    On Error GoTo NoDescription
    GetFieldDescription = fld.Properties("Description")
    Exit Function
NoDescription:
    GetFieldDescription = ""
End Function

手順

1. Accessを開く
2. Alt + F11 でVBAエディタを開く
3. 上記コードを挿入
4. ExportTableDefinitionsAsSQL を実行
5. イミディエイトウィンドウにCREATE TABLE文が出力される（コピペ可能）