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

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

.

Relevant Pages

  • Re: How to protect Python source from modification
    ... They can then execute any arbitrary SQL command. ... >>SQL commands on their own database, who are you to tell them they ...
    (comp.lang.python)
  • Re: SQL commad each time I open MS Word
    ... Opening this document will run the following SQL command: ... information from a database -for example, if your document is a form letter ...
    (microsoft.public.windowsxp.help_and_support)
  • Re: Database searching via asp/vbscript
    ... For your SQL command check out the LIKE command ... > I've created a database that holds information about a netowrk. ... > holds all the computers details such as hardware installed (including ... > administrator user. ...
    (microsoft.public.scripting.vbscript)
  • Re: Problem with "while/fetchrow_array"
    ... The first returns the last database in the known world as a row. ... I'm not sure if mysql has a mysql-only sql command you can give to get ... and execute it without any error ...
    (perl.dbi.users)
  • Trouble w/ create table sql on mx.ODBC
    ... the life of me, I can't get the 'create table' sql command to work. ... (ignore the geoprocessor object for ArcGIS): ... Sent from the Python - python-list mailing list archive at Nabble.com. ...
    (comp.lang.python)