SQL Syntax to rename a field in a Table

[ScardyBob]

Hello all,

I want to rename a field in a table programmatically. I know I can do
this using DAO/ADO, but after doing some searching I found that I could
possibly do it with a SQL statement. I tried the following query:

ALTER TABLE tblname RENAME COLUMN [oldfieldname] TO [newfieldname];

However, I get the error message 'Syntax Error in ALTER TABLE
statement', highlighting the 'RENAME' part of the statement. I was
wondering what is the correct syntax, if this is possible? I have
MSAccess 2003.

Thanks,
Mike

 

[Douglas J. Steele]

AFAIK, it's not possible to rename an existing field using DDL.

Your options include
- use DAO or ADOX to rename the field
- create a new field, populate the new field from the existing field then
drop the original field
- create a query that aliases the field to the new name, and use the query,
rather than the table
- live with the name!

 

[Guest]

I used your DAO solution to rename fields. It works well.
How do I change other field properties, specifically making a field the
primary index, changing it's data type or changing its caption?
--
-CLSWL

My understanding o f the position is the same as Doug's. Here are DAO and
ADOX solutions:

Public Sub RenameColumn_DAO(strTable As String, strOldName As String,
strNewName As String)

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strTable)
Set fld = tdf.Fields(strOldName)

fld.Name = strNewName

End Sub

Public Sub RenameColumn_ADOX(strTable As String, strOldName As String,
strNewName As String)

Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Set cat = New Catalog
cat.ActiveConnection = CurrentProject.Connection

Set tbl = cat(strTable)
tbl.Columns(strOldName) = strNewName

End Sub

Ken Sheridan
Stafford, England

Hello all,

I want to rename a field in a table programmatically. I know I can do
this using DAO/ADO, but after doing some searching I found that I could
possibly do it with a SQL statement. I tried the following query:

ALTER TABLE tblname RENAME COLUMN [oldfieldname] TO [newfieldname];

However, I get the error message 'Syntax Error in ALTER TABLE
statement', highlighting the 'RENAME' part of the statement. I was
wondering what is the correct syntax, if this is possible? I have
MSAccess 2003.

Thanks,
Mike

 

[Guest]

Mike:

As an example of changing a field's property here's some code I came up with
a while ago as an answer when somebody wanted to change the display control
of a Boolean (Yes/No) field to a check box:

Const PROPEXISTS = 3367
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property

Set db = CurrentDb

Set tdf = db.TableDefs("MyTable")
Set fld = tdf.Fields("MyFieldName")

On Error Resume Next
Set prp = fld.CreateProperty("DisplayControl", dbInteger, acCheckBox)
fld.Properties.Append prp
If Err = PROPEXISTS Then
fld.Properties("DisplayControl") = acTextBox
Else
' unknown error
MsgBox Err.Description
End If

To make a column a table's primary key you can use the DDL CREATE INDEX
statement e.g.

CREATE INDEX PrimaryKey
ON MyTable (MyID) WITH Primary;

Similarly to alter a column you use the DDL ALTER TABLE statement, e.g.

ALTER TABLE MyTable
ALTER COLUMN MyNumber DOUBLE;

Ken Sheridan
Stafford, England

I used your DAO solution to rename fields. It works well.
How do I change other field properties, specifically making a field the
primary index, changing it's data type or changing its caption?
--
-CLSWL

My understanding o f the position is the same as Doug's. Here are DAO and
ADOX solutions:

Public Sub RenameColumn_DAO(strTable As String, strOldName As String,
strNewName As String)

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strTable)
Set fld = tdf.Fields(strOldName)

fld.Name = strNewName

End Sub

Public Sub RenameColumn_ADOX(strTable As String, strOldName As String,
strNewName As String)

Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Set cat = New Catalog
cat.ActiveConnection = CurrentProject.Connection

Set tbl = cat(strTable)
tbl.Columns(strOldName) = strNewName

End Sub

Ken Sheridan
Stafford, England

Hello all,

I want to rename a field in a table programmatically. I know I can do
this using DAO/ADO, but after doing some searching I found that I could
possibly do it with a SQL statement. I tried the following query:

