Welcome, guest | Sign In | My Account | Store | Cart

Reads the structure of a Jet (Microsoft Access .MDB) database file, and creates the SQL DDL necessary to recreate the structure.

Originally written to aid in migrating Jet databases to larger RDBMS systems, through E/R design tools, when the supplied "import" routines missed objects like indexes and FKs.

A first experiment in Python, that became an often used tool.

Python, 265 lines
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
# jet2sql.py - M.Keranen <mksql@yahoo.com> [07/12/2000]
# --------------------------------------------------------------------
# Creates ANSI SQL DDL from a MS Jet database file, useful for reverse
# engineering database designs in E/R tools.
#
# Requires DAO 3.6 library.
# --------------------------------------------------------------------
# Usage: python jet2sql.py infile.MDB outfile.SQL

import sys, string, pythoncom, win32com.client

const = win32com.client.constants
daoEngine = win32com.client.Dispatch('DAO.DBEngine.36')

class jetReverse:
    def __init__ (self, infile):

        self.jetfilename=infile
        self.dtbs = daoEngine.OpenDatabase(infile)

        return

    def terminate(self):
        return

    def writeTable(self, currTabl):
        self.writeLine('\ncreate table ' + chr(34) + currTabl.Name + chr(34),"",1)
        self.writeLine('(',"",1)

        # Write Columns
        cn=0
        for col in currTabl.Fields:
            cn = cn +1
            self.writeColumn(col.Name, col.Type, col.Size, col.Required, col.Attributes, col.DefaultValue, col.ValidationRule, currTabl.Fields.Count-cn)

        # Validation Rule
        tablRule = currTabl.ValidationRule
        if tablRule <> "":
            tablRule = "    check(" + tablRule + ") "
            self.writeLine("",",",1) # add a comma and CR previous line
            self.writeLine(tablRule,"",0)

        # Primary Key
        pk=self.getPrimaryKey(currTabl)
        if pk <> "":
            self.writeLine("",",",1) # add a comma and CR previous line
            self.writeLine(pk,"",0)

        # End of table
        self.writeLine("","",1) # terminate previous line
        self.writeLine(');',"",1)

        # Write table comment
        try: sql = currTabl.Properties("Description").Value
        except pythoncom.com_error: sql=""
        if sql <> "":
           sql = "comment on table " + chr(34) + currTabl.Name + chr(34) + " is " + chr(34) + sql + chr(34) +";"
           self.writeLine(sql,"",1)

        # Write column comments
        for col in currTabl.Fields:
            try: sql = col.Properties("Description").Value
            except pythoncom.com_error: sql=""
            if sql <> "":
               sql = "comment on column " + chr(34) + currTabl.Name + chr(34) + "." + chr(34) + col.Name + chr(34) + " is " + chr(34) + sql + chr(34) + ";"
               self.writeLine(sql,"",1)

        # Write Indexes
        self.writeIndexes(currTabl)

        return

    def writeColumn(self, colName, colType, length, requird, attributes, default, check, colRix):
        # colRix: 0 based index of column from right side. 0 indicates rightmost column

        if colType == const.dbByte: dataType = "Byte"
        elif colType == const.dbInteger: dataType = "Integer"
        elif colType == const.dbSingle: dataType = "Single"
        elif colType == const.dbDouble: dataType = "Double"
        elif colType == const.dbDate: dataType = "DateTime"
        elif colType == const.dbLongBinary: dataType = "OLE"
        elif colType == const.dbMemo: dataType = "Memo"
        elif colType == const.dbCurrency: dataType = "Currency"
        elif colType == const.dbLong:
            if  (attributes & const.dbAutoIncrField): dataType = "Counter"
            else: dataType = "LongInteger"
        elif colType == const.dbText:
            if length == 0: dataType = "Text"
            else: dataType = "char("+str(length)+")"
        elif colType == const.dbBoolean:
            dataType = "Bit"
            if default == "Yes": default = "1"
            else: default = "0"
        else:
            if length == 0: dataType = "Text"
            else: dataType = "Text("+str(length)+")"

        if default <> "":
            defaultStr = "default " + default + " "
        else: defaultStr = ""

        if check <> "":
            checkStr = "check(" + check + ") "
        else:
            checkStr = ""

        if requird or (attributes & const.dbAutoIncrField):
            mandatory = "not null "
        else:
            mandatory = ""

        sql = "    " + chr(34) + colName + chr(34) + " " + dataType + " " + defaultStr + checkStr + mandatory
        if colRix > 0:
            self.writeLine(sql,",",1)
        else:
            self.writeLine(sql,"",0)

        return

    def getPrimaryKey(self, currTabl):

        # Get primary key fields
        sql = ""
        for idx in currTabl.Indexes:
           if idx.Primary:
              idxName = idx.Name
              sql = "    primary key "
              cn=0
              for col in idx.Fields:
                  cn=cn+1
                  sql = sql + chr(34) + col.Name + chr(34)
                  if idx.Fields.Count > cn : sql = sql + ","
        return sql

    def writeIndexes(self, currTabl):

        # Write index definition
        nIdx = -1
        for idx in currTabl.Indexes:
            nIdx = nIdx + 1
            idxName = idx.Name
            tablName = currTabl.Name
            if idx.Primary:
                idxName = tablName + "_PK"
            elif idxName[:9] == "REFERENCE":
               idxName = tablName + "_FK" + idxName[10:]
            else:
                idxName = tablName + "_IX" + str(nIdx)

            sql = "create "
            if idx.Unique: sql = sql + "unique "
            if idx.Clustered: sql = sql + "clustered "
            sql = sql + "index " + chr(34) + idxName + chr(34)
            sql = sql + " on " + chr(34) + tablName + chr(34) + " ("

            # Write Index Columns
            cn=0
            for col in idx.Fields:
                cn = cn + 1
                sql = sql + chr(34) + col.Name + chr(34)
                if col.Attributes & const.dbDescending:
                    sql = sql + " desc"
                else:
                    sql = sql + " asc"
                if idx.Fields.Count > cn: sql = sql + ","

            sql=sql + " );"

            self.writeLine(sql,"",1)
        return

    def writeForeignKey(self, currRefr):

        # Export foreign key
        sql = "\nalter table " + chr(34) + currRefr.ForeignTable + chr(34)
        self.writeLine(sql,"",1)

        sql = "    add foreign key ("
        cn = 0
        for col in currRefr.Fields:
            cn = cn + 1
            sql = sql + chr(34) + col.ForeignName + chr(34)
            if currRefr.Fields.Count > cn: sql = sql + ","
            
        sql = sql + ")"
        self.writeLine(sql,"",1)

        sql = "    references " + chr(34) + currRefr.Table + chr(34) + " ("
        cn = 0
        for col in currRefr.Fields:
            cn = cn + 1
            sql = sql + chr(34) + col.Name + chr(34)
            if currRefr.Fields.Count > cn: sql = sql + ","

        sql = sql + ")"
        if (currRefr.Attributes & const.dbRelationUpdateCascade) <> 0:
           sql = sql + " on update cascade"
        if (currRefr.Attributes & const.dbRelationDeleteCascade) <> 0:
           sql = sql + " on delete cascade"
        sql=sql+";"
        self.writeLine(sql,"",1)

        return

    def writeQuery(self, currQry):

        sql = "\ncreate view " + chr(34) + currQry.Name + chr(34) + " as"
        self.writeLine(sql,"",1)

        # Write Query text
        sql=string.replace(currQry.SQL,chr(13),"") # Get rid of extra linefeeds
        self.writeLine(sql,"",1)

        # Write Query comment
        try: sql = currQry.Properties("Description").Value
        except pythoncom.com_error: sql=""
        if sql <> "":
            sql =  "comment on table " + chr(34) + currQry.Name + chr(34) + " is " + chr(34) + sql + chr(34)
            self.writeLine(sql,"",1)
            
        return

    def writeLine(self,strLine, delimit, newline):
        # Used for controlling where lines terminate with a comma or other continuation mark
        sqlfile.write(strLine)
        if delimit: sqlfile.write(delimit)
        if newline: sqlfile.write('\n')
        return


