Rename field in Access DB

[Iain King]

On May 14, 4:29 pm, Tim Golden <m... at timgolden.me.uk> wrote:
> 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


Excellent, many thanks.

Iain