Rename field in Access DB

[Iain King]

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'ADO DB.Connection')
DSN = 'PROVIDER=Micro soft.Jet.OLEDB. 4.0;DATA SOURCE=dbfile.m db;'
connection.Open (DSN)
connection.Exec ute("ALTER TABLE tablename CHANGE from to") #this sql
doesn't work
connection.Clos e()

Anyone know how to get this to work?

Iain

[Diez B. Roggisch]

Re: Rename field in Access DB

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'ADO DB.Connection')
DSN = 'PROVIDER=Micro soft.Jet.OLEDB. 4.0;DATA SOURCE=dbfile.m db;'
connection.Open (DSN)
connection.Exec ute("ALTER TABLE tablename CHANGE from to") #this sql
doesn't work
connection.Clos e()
>
Anyone know how to get this to work?

I don't think Access supports SQL on the DDL-side. Try looking into the
Access COM-Api, maybe you can get a reference to the table and modify it.

Diez

[Tim Golden]

Re: Rename field in Access DB

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'ADO DB.Connection')
DSN = 'PROVIDER=Micro soft.Jet.OLEDB. 4.0;DATA SOURCE=dbfile.m db;'
connection.Open (DSN)
connection.Exec ute("ALTER TABLE tablename CHANGE from to") #this sql
doesn't work
connection.Clos e()

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

DATABASE_FILEPA TH = r"c:\temp\test. mdb"
CONNECTION_STRI NG = "Provider=Micro soft.Jet.OLEDB. 4.0; data Source=%s" % \
DATABASE_FILEPA TH

if os.path.exists (DATABASE_FILEP ATH):
os.remove (DATABASE_FILEP ATH)

adox = Dispatch ("ADOX.Catalog" )
adox.Create (CONNECTION_STR ING)
adox = None

db = Dispatch ('ADODB.Connect ion')
db.Open (CONNECTION_STR ING)
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.ActiveConn ection = db
adox.Tables ("dtest").Colum ns ("data").Nam e = "newdata"
adox.Tables.Ref resh ()
finally:
db.Close ()

db = Dispatch ('ADODB.Connect ion')
db.Open (CONNECTION_STR ING)
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

[Iain King]

Re: Rename field in Access DB

On May 14, 4:29 pm, Tim Golden <m...@timgolden .me.ukwrote:

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'ADO DB.Connection')
DSN = 'PROVIDER=Micro soft.Jet.OLEDB. 4.0;DATA SOURCE=dbfile.m db;'
connection.Open (DSN)
connection.Exec ute("ALTER TABLE tablename CHANGE from to") #this sql
doesn't work
connection.Clos e()

>
<code>
import os, sys
from win32com.client .gencache import EnsureDispatch as Dispatch
>
DATABASE_FILEPA TH = r"c:\temp\test. mdb"
CONNECTION_STRI NG = "Provider=Micro soft.Jet.OLEDB. 4.0; data Source=%s" % \
DATABASE_FILEPA TH
>
if os.path.exists (DATABASE_FILEP ATH):
os.remove (DATABASE_FILEP ATH)
>
adox = Dispatch ("ADOX.Catalog" )
adox.Create (CONNECTION_STR ING)
adox = None
>
db = Dispatch ('ADODB.Connect ion')
db.Open (CONNECTION_STR ING)
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.ActiveConn ection = db
adox.Tables ("dtest").Colum ns ("data").Nam e = "newdata"
adox.Tables.Ref resh ()
finally:
db.Close ()
>
db = Dispatch ('ADODB.Connect ion')
db.Open (CONNECTION_STR ING)
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