ALTER TABLE tblname RENAME COLUMN [oldfieldname] TO [newfieldname];

However, I get the error message 'Syntax Error in ALTER TABLE
statement', highlighting the 'RENAME' part of the statement. I was
wondering what is the correct syntax, if this is possible? I have
MSAccess 2003.

Thanks,
Mike

 

[aaron.kempf]

don't ****ing use DAO and don't listen to these newbies

alter table
alter column

use plain old sql or STFU and go get a new job

-Aaron

 

[Guest]

So I understood the 1st half of that which was VB.

What is the rest of it?

CREATE INDEX PrimaryKey
ON MyTable (MyID) WITH Primary;

Similarly to alter a column you use the DDL ALTER TABLE statement, e.g.

ALTER TABLE MyTable
ALTER COLUMN MyNumber DOUBLE;

I have no idea where that goes or how to use it.

Specifically, I don't know how to change the code in order to alter a
field's datatype. I have tried the following:

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strTable)

Set fld = tdf.CreateField(strFieldName, dbDate)
tdf.Fields.Append fld


It works if I was to create a new field but I don't. I want to change an
existing field.

I also don't know how to adapt the code to make a field the primary key.


--
-CLSWL

Mike:

As an example of changing a field's property here's some code I came up with
a while ago as an answer when somebody wanted to change the display control
of a Boolean (Yes/No) field to a check box:

Const PROPEXISTS = 3367
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property

Set db = CurrentDb

Set tdf = db.TableDefs("MyTable")
Set fld = tdf.Fields("MyFieldName")

On Error Resume Next
Set prp = fld.CreateProperty("DisplayControl", dbInteger, acCheckBox)
fld.Properties.Append prp
If Err = PROPEXISTS Then
fld.Properties("DisplayControl") = acTextBox
Else
' unknown error
MsgBox Err.Description
End If

To make a column a table's primary key you can use the DDL CREATE INDEX
statement e.g.

CREATE INDEX PrimaryKey
ON MyTable (MyID) WITH Primary;

Similarly to alter a column you use the DDL ALTER TABLE statement, e.g.

ALTER TABLE MyTable
ALTER COLUMN MyNumber DOUBLE;

Ken Sheridan
Stafford, England

I used your DAO solution to rename fields. It works well.
How do I change other field properties, specifically making a field the
primary index, changing it's data type or changing its caption?
--
-CLSWL

My understanding o f the position is the same as Doug's. Here are DAO and
ADOX solutions:

Public Sub RenameColumn_DAO(strTable As String, strOldName As String,
strNewName As String)

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strTable)
Set fld = tdf.Fields(strOldName)

fld.Name = strNewName

End Sub

Public Sub RenameColumn_ADOX(strTable As String, strOldName As String,
strNewName As String)

Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Set cat = New Catalog
cat.ActiveConnection = CurrentProject.Connection

Set tbl = cat(strTable)
tbl.Columns(strOldName) = strNewName

End Sub

Ken Sheridan
Stafford, England

:

Hello all,

I want to rename a field in a table programmatically. I know I can do
this using DAO/ADO, but after doing some searching I found that I could
possibly do it with a SQL statement. I tried the following query:

ALTER TABLE tblname RENAME COLUMN [oldfieldname] TO [newfieldname];

However, I get the error message 'Syntax Error in ALTER TABLE
statement', highlighting the 'RENAME' part of the statement. I was
wondering what is the correct syntax, if this is possible? I have
MSAccess 2003.

Thanks,
Mike

 

[Guest]

Its DDL, which is the Data Definition Language part of SQL. You simply enter
it into a query in SQL view and run it. Or if you want to do it in code you
can Execute the SQL statement with DAO or ADO. If you use ADO you need to
create a reference to the Microsoft ADO Extensions for DDL and Security
object library first (Tools | References on the VBA menu bar). You'll find
details of the SQL data types in the Help topic for Microsoft Jet SQL
Reference.

Ken Sheridan
Stafford, England

So I understood the 1st half of that which was VB.

What is the rest of it?

