changing column names?

[pdbowling]

Hi all,
Is there an SQL statement that will change the column name of an existing table??? Thanks .
PB

 

[Fornatian]

This code will change the name of field in a table:

Dim dbs As DAO.Database
Dim tdf As TableDef

Set dbs = CurrentDb()
Set tdf = dbs.TableDefs("TableName")

tdf.Fields("OldName").Name = "NewName"

Set tdf = Nothing
Set dbs = Nothing

Note that it will not change the name in any queries, forms,reports or modules which reference the field name explicitly. For that you need a third party tool such as:
http://www.moshannon.com/speedferret.html

 

[pdbowling]

user defined type not defined

It won't compile. I get this error


user defined type not defined.

and the debugger stops here.
Dim dbs As DAO.Database

Any clues?
PB

 

[jfgambit]

Do you have the Microsoft DAO Reference checked?

 

[pdbowling]

Checked?

Well, being the newby I am, I don't know if I have it checked because I don't know where to look to find out. Sorry to be so green in the presence of all you pros.
PB

 

[jfgambit]

Go into the design mode of your Form, click the Code button (the colorful square on the toolbar), under TOOLS > REFERENCES check to see if the Microsoft DAO 3.6 object library is checked, if not check it.

That should do it.

 

[pdbowling]

Yes

Yes, that fixed the CurrentDb() function now it crashes at the line indicated below

Dim dbs As DAO.Database
Dim tdf As TableDef

Set dbs = CurrentDb()
Set tdf = dbs.TableDefs("Ark")

tdf.Fields("DeptCode").Name = "Po_No" <------------- fails

Set tdf = Nothing
Set dbs = Nothing

The error msg is:

"The database engine could not lock table 'Ark' because it is already in use by another person or process."

I'm the only user and it's not open anywhere. Kind of annoying myself now. I hope you have patience with me. Thanks
PB

 

[Fornatian]

This sometimes happens on my machine, close down the db, compact and repair, then try running the code ensuring that all forms and reports are closed. This usually does it, my PC usually says the db is locked by user 'Admin', and I am working on a standalone!