if __name__ == '__main__':
    if len(sys.argv)<2:
        print "Usage: jet2sql.py infile.mdb outfile.sql"
    else:
        jetEng = jetReverse(sys.argv[1])
        outfile = sys.argv[2]

        sqlfile = open(outfile,'w')

        print "\nReverse engineering %s to %s" % (jetEng.jetfilename, outfile)

        # Tables
        sys.stdout.write("\n   Tables")
        for tabl in jetEng.dtbs.TableDefs:
            sys.stdout.write(".")
            if tabl.Name[:4] <> "MSys" and tabl.Name[:4] <> "~TMP":
                jetEng.writeTable(tabl)

        # Relations / FKs
        sys.stdout.write("\n   Relations")
        for fk in jetEng.dtbs.Relations:
            sys.stdout.write(".")
            jetEng.writeForeignKey(fk)

        # Queries
        sys.stdout.write("\n   Queries")
        for qry in jetEng.dtbs.QueryDefs:
            sys.stdout.write(".")
            jetEng.writeQuery(qry)

        print "\n   Done\n"
        
        # Done
        sqlfile.close()
        jetEng.terminate()

12 comments

Michael Strasser 23 years, 1 month ago  # | flag

Can you distinguish between CHAR and VARCHAR fields? Wow! What a great tool!