CREATE INDEX PrimaryKey
ON MyTable (MyID) WITH Primary;

Similarly to alter a column you use the DDL ALTER TABLE statement, e.g.

ALTER TABLE MyTable
ALTER COLUMN MyNumber DOUBLE;

I have no idea where that goes or how to use it.

Specifically, I don't know how to change the code in order to alter a
field's datatype. I have tried the following:

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strTable)

Set fld = tdf.CreateField(strFieldName, dbDate)
tdf.Fields.Append fld


It works if I was to create a new field but I don't. I want to change an
existing field.

I also don't know how to adapt the code to make a field the primary key.


--
-CLSWL

Mike:

As an example of changing a field's property here's some code I came up with
a while ago as an answer when somebody wanted to change the display control
of a Boolean (Yes/No) field to a check box:

Const PROPEXISTS = 3367
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property

Set db = CurrentDb

Set tdf = db.TableDefs("MyTable")
Set fld = tdf.Fields("MyFieldName")

On Error Resume Next
Set prp = fld.CreateProperty("DisplayControl", dbInteger, acCheckBox)
fld.Properties.Append prp
If Err = PROPEXISTS Then
fld.Properties("DisplayControl") = acTextBox
Else
' unknown error
MsgBox Err.Description
End If

To make a column a table's primary key you can use the DDL CREATE INDEX
statement e.g.

CREATE INDEX PrimaryKey
ON MyTable (MyID) WITH Primary;

Similarly to alter a column you use the DDL ALTER TABLE statement, e.g.

ALTER TABLE MyTable
ALTER COLUMN MyNumber DOUBLE;

Ken Sheridan
Stafford, England

I used your DAO solution to rename fields. It works well.
How do I change other field properties, specifically making a field the
primary index, changing it's data type or changing its caption?
--
-CLSWL


:

My understanding o f the position is the same as Doug's. Here are DAO and
ADOX solutions:

Public Sub RenameColumn_DAO(strTable As String, strOldName As String,
strNewName As String)

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strTable)
Set fld = tdf.Fields(strOldName)

fld.Name = strNewName

End Sub

Public Sub RenameColumn_ADOX(strTable As String, strOldName As String,
strNewName As String)

Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Set cat = New Catalog
cat.ActiveConnection = CurrentProject.Connection

Set tbl = cat(strTable)
tbl.Columns(strOldName) = strNewName

End Sub

Ken Sheridan
Stafford, England

:

Hello all,

I want to rename a field in a table programmatically. I know I can do
this using DAO/ADO, but after doing some searching I found that I could
possibly do it with a SQL statement. I tried the following query:

ALTER TABLE tblname RENAME COLUMN [oldfieldname] TO [newfieldname];

However, I get the error message 'Syntax Error in ALTER TABLE
statement', highlighting the 'RENAME' part of the statement. I was
wondering what is the correct syntax, if this is possible? I have
MSAccess 2003.

Thanks,
Mike

 

[Guest]

This is what I have so far. The inicated line is causing the error.

Function MakeKey_DAO(strTable As String, strFieldName As String)
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim ind As DAO.Index

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strTable)
Set fld = tdf.Fields(strFieldName)


Set ind = tdf.CreateIndex("indNew")
--> ind.Fields.Append fld
ind.Primary = True
tdf.Indexes.Append ind

End Function

Run time error 3367
Cannot append. An object with that name already exist in the collection.

--
-CLSWL

Its DDL, which is the Data Definition Language part of SQL. You simply enter
it into a query in SQL view and run it. Or if you want to do it in code you
can Execute the SQL statement with DAO or ADO. If you use ADO you need to
create a reference to the Microsoft ADO Extensions for DDL and Security
object library first (Tools | References on the VBA menu bar). You'll find
details of the SQL data types in the Help topic for Microsoft Jet SQL
Reference.

Ken Sheridan
Stafford, England

So I understood the 1st half of that which was VB.

What is the rest of it?

CREATE INDEX PrimaryKey
ON MyTable (MyID) WITH Primary;

Similarly to alter a column you use the DDL ALTER TABLE statement, e.g.

