ALTER COLUMN syntax problem when attempting to set a default value

[Janet]

Access 2003 - I need to change an existing column by adding Date() to the
default property but cannot find the correct syntax. Here's one of many
statements I've tried:

VSQL = "ALTER TABLE Classes ALTER COLUMN StartDate SET DEFAULT Date();"

I have to do this from VBA, not by simply changing the design of the table.
Should I be using ADO instead? If so, can anyone point me in the right
direction?

 

[Janet]

Disregard. DAO is the solution - I used:

Set tdf = db.TableDefs("Classes")
tdf("StartDate").DefaultValue = "Date()"
tdf("EndDate").DefaultValue = "Date()"

 

[Jamie Collins]

Access 2003 - I need to change an existing column by adding Date() to the
default property but cannot find the correct syntax. Here's one of many
statements I've tried:

VSQL = "ALTER TABLE Classes ALTER COLUMN StartDate SET DEFAULT Date();"

If your CREATE TABLE looked like this (i.e. omitted the DEFAULT):

CREATE TABLE Classes (StartDate DATETIME NOT NULL);

then you could add the missing DEFAULT like this:

ALTER TABLE Classes ALTER COLUMN StartDate DATETIME DEFAULT DATE() NOT
NULL

Jamie.

--

 

[Richard_doing_2007]

I get a syntax error on the following when executed from CTRL+G (VBA command
window, Access 2003)

Help?
currentdb.Execute("ALTER TABLE LogHeap ALTER COLUMN log_date DATETIME
DEFAULT DATE() NOT NULL")

 

[RoyVidar]

I get a syntax error on the following when executed from CTRL+G (VBA command
window, Access 2003)

Help?
currentdb.Execute("ALTER TABLE LogHeap ALTER COLUMN log_date DATETIME
DEFAULT DATE() NOT NULL")

Try executing on an ADO connection in stead.

"Note The DEFAULT statement can be executed only through the Jet OLE
DB provider and ADO. It will return an error message if used through
the Access SQL View user interface."

http://msdn2.microsoft.com/en-us/library/Aa140015(office.10).aspx

 

[Douglas J. Steele]

Assuming there's data in the table, I believe you'll need to provide a value
to all the field in all existing rows before you can set its default to Not
Null.

 

[Richard_doing_2007]

Great, bravo! Thank you!

Private Sub bahhhh()
' CurrentDb.Execute "create table AATEST (fld1 char(1), fld2 datetime not
null)"
Dim cnn As ADODB.Connection, cmd As ADODB.Command
Dim sql As String
Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Source\mydatabase.mdb;Persist Security Info=False"
Set cmd = New ADODB.Command
cmd.CommandText = "create table AATEST (fld1 char(1), fld2 datetime not
null)"
cmd.CommandText = "create table AATEST (fld1 char(1), fld2 datetime
default now() not null)"
cmd.ActiveConnection = cnn
cmd.Execute
' CurrentDb.Execute "create table AATEST (fld1 char(1), fld2 datetime
default now() not null)"
End Sub

 

[Jamie Collins]

Help?
currentdb.Execute("ALTER TABLE LogHeap ALTER COLUMN log_date DATETIME
DEFAULT DATE() NOT NULL")

CurrentDB returns a DAO object and DAO does not support the syntax.
Use an ADO connection instead e.g.

CurrentProject.Connection.Execute "ALTER TABLE LogHeap ALTER COLUMN
log_date DATETIME DEFAULT DATE() NOT NULL"

Jamie.

--