Path: csiph.com!usenet.pasdenom.info!weretis.net!feeder1.news.weretis.net!feeder.erje.net!eu.feeder.erje.net!xlned.com!feeder7.xlned.com!news2.euro.net!newsgate.cistron.nl!newsgate.news.xs4all.nl!post.news.xs4all.nl!not-for-mail
Return-Path: <torriem@gmail.com>
X-Original-To: python-list@python.org
Delivered-To: python-list@mail.python.org
X-Spam-Status: OK 0.001
X-Spam-Evidence: '*H*': 1.00; '*S*': 0.00; 'schema': 0.05; 'implements': 0.07; 'postgresql': 0.07; 'properly.': 0.07; 'api': 0.09; 'python': 0.09; 'pyodbc': 0.09; 'sql,': 0.09; 'file,': 0.15; 'abi': 0.16; 'conn': 0.16; 'from:addr:torriem': 0.16; 'from:name:michael torrie': 0.16; 'pwd': 0.16; 'syntax,': 0.16; 'wrote:': 0.17; 'driver': 0.17; 'windows': 0.19; 'insert': 0.23; 'least': 0.25; 'header:In-Reply-To:1': 0.25; 'header:User- Agent:1': 0.26; 'looks': 0.26; 'wrote': 0.26; 'am,': 0.27; 'compiled': 0.27; 'structures': 0.27; 'run': 0.28; 'installed,': 0.29; 'convert': 0.29; 'file': 0.32; "skip:' 20": 0.32; 'handle': 0.33; 'to:addr:python-list': 0.33; "can't": 0.34; 'received:org': 0.36; 'but': 0.36; 'message-id:@gmail.com': 0.36; 'data.': 0.36; 'data': 0.37; 'subject:: ': 0.38; 'to:addr:python.org': 0.39; 'received:192': 0.39; 'received:192.168': 0.40; 'header:Received:5': 0.40; 'easy': 0.60; 'skip:u 10': 0.60; 'curs': 0.84; 'reveal': 0.84; 'joel': 0.91
X-Virus-Scanned: amavisd-new at torriefamily.org
Date: Thu, 29 Nov 2012 10:22:56 -0700
From: Michael Torrie <torriem@gmail.com>
User-Agent: Mozilla/5.0 (X11; Linux i686; rv:10.0.11) Gecko/20121115 Thunderbird/10.0.11
MIME-Version: 1.0
To: python-list@python.org
Subject: Re: Migrate from Access 2010 / VBA
References: <6db4ed18-a16e-4cd1-940b-c10f51207780@googlegroups.com> <m2d2yyy4jm.fsf@valheru.db3l.homeip.net> <62fbd558-ac46-4de9-90ca-b099a36b31e2@f17g2000vbz.googlegroups.com> <CAPM-O+zWx7Uj_+z6EHu+R6J17v6gEP1rzKv+J9mPyP_hSPgkZA@mail.gmail.com>
In-Reply-To: <CAPM-O+zWx7Uj_+z6EHu+R6J17v6gEP1rzKv+J9mPyP_hSPgkZA@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-BeenThere: python-list@python.org
X-Mailman-Version: 2.1.15
Precedence: list
List-Id: General discussion list for the Python programming language <python-list.python.org>
List-Unsubscribe: <http://mail.python.org/mailman/options/python-list>, <mailto:python-list-request@python.org?subject=unsubscribe>
List-Archive: <http://mail.python.org/pipermail/python-list/>
List-Post: <mailto:python-list@python.org>
List-Help: <mailto:python-list-request@python.org?subject=help>
List-Subscribe: <http://mail.python.org/mailman/listinfo/python-list>, <mailto:python-list-request@python.org?subject=subscribe>
Newsgroups: comp.lang.python
Message-ID: <mailman.368.1354209790.29569.python-list@python.org>
Lines: 27
NNTP-Posting-Host: 2001:888:2000:d::a6
X-Trace: 1354209790 news.xs4all.nl 6855 [2001:888:2000:d::a6]:44090
X-Complaints-To: abuse@xs4all.nl
Xref: csiph.com comp.lang.python:34073

On 11/29/2012 09:05 AM, Joel Goldstick wrote:
> This looks promising:
> http://www.codediesel.com/data/migrating-access-mdb-to-mysql/

Unfortunately I have not found mdb tools to be sufficient.  You can use
them to convert the schema to sql, and to reveal any mdb password (great
for looking at the data structures of compiled apps), but it can't
handle all the data types properly.  To get data out of an mdb file, I
wrote a simple python program that used pyodbc to get the data.  pyodbc
implements a standard python db api interface.  I opened the access
database file with:


MDB = 'C:/Path/to/frs_or_mdb_file'
DRV = '{Microsoft Access Driver (*.mdb)}'
PWD = 'ifneeded'

conn = pyodbc.connect('DRIVER=%s;DBQ=%s;UID=admin;PWD=%s' % (DRV,MDB,PWD))

curs = conn.cursor()

Then you can run queries with standard python db abi calls in standard
SQL syntax, and it's pretty easy to pull out the data and insert it into
a MySQL or PostgreSQL database.

This is for python on Windows of course, and has to have Access
installed, or at least the access engine.