ALTER TABLE MyTable
ALTER COLUMN MyNumber DOUBLE;

I have no idea where that goes or how to use it.

Specifically, I don't know how to change the code in order to alter a
field's datatype. I have tried the following:

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strTable)

Set fld = tdf.CreateField(strFieldName, dbDate)
tdf.Fields.Append fld


It works if I was to create a new field but I don't. I want to change an
existing field.

I also don't know how to adapt the code to make a field the primary key.


--
-CLSWL

Mike:

As an example of changing a field's property here's some code I came up with
a while ago as an answer when somebody wanted to change the display control
of a Boolean (Yes/No) field to a check box:

Const PROPEXISTS = 3367
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property

Set db = CurrentDb

Set tdf = db.TableDefs("MyTable")
Set fld = tdf.Fields("MyFieldName")

On Error Resume Next
Set prp = fld.CreateProperty("DisplayControl", dbInteger, acCheckBox)
fld.Properties.Append prp
If Err = PROPEXISTS Then
fld.Properties("DisplayControl") = acTextBox
Else
' unknown error
MsgBox Err.Description
End If

To make a column a table's primary key you can use the DDL CREATE INDEX
statement e.g.

CREATE INDEX PrimaryKey
ON MyTable (MyID) WITH Primary;

Similarly to alter a column you use the DDL ALTER TABLE statement, e.g.

ALTER TABLE MyTable
ALTER COLUMN MyNumber DOUBLE;

Ken Sheridan
Stafford, England

:

I used your DAO solution to rename fields. It works well.
How do I change other field properties, specifically making a field the
primary index, changing it's data type or changing its caption?
--
-CLSWL


:

My understanding o f the position is the same as Doug's. Here are DAO and
ADOX solutions:

Public Sub RenameColumn_DAO(strTable As String, strOldName As String,
strNewName As String)

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strTable)
Set fld = tdf.Fields(strOldName)

fld.Name = strNewName

End Sub

Public Sub RenameColumn_ADOX(strTable As String, strOldName As String,
strNewName As String)

Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Set cat = New Catalog
cat.ActiveConnection = CurrentProject.Connection

Set tbl = cat(strTable)
tbl.Columns(strOldName) = strNewName

End Sub

Ken Sheridan
Stafford, England

:

Hello all,

I want to rename a field in a table programmatically. I know I can do
this using DAO/ADO, but after doing some searching I found that I could
possibly do it with a SQL statement. I tried the following query:

ALTER TABLE tblname RENAME COLUMN [oldfieldname] TO [newfieldname];

However, I get the error message 'Syntax Error in ALTER TABLE
statement', highlighting the 'RENAME' part of the statement. I was
wondering what is the correct syntax, if this is possible? I have
MSAccess 2003.

Thanks,
Mike

 

[Douglas J. Steele]

If indNew already exists, you'll need to delete it first. Once an index has
been added to the TableDef's Indexes collection, the Primary property is
read-only.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

This is what I have so far. The inicated line is causing the error.

Function MakeKey_DAO(strTable As String, strFieldName As String)
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim ind As DAO.Index

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strTable)
Set fld = tdf.Fields(strFieldName)


Set ind = tdf.CreateIndex("indNew")
--> ind.Fields.Append fld
ind.Primary = True
tdf.Indexes.Append ind

End Function

Run time error 3367
Cannot append. An object with that name already exist in the collection.

--
-CLSWL

Its DDL, which is the Data Definition Language part of SQL. You simply
enter
it into a query in SQL view and run it. Or if you want to do it in code
you
can Execute the SQL statement with DAO or ADO. If you use ADO you need
to
create a reference to the Microsoft ADO Extensions for DDL and Security
object library first (Tools | References on the VBA menu bar). You'll
find
details of the SQL data types in the Help topic for Microsoft Jet SQL
Reference.

Ken Sheridan
Stafford, England

So I understood the 1st half of that which was VB.

What is the rest of it?

CREATE INDEX PrimaryKey
ON MyTable (MyID) WITH Primary;

