Create Derby SQL from MS Access VBA

Module1.bas

Attribute VB_Name = "Module1"
Option Compare Database
Option Explicit

Public Sub sp_help()
    
    Dim db As DAO.Database
    Dim tbl As DAO.TableDef
    Dim fld As DAO.Field
    Dim sStmt As String
    Dim isFirst As Boolean
    Dim fs As Object, a As Object
    Dim z As String
    
    
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.CreateTextFile("D:\Projects\Taxi\Database\sql.txt", True)
    a.Writeline ("/* Create DB Structures. /*")
    
    
    Set db = CurrentDb()
    For Each tbl In db.TableDefs
        If Left(tbl.Name, 4) <> "MSys" Then
            
        
            Debug.Print ""
            
            isFirst = True
            a.Writeline "create table [" & tbl.Name & "]("
            
                Debug.Print tbl.Name
                For Each fld In tbl.Fields
                    If Not isFirst Then
                        a.Write ","
                    Else
                        isFirst = False
                    End If
                    a.Writeline " " & fld.Name & " " & getFieldName(fld)
                    'Debug.Print " " & fld.Name & " " & getFieldName(fld)
                Next
            a.Writeline ");"
        End If
    Next
    Set db = Nothing
    
    a.Close
End Sub

Public Sub sp_help_data()
    
    Dim db As DAO.Database
    Dim tbl As DAO.TableDef
    Dim fld As DAO.Field
    Dim sStmt As String
    Dim isFirst As Boolean
    Dim fs As Object, a As Object
    Dim z As String
    Dim idx As Integer
    
    Set fs = CreateObject("Scripting.FileSystemObject")
    
    
    
    Set db = CurrentDb()
    For Each tbl In db.TableDefs
        If Left(tbl.Name, 4) <> "MSys" Then
            Dim sFileName, rs As DAO.Recordset, rstFiltered As DAO.Recordset
            Dim sSQL$, sSQLValue$
            
            Dim fileNameIdx As Integer
            Dim fileRecordCount As Integer
            
            sFileName = getDerbyName(tbl.Name)
            Debug.Print sFileName
            fileNameIdx = 1000
            Set a = fs.CreateTextFile("D:\Projects\Taxi\Database\" & sFileName & fileNameIdx & ".sql.txt", True, True)
            
            a.Writeline "/* Insert DB database. */"
            a.Writeline "Connect 'jdbc:derby:database.derby.v2';" & vbCrLf
            
            Set rstFiltered = tbl.OpenRecordset(RecordsetTypeEnum.dbOpenDynaset, RecordsetOptionEnum.dbReadOnly)
            
            
            
            isFirst = True
            fileRecordCount = 0
            
            If (tbl.Name = "Table2-COLLECTION JOURNAL" Or _
                tbl.Name = "Table7-WORKSHOP JOURNAL") Then 'Or _
                'tbl.Name = "Table5-OUTSTANDING COLLECTION") Then
                
                rstFiltered.Filter = " RECDate > #01-01-2018# "
            End If
            Set rs = rstFiltered.OpenRecordset
            While Not rs.EOF
                sSQL = ""
                sSQLValue = ""
                isFirst = True
                
                
                    
                
                fileRecordCount = fileRecordCount + 1
                sSQL = "insert into " & LCase(getDerbyName(tbl.Name)) & "("
                    For idx = 0 To tbl.Fields.Count - 1
                        Set fld = tbl.Fields(idx)
                        
                        If Not isFirst Then
                            sSQL = sSQL & ", "
                            sSQLValue = sSQLValue & ", "
                        Else
                            isFirst = False
                        End If
                        
                        If fld.Name = "order" Or fld.Name = "N0" Then
                            sSQL = sSQL & "id"
                        ElseIf fld.Name = "note" Then
                            sSQL = sSQL & "NOTES"
                        ElseIf fld.Name = "from" Or fld.Name = "to" Then
                            sSQL = sSQL & "Date" & fld.Name
                        Else
                            sSQL = sSQL & fld.Name
                        End If
                                                
                        sSQLValue = sSQLValue & getFieldValue(rs.Fields(idx))
                        
                        If (fld.Name = "CARNUMBER" And (tbl.Name = "Table1-CAR LIST" Or tbl.Name = "Table4-OUTSTANDING FOLLOW UP")) Or _
                           (fld.Name = "RECNUMBER" And (tbl.Name = "Table5-OUTSTANDING COLLECTION" Or _
                            tbl.Name = "Table7-WORKSHOP JOURNAL")) _
                        Then
                            sSQL = sSQL & ", ID"
                            sSQLValue = sSQLValue & ", " & getFieldValue(rs.Fields(idx))
                        End If
                        If (tbl.Name = "Table6-INFORMATIONS" And fld.Name = "PRATE") Or _
                           (tbl.Name = "Table9-NONE OPERATION" And fld.Name = "DocNo") _
                        Then
                            sSQL = sSQL & ", ID"
                            sSQLValue = sSQLValue & ", " & fileRecordCount
                        End If
                    Next
                
                
                a.Writeline sSQL & ")"
                a.Write "VALUES("
                a.Write sSQLValue
                a.Writeline ");" & vbCrLf
                
                
                If fileRecordCount > 10000 Then
                    fileRecordCount = 0
                    
                    a.Close
                    converttuUTF8 "D:\Projects\Taxi\Database\" & sFileName & fileNameIdx & ".sql.txt", _
                        "D:\Projects\VinaTaxi\Database\out\" & sFileName & fileNameIdx & ".sql.txt"
                    
                    fileNameIdx = fileNameIdx + 1
                    Set a = fs.CreateTextFile("D:\Projects\Taxi\Database\" & sFileName & fileNameIdx & ".sql.txt", True, True)
                    
                    a.Writeline "/* Insert DB database. */"
                    a.Writeline "Connect 'jdbc:derby:database.derby.v2';"
                    
                End If
                
                rs.MoveNext
            Wend
            
            a.Close
            converttuUTF8 "D:\Projects\Taxi\Database\" & sFileName & fileNameIdx & ".sql.txt", _
                        "D:\Projects\Taxi\Database\out\" & sFileName & fileNameIdx & ".sql.txt"
                        
            
            rstFiltered.Clone
            Set rstFiltered = Nothing
            
            rs.Close
            Set rs = Nothing
            
        End If
    Next
    Set db = Nothing
    
    MsgBox "Done. Done. Done. Done. Done. Done. Done. Done. Done. Done. Done. Done. Done. Done. Done. "
End Sub

Sub converttuUTF8(file1 As String, file2 As String)

    Call Shell("javaw -cp D:/Projects/Taxi/Database convert """ & file1 & """ """ & file2 & """", vbNormalFocus)
    
    Call Kill(file1)
    
End Sub

Function getFieldValue(fld As Field)
    Dim sRet As String
    
    sRet = ""
    
    If IsNull(fld.Value) Then
        sRet = "null"
    ElseIf fld.Type = DataTypeEnum.dbText Then
        sRet = "'" & Replace("" & fld.Value, "'", "''") & "'"
    ElseIf fld.Type = DataTypeEnum.dbDate Then
        sRet = "'" & Format(fld.Value, "YYYY-MM-DD HH:mm:ss") & "'"
    Else
        sRet = "" & fld.Value
    End If
    
    getFieldValue = sRet
End Function

Function getFieldName(ftyle As Field)
    Dim ret As String
    ret = "n/a"
    Select Case ftyle.Type
        Case DataTypeEnum.dbInteger
        Case DataTypeEnum.dbLong
            ret = "long"
        Case DataTypeEnum.dbDouble
            ret = "double"
        Case DataTypeEnum.dbDate
            ret = "DateTime"
        Case DataTypeEnum.dbChar
            ret = "string"
        Case DataTypeEnum.dbText
            ret = "text" & "(" & ftyle.Size & ")"
            
        Case Else
            ret = "" & ftyle
    End Select
    getFieldName = ret
End Function


Function getDerbyName(tableName As String) As String

    Dim ret As String
    
    Select Case tableName
    
        Case "Table10-DEPT APPROVAL"
            ret = "TX_DEPTAPPROVAL"
            
        Case "Table1-CAR LIST"
            ret = "TX_CARLIST"
            
        Case "Table2-COLLECTION JOURNAL"
            ret = "TX_COLLECTIONJOURNAL"
            
        Case "Table3-PAYMENT CODE"
            ret = "TX_PAYMENTCODE"
            
        Case "Table4-OUTSTANDING FOLLOW UP"
            ret = "TX_OSDFOLLOWUP"
            
        Case "Table5-OUTSTANDING COLLECTION"
            ret = "TX_OSDCOLLECTION"
            
        Case "Table6-INFORMATIONS"
            ret = "TX_INFORMATIONS"
            
        Case "Table7-WORKSHOP JOURNAL"
            ret = "TX_WORKSHOPJOURNAL"
            
        Case "Table8-OUTSTANDING"
            ret = "TX_OUTSTANDING"
            
        Case "Table9-NONE OPERATION"
            ret = "TX_NONEOPERATION"
            
    End Select
    
    getDerbyName = ret
End Function