Is it possible to distinguish between char and varchar fields? In Access the former are fixed length and values are padded with spaces. Access creates variable length text fields by default and there appears to be no way to instruct it otherwise (using Access itself).

It is possible to distinguish between fixed- and variable-length text fields via ODBC (ColdFusion Studio's ODBC browser can do it) so I guess you can get that information via ADO. Otherwise it is probably better to generate varchar fields instead of char ones. Michael Strasser

MG Foster 23 years ago  # | flag

Errors in "Reverse engineer MS Access/Jet databases" Carp { I tried this class on a DAO 3.51 db (yes I changed the Dispatch() method to use the correct engine) & it failed on in the writeColumn() function. The constant dbByte was not recognized. Since this is the first "if" comparison in the function, my guess is that the other constants also do not map. Using the PythonWin debugger I could not access the object "const." My guess is the "const" object is not accessing the DAO constants (of which, dbByte is one). } MG Foster

Matt Keranen (author) 23 years ago  # | flag

Re: Errors in "Reverse engineer MS Access/Jet databases", MG Foster, 2001/04/02. Not sure what is wrong, but I have successfully run this script under DAO 3.51 and 4.0, without errors. I repeated a test with DAO 3.51 on a Jet DB created with Access97, to be certain.

MG Foster 22 years, 11 months ago  # | flag

MG's fix to his Carp. I did get it to work. There has to be a Python byte code library of the DAO library. If you have PythonWin open it & make sure you don't have any scripts in the edit window. From the main menu, select Tools > "COM Makepy Utility." When the "Select Library" dialog box opens, look for the DAO library you want to make into a Python byte code library & click it (it should be like "Microsoft DAO 3.5 Library"). After this is accomplished the "Reverse Engineering" script worked.

Tim Keating 22 years, 7 months ago  # | flag

Another (more elegant?) solution. You can also solve this problem by adding the following snippet of code:

# Use these commands in Python code to auto generate .py support
from win32com.client import gencache
gencache.EnsureModule('{00025E01-0000-0000-C000-000000000046}', 0, 5, 0)
Seth Grimes 20 years, 9 months ago  # | flag

jet2sql.py problem -- AttributeError: dbByte.

    I tried the jet2sql.py program at

http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/52267 and got the error --

C:\NASS\DATA>c:\python22\python jet2sql.py ag_co_co.mdb ag_co_co.sql

Reverse engineering ag_co_co.mdb to ag_co_co.sql

Tables.Traceback (most recent call last):

File "jet2sql.py", line 247, in ? jetEng.writeTable(tabl)

File "jet2sql.py", line 34, in writeTable

self.writeColumn(col.Name, col.Type, col.Size, col.Required,

col.Attributes, col.DefaultValue, col.ValidationRule, currTabl.Fields.Count-cn)

File "jet2sql.py", line 76, in writeColumn

if colType == const.dbByte: dataType = "Byte"

File "C:\Python22\lib\site-packages\win32com\client__init__.py", line 168, in __getattr__

raise AttributeError, a

AttributeError: dbByte

    Any ideas?



    Thanks,



                                            Seth
Russell Fine 20 years, 8 months ago  # | flag

I had the same problem... But it was solved by using makepy to generate class information for the database engine that I was using (DAO.DBEngine.36).

kari Ujanen 19 years, 1 month ago  # | flag

UnicodeEncodeError: 'ascii' codec can't encode character. This is a fabulous utility.

One enhancement on some applications though ...

Sample Error Message:


Tables.Traceback (most recent call last):

File "jet2sql.py", line 247, in ?

jetEng.writeTable(tabl)

File "jet2sql.py", line 66, in writeTable

self.writeLine(sql,"",1)

File "jet2sql.py", line 225, in writeLine

sqlfile.write(strLine)

UnicodeEncodeError: 'ascii' codec can't encode character '\u03bc'in position 52: ordinal not in range(128)


unicode 03bc is small greek letter mu

The Fix after much futzing around was:


changed line 66

from:

self.writeLine(sql,"",1)

to:

self.writeLine(sql.encode('UTF-8'),"",1)


Worked beautifully

kari Ujanen 19 years, 1 month ago  # | flag

Same thing DAO 3.6 COM not configured. getting DAO 3.6 working with python 2.4 on Windows XP SP2


Had to get pythonWin (pyWin32) working first

Downloaded from

https://sourceforge.net/projects/pywin32/

executed the .msi

had a complaint about mfc71.dll

download mfc71.dll from

http://starship.python.net/crew/mhammond/downloads/mfc71.dll

stuck it in Windows/System32


now for DA0 3.6

from Mark Hammonds superlative extensions

PythonWin ->tools -> COM makepy utility

-> from "Select Library" panel scroll down select 'Microsoft DAO 3.6 Object Library (5.0)

click OK

you're off ...

Gleen Aduana 18 years, 1 month ago  # | flag

_QueryDefs.py errors.

I was using this in Windows XP, Python 2.4. First, I was getting this error,
----
   Tables.Traceback (most recent call last):
  File "jet2sql.py", line 247, in ?
    jetEng.writeTable(tabl)
  File "jet2sql.py", line 34, in writeTable
    self.writeColumn(col.Name, col.Type, col.Size, col.Required, col.Attributes,
 col.DefaultValue, col.ValidationRule, currTabl.Fields.Count-cn)
  File "jet2sql.py", line 76, in writeColumn
    if colType == const.dbByte: dataType = "Byte"
  File "C:\Python24\lib\site-packages\win32com\client\__init__.py", line 168, in
 __getattr__
    raise AttributeError, a
AttributeError: dbByte
---

and it was because python didn't know anything about the constant dbByte in the constants
class. I fixed it by using the "COM Makepy Utility" to generate the Microsoft DAO 3.6-specific
Python stub so the named constants will be available.


The "invalid syntax" error came next.

---
 File "C:\jet2sql.py", line 257, in ?
    for qry in jetEng.dtbs.QueryDefs:

    __import__("win32com.gen_py." + dir_name + "." + child)
  File "C:\Python24\lib\site-packages\win32com\gen_py\00025E01-0000-0000-C000-000000000046x0x5x0\_QueryDef.py", line 61
     "LastUpdated": (1610809345, 2, (12, 0), (), "LastUpdated", None),
        "MaxRecords": (1610809375, 2, (3, 0), (), "MaxRecords", None),
                                                                      ^
 SyntaxError: invalid syntax
---

and that was fixed by commenting out the "LastUpdated" line in _QueryDef.py.
Then yet another "invalid syntax" error,

---

  File "C:\Python24\lib\site-packages\win32com\gen_py\00025E01-0000-0000-C000-000000000046x0x5x0\_QueryDef.py", line 140
     (('Updatable', 'pb'), 1610809353, (1610809353, (), [(16395, 10, None, None)], 1, 2, 4, 0, 68, (3, 0, None, None), 0)),
    (('Connect', 'pbstr'), 1610809354, (1610809354, (), [(16392, 10, None, None)], 1, 2, 4, 0, 72, (3, 0, None, None), 0)),
                                                                                                                           ^
 SyntaxError: invalid syntax
---

which was fixed by commenting out the "Updatable" line again in _QueryDef.py

Overall, this is a very useful tool.
Matt Keranen (author) 17 years, 3 months ago  # | flag

MakePy necessary. I have not used this script in quite some time, as I have not used Access/Jet databases much in the part few years. After a recent conversation, I checked the comments on this recipe, and was plesantly surprised that someone found it useful within the year!

Testing the script on Python 2.5 with pywin build 209.1 on WinXPsp2, I find the script still works with out modification. The one step that is necessary is to run the COM Makepy utility (part of Mark Hammond's excellent Win32 extensions) against the DAO 3.6 library.

After noting the recent comments, I was going to update the script to use ADO, but decided if the DAO version still runs as-is, why reinvent the wheel.

india mcq 15 years, 1 month ago  # | flag

For my fellow new users, who need more details.

  1. Download ActiveState Python and install.
  2. Open "PythonWin Editor" from the newly created "Start / Program / ActiveState ActivePython" Start menu item.
  3. In the tools menu find "COM Makepy utility" then select "Microsoft DAO 3.6 Object Library (5.0)" (At least that is what mine had).
  4. Then open a cmd window. I use [Windows Key]+r then type "cmd" [enter].
  5. Change to the correct directory. i.e. "cd c:\work\database"
  6. Enter the command line "python jet2sql.py infile.mdb outfile.sql" hit [enter] Note: Entering the same line in the PythonWin interactive window did not work for me. I got "invalid syntax" errors.

Thanks to Matt for this tool.