Trouble w/ 'create table' sql on mx.ODBC

[Steve Holden]

Greg Corradini wrote:
> Hello,
> Lately I've been using the mx.ODBC module to query Access (mdb) tables. For
> the life of me, I can't get the 'create table' sql command to work. I use
> this command in Oracle and I've seen other mx.ODBC users weave into their
> scripts for Access. But I still can't get this simple test run below to work
> (ignore the geoprocessor object for ArcGIS):
> 
> #Import Python Standard Library Modules
> import win32com.client, sys, os, string, copy, glob
> import mx.ODBC.Windows as odbc
> 
> # Create the Geoprocessor Object
> gp = win32com.client.Dispatch("esriGeoprocessing.GpDispatch.1")
> gp.overwriteoutput = 1
> 
> # Variables
> tempspace = "C:\Documents and Settings\corr1gre\Desktop\Workspace\DBFs &
> Shapefiles\TEST.mdb" 
> workspace = string.replace(tempspace,"\\","/")
>                            
> #Conection to Access
> driv = 'DRIVER={Microsoft Access Driver (*.mdb)};DBQ='+workspace
> conn = odbc.DriverConnect(driv)
> curse = conn.cursor()
> 
> #Upload Tbl 1 as a List of Tuples
> curse.execute('SELECT TISCODE,EXISTSIN,STATUS,NOTES from Mower_I')
> x = curse.fetchall()
> 
> #Create New Table
> curse.execute('Create table TEST (TISCODE Integer)')
> curse.close()
> conn.close()
> del gp

So what happens? Do you see a traceback, or does the program complete 
without any apparent change to the database?

It might be because Access doesn't automatically commit DDL changes like 
some other databases do. Try adding a conn.commit() before your 
conn.close().

regards
  Steve
-- 
Steve Holden       +44 150 684 7255  +1 800 494 3119
Holden Web LLC/Ltd          http://www.holdenweb.com
Skype: holdenweb     http://del.icio.us/steve.holden
Recent Ramblings       http://holdenweb.blogspot.com