Similarly to alter a column you use the DDL ALTER TABLE statement,
e.g.

ALTER TABLE MyTable
ALTER COLUMN MyNumber DOUBLE;


I have no idea where that goes or how to use it.

Specifically, I don't know how to change the code in order to alter a
field's datatype. I have tried the following:

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strTable)

Set fld = tdf.CreateField(strFieldName, dbDate)
tdf.Fields.Append fld


It works if I was to create a new field but I don't. I want to change
an
existing field.

I also don't know how to adapt the code to make a field the primary
key.


--
-CLSWL


:

Mike:

As an example of changing a field's property here's some code I came
up with
a while ago as an answer when somebody wanted to change the display
control
of a Boolean (Yes/No) field to a check box:

Const PROPEXISTS = 3367
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property

Set db = CurrentDb

Set tdf = db.TableDefs("MyTable")
Set fld = tdf.Fields("MyFieldName")

On Error Resume Next
Set prp = fld.CreateProperty("DisplayControl", dbInteger,
acCheckBox)
fld.Properties.Append prp
If Err = PROPEXISTS Then
fld.Properties("DisplayControl") = acTextBox
Else
' unknown error
MsgBox Err.Description
End If

To make a column a table's primary key you can use the DDL CREATE
INDEX
statement e.g.

CREATE INDEX PrimaryKey
ON MyTable (MyID) WITH Primary;

Similarly to alter a column you use the DDL ALTER TABLE statement,
e.g.

ALTER TABLE MyTable
ALTER COLUMN MyNumber DOUBLE;

Ken Sheridan
Stafford, England

:

I used your DAO solution to rename fields. It works well.
How do I change other field properties, specifically making a field
the
primary index, changing it's data type or changing its caption?
--
-CLSWL


:

My understanding o f the position is the same as Doug's. Here
are DAO and
ADOX solutions:

Public Sub RenameColumn_DAO(strTable As String, strOldName As
String,
strNewName As String)

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strTable)
Set fld = tdf.Fields(strOldName)

fld.Name = strNewName

End Sub

Public Sub RenameColumn_ADOX(strTable As String, strOldName As
String,
strNewName As String)

Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Set cat = New Catalog
cat.ActiveConnection = CurrentProject.Connection

Set tbl = cat(strTable)
tbl.Columns(strOldName) = strNewName

End Sub

Ken Sheridan
Stafford, England

:

Hello all,

I want to rename a field in a table programmatically. I know I
can do
this using DAO/ADO, but after doing some searching I found that
I could
possibly do it with a SQL statement. I tried the following
query:

ALTER TABLE tblname RENAME COLUMN [oldfieldname] TO
[newfieldname];

However, I get the error message 'Syntax Error in ALTER TABLE
statement', highlighting the 'RENAME' part of the statement. I
was
wondering what is the correct syntax, if this is possible? I
have
MSAccess 2003.

Thanks,
Mike

 

[Guest]

No that's not the line that is giving the error, this one is:

ind.Fields.Append fld

This is not the correct syntax. I don't know how to make a field that
ALREADY EXISTS the primary key using VB.



--
-CLSWL

If indNew already exists, you'll need to delete it first. Once an index has
been added to the TableDef's Indexes collection, the Primary property is
read-only.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

This is what I have so far. The inicated line is causing the error.

Function MakeKey_DAO(strTable As String, strFieldName As String)
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim ind As DAO.Index

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strTable)
Set fld = tdf.Fields(strFieldName)


Set ind = tdf.CreateIndex("indNew")
--> ind.Fields.Append fld
ind.Primary = True
tdf.Indexes.Append ind

End Function

Run time error 3367
Cannot append. An object with that name already exist in the collection.

--
-CLSWL

Its DDL, which is the Data Definition Language part of SQL. You simply
enter
it into a query in SQL view and run it. Or if you want to do it in code
you
can Execute the SQL statement with DAO or ADO. If you use ADO you need
to
create a reference to the Microsoft ADO Extensions for DDL and Security
object library first (Tools | References on the VBA menu bar). You'll
find
details of the SQL data types in the Help topic for Microsoft Jet SQL
Reference.

