Add Column

[Guest]

Can anyone help with this statement which is not working?

Set dbs = OpenDatabase("c:\db1.mdb")

dbs.Execute "ALTER TABLE [tblAL] " _
& "ADD COLUMN DF DATETIME(Short Date);"

I am trying to add a DATETIME column in Short Date format but not sure of
syntax for the Short Date format.

If it was a Text column of lenght 100 then it would be simply be Text(100)

 

[Douglas J Steele]

dbs.Execute "ALTER TABLE [tblAL] " _
& "ADD COLUMN DF DATE"

You can't set formatting through DDL.

 

[Guest]

Thanks - On a similar note - for an existing Text field, how can you specify
in DDL that Zero Length is allowed - I guess :-

dbs.Execute "ALTER TABLE [tblAL] " _
& "ALTER COLUMN DF NULL;"

or do I need to drop then add?

dbs.Execute "ALTER TABLE [tblAL] " _
& "ADD COLUMN DF DATE"

You can't set formatting through DDL.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Can anyone help with this statement which is not working?

Set dbs = OpenDatabase("c:\db1.mdb")

dbs.Execute "ALTER TABLE [tblAL] " _
& "ADD COLUMN DF DATETIME(Short Date);"

I am trying to add a DATETIME column in Short Date format but not sure of
syntax for the Short Date format.

If it was a Text column of lenght 100 then it would be simply be Text(100)

 

[Douglas J Steele]

I think I mistyped the previous reply: it should be DATETIME, not DATE.

With respect to Zero Length and Null, they aren't the same thing. In fact,
if you allow Zero Length, I don't believe it's possible to also allow Null.

There are certain things that can't be done using DDL. If memory serves,
this is one of those things.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks - On a similar note - for an existing Text field, how can you specify
in DDL that Zero Length is allowed - I guess :-

dbs.Execute "ALTER TABLE [tblAL] " _
& "ALTER COLUMN DF NULL;"

or do I need to drop then add?

dbs.Execute "ALTER TABLE [tblAL] " _
& "ADD COLUMN DF DATE"

You can't set formatting through DDL.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Can anyone help with this statement which is not working?

Set dbs = OpenDatabase("c:\db1.mdb")

dbs.Execute "ALTER TABLE [tblAL] " _
& "ADD COLUMN DF DATETIME(Short Date);"

I am trying to add a DATETIME column in Short Date format but not sure of
syntax for the Short Date format.

If it was a Text column of lenght 100 then it would be simply be Text(100)

 

[Guest]

I would consider using DAO to modify your table definitions. By using DAO you
have the ability to modify all the properties (although some you need to add
programatically to the field if they have not been modified through the user
interfacee - "Format" is one of them). Here is how I would add a "Short
Date" field to a table:

Public Function AddDateColumn()

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

'Point to the database
Set db = OpenDatabase("C:\db1.mdb")

With db.TableDefs("tblAL")

'Create a field
Set fld = .CreateField("DF", dbDate)

'Add the field to the fields collection
.Fields.Append fld

'Create a property
Set prp = fld.CreateProperty("Format", dbText, "Short Date")

'Add the property to the field
fld.Properties.Append prp

End With

'Clean up
Set prp = Nothing
Set fld = Nothing
Set db = Nothing

End Function

You can use this same framework to modify the AllowZeroLength (property does
NOT need to be added) and whether or not a it is Required (property does NOT
need to be added)

Here is a block of code that will print all the fields, properties, and
property values of a table:

Public Function ListFieldProperities()

Dim fld As DAO.Field
Dim prp As DAO.Property
Dim db As DAO.Database

Set db = CurrentDB 'OpenDatabase("C:\db1.mdb")

With db.TableDefs("tblMyTable")

Debug.Print "Field Name"
Debug.Print " Property" & " / Type" & " / Value"
Debug.Print " ----------------------------"
For Each fld In .Fields
Debug.Print " ": Debug.Print fld.Name: Debug.Print " "
On Error Resume Next
For Each prp In fld.Properties
Debug.Print " " & prp.Name & " / " & prp.Type & " / " &
prp.Value
Next
On Error GoTo 0
Next

End With

End Function

Hope this helps you out!
Brent
datAdrenaline