[python-win32] Copying a MS Access Table

[Mike Driscoll]

Tim Golden wrote:
> <div class="moz-text-flowed" style="font-family: -moz-fixed">[copying 
> back to the list]
>
>> Not sure how to reply to the thread, since I thought that if I reply 
>> to "python-win32 at python.org" then it would start a new thread? 
>
> Replying to python-win32 at python.org is the right thing to
> do; the mailing list software should recognise all the
> clues it needs to continue the thread.
>
>
> sebastian.venus at uk.bnpparibas.com wrote:
>> Hi Tim,
>>
>> I tried this:
>>>>> a = win32com.client.Dispatch("access.application")
>>>>> connStr = 'Driver={PostgreSQL 
>> Unicode};Server=%s;Database=%s;Uid=%s;Pwd=%s;ConnSettings="set 
>> TimeZone to -8; set search_path to pid,public;"'
>
> Well that surprised me: you're using Access as a frontend
> to PostgreSQL?
>>>>> a.OpenCurrentDatabase(r'C:/TEMP/db1.mdb')
>>>>> a.DoCmd.CopyObject(connStr,'MyTable2',0,'MyTable1')
>>
>> but it threw an exception that I couldn't interpret. Can you please 
>> help? 
>
> This is obviously tricky with two different databases in the equation, 
> one of them PostgreSQL, but what will be
> most helpful is for you to produce an easily reproducible
> sample which someone else can run, plus the traceback you
> get. Going through this exercise may even cause you to
> track down the problem yourself. In particular, try to
> eliminate external dependencies: try to do the copy table
> thing between two Access databases, or within the same
> one (if that's possible; I haven't used Access in earnest
> for a while now).
>
> TJG
>
>

This may be a stupid idea, but I've used SqlAlchemy to get information 
out of Access before. Since SqlAlchemy is database-agnostic, you should 
be able to grab the info from Access, connect to another database with a 
pre-created matched db schema and just tell SqlAlchemy to put the data 
into it. For  the most part, it can translate db differences such that 
this will work without any (or few) issues.

Note that SqlAlchemy's support of Access is experimental. See the 
following thread: 
http://www.mail-archive.com/sqlalchemy@googlegroups.com/msg06112.html

- Mike

Mike