Rename field in Access DB

[Tim Golden]

Iain King wrote:
> I'm manipulating an MS Access db via ADODB with win32com.client.  I
> want to rename a field within a table, but I don't know how to.  I
> assume there is a line of SQL which will do it, but nothing I've tried
> (from searching) has worked.
> Basic code:
> 
> import win32com.client
> connection = win32com.client.Dispatch(r'ADODB.Connection')
> DSN = 'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=dbfile.mdb;'
> connection.Open(DSN)
> connection.Execute("ALTER TABLE tablename CHANGE from to")   #this sql
> doesn't work
> connection.Close()

<code>
import os, sys
from win32com.client.gencache import EnsureDispatch as Dispatch

DATABASE_FILEPATH = r"c:\temp\test.mdb"
CONNECTION_STRING = "Provider=Microsoft.Jet.OLEDB.4.0; data Source=%s" % \
  DATABASE_FILEPATH

if os.path.exists (DATABASE_FILEPATH):
   os.remove (DATABASE_FILEPATH)

adox = Dispatch ("ADOX.Catalog")
adox.Create (CONNECTION_STRING)
adox = None

db = Dispatch ('ADODB.Connection')
db.Open (CONNECTION_STRING)
try:
  db.Execute ('CREATE TABLE dtest (id INT, data INT)')
  db.Execute ('INSERT INTO dtest (id, data) VALUES (1, 2)')

  try:
    db.Execute ('SELECT id, newdata FROM dtest')
  except:
    print "FAILED as expected"
  else:
    print "SUCCEEDED unexpectedly"

  try:
    db.Execute ('SELECT id, data FROM dtest')
  except:
    print "FAILED unexpectedly"
  else:
    print "SUCCEEDED as expected"

  adox = Dispatch ("ADOX.Catalog")
  adox.ActiveConnection = db
  adox.Tables ("dtest").Columns ("data").Name = "newdata"
  adox.Tables.Refresh ()
finally:
  db.Close ()

db = Dispatch ('ADODB.Connection')
db.Open (CONNECTION_STRING)
try:
  
  try:
    db.Execute ('SELECT id, data FROM dtest')
  except:
    print "FAILED as expected"
  else:
    print "SUCCEEDED unexpectedly"

  try:
    db.Execute ('SELECT id, newdata FROM dtest')
  except:
    print "FAILED unexpectedly"
  else:
    print "SUCCEEDED as expected"

finally:
   db.Close ()

</code>

TJG