Ken Sheridan
Stafford, England

:

So I understood the 1st half of that which was VB.

What is the rest of it?

CREATE INDEX PrimaryKey
ON MyTable (MyID) WITH Primary;

Similarly to alter a column you use the DDL ALTER TABLE statement,
e.g.

ALTER TABLE MyTable
ALTER COLUMN MyNumber DOUBLE;


I have no idea where that goes or how to use it.

Specifically, I don't know how to change the code in order to alter a
field's datatype. I have tried the following:

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strTable)

Set fld = tdf.CreateField(strFieldName, dbDate)
tdf.Fields.Append fld


It works if I was to create a new field but I don't. I want to change
an
existing field.

I also don't know how to adapt the code to make a field the primary
key.


--
-CLSWL


:

Mike:

As an example of changing a field's property here's some code I came
up with
a while ago as an answer when somebody wanted to change the display
control
of a Boolean (Yes/No) field to a check box:

Const PROPEXISTS = 3367
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property

Set db = CurrentDb

Set tdf = db.TableDefs("MyTable")
Set fld = tdf.Fields("MyFieldName")

On Error Resume Next
Set prp = fld.CreateProperty("DisplayControl", dbInteger,
acCheckBox)
fld.Properties.Append prp
If Err = PROPEXISTS Then
fld.Properties("DisplayControl") = acTextBox
Else
' unknown error
MsgBox Err.Description
End If

To make a column a table's primary key you can use the DDL CREATE
INDEX
statement e.g.

CREATE INDEX PrimaryKey
ON MyTable (MyID) WITH Primary;

Similarly to alter a column you use the DDL ALTER TABLE statement,
e.g.

ALTER TABLE MyTable
ALTER COLUMN MyNumber DOUBLE;

Ken Sheridan
Stafford, England

:

I used your DAO solution to rename fields. It works well.
How do I change other field properties, specifically making a field
the
primary index, changing it's data type or changing its caption?
--
-CLSWL


:

My understanding o f the position is the same as Doug's. Here
are DAO and
ADOX solutions:

Public Sub RenameColumn_DAO(strTable As String, strOldName As
String,
strNewName As String)

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strTable)
Set fld = tdf.Fields(strOldName)

fld.Name = strNewName

End Sub

Public Sub RenameColumn_ADOX(strTable As String, strOldName As
String,
strNewName As String)

Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Set cat = New Catalog
cat.ActiveConnection = CurrentProject.Connection

Set tbl = cat(strTable)
tbl.Columns(strOldName) = strNewName

End Sub

Ken Sheridan
Stafford, England

:

Hello all,

I want to rename a field in a table programmatically. I know I
can do
this using DAO/ADO, but after doing some searching I found that
I could
possibly do it with a SQL statement. I tried the following
query:

ALTER TABLE tblname RENAME COLUMN [oldfieldname] TO
[newfieldname];

However, I get the error message 'Syntax Error in ALTER TABLE
statement', highlighting the 'RENAME' part of the statement. I
was
wondering what is the correct syntax, if this is possible? I
have
MSAccess 2003.

Thanks,
Mike

 

[Guest]

I think you'll find it simpler with DDL:

Function MakeKey_DDL(strTable As String, strFieldName As String)

Dim dbs As DAO.Database
Dim strSQL As String

Set dbs = CurrentDb

strSQL = "CREATE INDEX indNew " & _
"ON " & strTable & "(" & strFieldName & ") WITH Primary"
dbs.Execute strSQL

End Function

Ken Sheridan
Stafford, England

This is what I have so far. The inicated line is causing the error.

Function MakeKey_DAO(strTable As String, strFieldName As String)
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim ind As DAO.Index

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strTable)
Set fld = tdf.Fields(strFieldName)


Set ind = tdf.CreateIndex("indNew")
--> ind.Fields.Append fld
ind.Primary = True
tdf.Indexes.Append ind

End Function

Run time error 3367
Cannot append. An object with that name already exist in the collection.

--
-CLSWL

