AnsweredAssumed Answered

Changing the format of a field in an Access table from a python script

Question asked by simpson.ben1990 on Dec 4, 2015
Latest reply on Dec 12, 2015 by bixb0012

Hi,

 

In my python script I have some code which, using a SQL statement copies a table from one Access database to another. In the original database there are several fields which are Yes/No fields. The problem I am having is that when I copy over the table to the new database these fields revert to Access's default boolean code (-1/0). I know how to manually change the fields format in Access but I am struggling to work out how to do it from my python script. I need to get these fields back to displaying Yes/No.

 

I copy the table across using the """SELECT * INTO (table name) IN (new database) FROM (table name) IN (original database)""". I have tried using the FORMAT(table name,'Yes/No') clause in SELECT statements, ALTER TABLE statements and UPDATE statements but with no luck.

 

At this point I am not too sure what I am suppose to do. It is most likely that I am doing something very obvious wrong but I am clueless as to what it is.

 

Thanks in advance for any help,

 

Ben.

 

This is a snippet of my current script:

 

import os
import sys
import arcpy
import pyodbc

common_db_new = [arcpy.GetParameterAsText(0)]
direc = os.path.dirname(__file__)
Broom_db = direc + "\\River_Broom_126.mdb"
Broom_common = direc + "\\River_Broom_126_CommonTables2k.mdb"

Broom_conn = pyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb)};DBQ='+Broom_common)
Broom_cursor = Broom_conn.cursor()
common_conn = pyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb)};DBQ='+common_db_new[0])
common_cursor = common_conn.cursor()

SQL = """SELECT * INTO DeterminandsForSimcat IN '{0}'
FROM DeterminandsForSimcat IN '{1}'""".format(common_db_new[0], Broom_common)
common_cursor.execute(SQL)
common_cursor.commit()

SQL = """SELECT Include
FORMAT(Include,'Yes/No')
FROM DeterminandsForSimcat"""
common_cursor.execute(SQL)
common_cursor.commit()

common_cursor.close()
common_conn.close()
Broom_cursor.close()
Broom_conn.close()

Outcomes