yBV4dXr7

1
2Public Sub createMySqlDump(Filename As String)
3    ' Declaration of Variables
4    
5    Dim j As Integer
6    Dim k As Integer
7    
8    Dim SQL As String
9    Dim SQL_INSERT As String
10    
11    Dim db As Database
12    Dim tables As TableDefs
13    Dim table As TableDef
14    Dim lf As fields
15    Dim f As field
16    Dim objIndex As Index
17    
18    Dim rs As Recordset
19    
20    'init Database
21    Set db = CurrentDb
22    Set tables = db.TableDefs
23    
24    'iterate over all Tables
25    For Each table In tables
26         
27        If Left(table.name, 4) <> "MSys" Then
28            ' if table is not system Table
29             
30            SQL = ""        'Init SQL
31            SQL_INSERT = ""
32    
33            Dim pkf As String 'Field for primary Keys
34            Dim tablename As String 'field for tablename
35            tablename = Replace(table.name, " ", "_") 'correct Tablename
36            
37            
38            ' Writing directly into file instead of storing in String saves about 80-90% of runtime.
39            ' Prepare File for Output
40            ' set and open file for output
41            Dim intF As Integer
42            intF = FreeFile()
43            Open Filename & tablename & ".sql" For Output As intF
44            
45            
46            'Drop exisitng Table
47            Print #intF, "DROP TABLE IF EXISTS " & tablename & ";" & vbNewLine
48            'Create New Table
49             Print #intF, "CREATE TABLE " & tablename & "(" & vbNewLine
50            
51            'Find Primary Key
52            For Each objIndex In table.Indexes
53                If objIndex.Primary Then
54                    pkf = objIndex.fields
55                    pkf = Replace(pkf, "+", "")
56                    Exit For
57                End If
58            Next
59            
60            'Prepare insert Statement
61            SQL_INSERT = "INSERT INTO " & tablename & "("
62            j = 0
63            'Iterate over all fields
64            For Each f In table.fields
65                 Print #intF, vbTab & f.name & " " & getFieldType(f.Type, f.name = pkf)
66                 Print #intF, IIf(j < table.fields.Count - 1, ",", "") & vbNewLine
67                SQL_INSERT = SQL_INSERT & f.name
68                SQL_INSERT = SQL_INSERT & IIf(j < table.fields.Count - 1, ",", "")
69                j = j + 1
70            Next
71            'finalize SQL
72            Print #intF, ");" & vbNewLine
73            
74            Print #intF, SQL_INSERT & ") " & vbNewLine & " VALUES "
75            
76            'Prepare Selecte Statement
77            Dim sql_select As String
78            sql_select = "select * from " & tablename & " ORDER BY " & Replace(pkf, ";", ",")
79            'open Recordset
80            Set rs = db.OpenRecordset(sql_select)
81            'If Data is found
82            If rs.RecordCount > 0 Then
83                rs.MoveFirst
84                'loop for all rows
85                Do Until rs.EOF
86                    Print #intF, vbNewLine & vbTab & "("
87                    j = 0
88                    'loop for all fields
89                    For Each f In table.fields
90                        Print #intF, "'" & cleanValue(IIf(IsNull(rs(f.name).value), "", rs(f.name).value)) & "'" & IIf(j < table.fields.Count - 1, ",", "")
91                        j = j + 1
92                    Next
93                    Print #intF, ")"
94                    
95                    rs.MoveNext
96                    If Not rs.EOF Then
97                        Print #intF, ","
98                    End If
99                Loop
100                Print #intF, vbNewLine & ";" & vbNewLine & vbNewLine
101            End If
102             
103            Close #intF
104    
105        End If
106        Set rs = Nothing
107    Next
108   
109
110End Sub