Its DDL, which is the Data Definition Language part of SQL. You simply enter
it into a query in SQL view and run it. Or if you want to do it in code you
can Execute the SQL statement with DAO or ADO. If you use ADO you need to
create a reference to the Microsoft ADO Extensions for DDL and Security
object library first (Tools | References on the VBA menu bar). You'll find
details of the SQL data types in the Help topic for Microsoft Jet SQL
Reference.

Ken Sheridan
Stafford, England

So I understood the 1st half of that which was VB.

What is the rest of it?

CREATE INDEX PrimaryKey
ON MyTable (MyID) WITH Primary;

Similarly to alter a column you use the DDL ALTER TABLE statement, e.g.

ALTER TABLE MyTable
ALTER COLUMN MyNumber DOUBLE;


I have no idea where that goes or how to use it.

Specifically, I don't know how to change the code in order to alter a
field's datatype. I have tried the following:

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strTable)

Set fld = tdf.CreateField(strFieldName, dbDate)
tdf.Fields.Append fld


It works if I was to create a new field but I don't. I want to change an
existing field.

I also don't know how to adapt the code to make a field the primary key.


--
-CLSWL


:

Mike:

As an example of changing a field's property here's some code I came up with
a while ago as an answer when somebody wanted to change the display control
of a Boolean (Yes/No) field to a check box:

Const PROPEXISTS = 3367
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property

Set db = CurrentDb

Set tdf = db.TableDefs("MyTable")
Set fld = tdf.Fields("MyFieldName")

On Error Resume Next
Set prp = fld.CreateProperty("DisplayControl", dbInteger, acCheckBox)
fld.Properties.Append prp
If Err = PROPEXISTS Then
fld.Properties("DisplayControl") = acTextBox
Else
' unknown error
MsgBox Err.Description
End If

To make a column a table's primary key you can use the DDL CREATE INDEX
statement e.g.

CREATE INDEX PrimaryKey
ON MyTable (MyID) WITH Primary;

Similarly to alter a column you use the DDL ALTER TABLE statement, e.g.

ALTER TABLE MyTable
ALTER COLUMN MyNumber DOUBLE;

Ken Sheridan
Stafford, England

:

I used your DAO solution to rename fields. It works well.
How do I change other field properties, specifically making a field the
primary index, changing it's data type or changing its caption?
--
-CLSWL


:

My understanding o f the position is the same as Doug's. Here are DAO and
ADOX solutions:

Public Sub RenameColumn_DAO(strTable As String, strOldName As String,
strNewName As String)

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strTable)
Set fld = tdf.Fields(strOldName)

fld.Name = strNewName

End Sub

Public Sub RenameColumn_ADOX(strTable As String, strOldName As String,
strNewName As String)

Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Set cat = New Catalog
cat.ActiveConnection = CurrentProject.Connection

Set tbl = cat(strTable)
tbl.Columns(strOldName) = strNewName

End Sub

Ken Sheridan
Stafford, England

:

Hello all,

I want to rename a field in a table programmatically. I know I can do
this using DAO/ADO, but after doing some searching I found that I could
possibly do it with a SQL statement. I tried the following query:

ALTER TABLE tblname RENAME COLUMN [oldfieldname] TO [newfieldname];

However, I get the error message 'Syntax Error in ALTER TABLE
statement', highlighting the 'RENAME' part of the statement. I was
wondering what is the correct syntax, if this is possible? I have
MSAccess 2003.

Thanks,
Mike

 

[Guest]

Thank you, thank you, thank you. That worked perfectly!
And so does this

Function ChangeColumn_DDL(strTable As String, strFieldName As String)

Dim dbs As DAO.Database
Dim strSQL As String

Set dbs = CurrentDb

strSQL = "ALTER TABLE " & strTable & _
" ALTER COLUMN " & strFieldName & " DATETIME"
dbs.Execute strSQL

End Function

--
-CLSWL

I think you'll find it simpler with DDL:

Function MakeKey_DDL(strTable As String, strFieldName As String)

Dim dbs As DAO.Database
Dim strSQL As String

Set dbs = CurrentDb

strSQL = "CREATE INDEX indNew " & _
"ON " & strTable & "(" & strFieldName & ") WITH Primary"
dbs.Execute strSQL

End Function

Ken Sheridan
Stafford, England

This is what I have so far. The inicated line is causing the error.

Function MakeKey_DAO(strTable As String, strFieldName As String)
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim ind As DAO.Index

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strTable)
Set fld = tdf.Fields(strFieldName)


Set ind = tdf.CreateIndex("indNew")
--> ind.Fields.Append fld
ind.Primary = True
tdf.Indexes.Append ind

End Function

Run time error 3367
Cannot append. An object with that name already exist in the collection.

--
-CLSWL

Its DDL, which is the Data Definition Language part of SQL. You simply enter
it into a query in SQL view and run it. Or if you want to do it in code you
can Execute the SQL statement with DAO or ADO. If you use ADO you need to
create a reference to the Microsoft ADO Extensions for DDL and Security
object library first (Tools | References on the VBA menu bar). You'll find
details of the SQL data types in the Help topic for Microsoft Jet SQL
Reference.

Ken Sheridan
Stafford, England

:

So I understood the 1st half of that which was VB.

What is the rest of it?

CREATE INDEX PrimaryKey
ON MyTable (MyID) WITH Primary;

Similarly to alter a column you use the DDL ALTER TABLE statement, e.g.

ALTER TABLE MyTable
ALTER COLUMN MyNumber DOUBLE;


I have no idea where that goes or how to use it.

Specifically, I don't know how to change the code in order to alter a
field's datatype. I have tried the following:

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strTable)

Set fld = tdf.CreateField(strFieldName, dbDate)
tdf.Fields.Append fld


It works if I was to create a new field but I don't. I want to change an
existing field.

I also don't know how to adapt the code to make a field the primary key.


--
-CLSWL


:

Mike:

As an example of changing a field's property here's some code I came up with
a while ago as an answer when somebody wanted to change the display control
of a Boolean (Yes/No) field to a check box:

Const PROPEXISTS = 3367
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property

Set db = CurrentDb

Set tdf = db.TableDefs("MyTable")
Set fld = tdf.Fields("MyFieldName")

On Error Resume Next
Set prp = fld.CreateProperty("DisplayControl", dbInteger, acCheckBox)
fld.Properties.Append prp
If Err = PROPEXISTS Then
fld.Properties("DisplayControl") = acTextBox
Else
' unknown error
MsgBox Err.Description
End If

To make a column a table's primary key you can use the DDL CREATE INDEX
statement e.g.

CREATE INDEX PrimaryKey
ON MyTable (MyID) WITH Primary;

Similarly to alter a column you use the DDL ALTER TABLE statement, e.g.

ALTER TABLE MyTable
ALTER COLUMN MyNumber DOUBLE;

Ken Sheridan
Stafford, England

:

I used your DAO solution to rename fields. It works well.
How do I change other field properties, specifically making a field the
primary index, changing it's data type or changing its caption?
--
-CLSWL


:

My understanding o f the position is the same as Doug's. Here are DAO and
ADOX solutions:

Public Sub RenameColumn_DAO(strTable As String, strOldName As String,
strNewName As String)

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strTable)
Set fld = tdf.Fields(strOldName)

fld.Name = strNewName

End Sub

Public Sub RenameColumn_ADOX(strTable As String, strOldName As String,
strNewName As String)

Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Set cat = New Catalog
cat.ActiveConnection = CurrentProject.Connection

Set tbl = cat(strTable)
tbl.Columns(strOldName) = strNewName

End Sub

Ken Sheridan
Stafford, England

:

Hello all,

I want to rename a field in a table programmatically. I know I can do
this using DAO/ADO, but after doing some searching I found that I could
possibly do it with a SQL statement. I tried the following query:

ALTER TABLE tblname RENAME COLUMN [oldfieldname] TO [newfieldname];

However, I get the error message 'Syntax Error in ALTER TABLE
statement', highlighting the 'RENAME' part of the statement. I was
wondering what is the correct syntax, if this is possible? I have
MSAccess 2003.

Thanks,
Mike