• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 719
  • Last Modified:

ON CREATE FIELD SET A DEFAULT VALUE SQL

hallo folks
what i have is :

ALTER TABLE mytable
ADD myfield DATETIME

but i need to set this field an DEFAULT Value like "now()"
how to do it ??? in ms access sql ..... 97 or 2000 ???
0
dMa
Asked:
dMa
  • 11
  • 7
  • 2
  • +2
1 Solution
 
nico5038Commented:
For this you'll need to open the table fields definition and add the value "Now()" to the defaultvalue property.

Guess it's not possible using DDL.

Nic;o)
0
 
dMaAuthor Commented:
on access 2000 it's posiblle ....
0
 
nico5038Commented:
According M$ this should work:
ALTER TABLE mytable
add  column  myfield datetime default NOW();

However it's giving a syntax error...

So I would use some VBA code to add it, like:

Function fncSetDefault()

CurrentDb.TableDefs("mytable").Fields("myfield").DefaultValue = "Now()"

End Function

Nic;o)
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
dMaAuthor Commented:
ALTER TABLE mytable
add  column  myfield datetime default NOW();
dosn't work .... and an function ..... are uninterestingly
0
 
dMaAuthor Commented:
ALTER TABLE mytable
add  column  myfield datetime default NOW();
dosn't work .... and an function ..... are uninterestingly
thx ....
0
 
Ryan ChongCommented:
Isn't it the Nico's suggestion works > CurrentDb.TableDefs("mytable").Fields("myfield").DefaultValue = "Now()" ?

another alternative is use ADOX but i think it's too far already :)
0
 
dMaAuthor Commented:
yes offcourse
CurrentDb.TableDefs("mytable").Fields("myfield").DefaultValue = "Now()" ?
works fein .... the problem is i need an example in
SQL ... on oracle it's so easy ..... ;(
0
 
nico5038Commented:
Then check the oracle syntax and use a passthrough query.

Nic;o)
0
 
dMaAuthor Commented:
the oracle sytax is like the access 2000 syntax
"
ALTER TABLE mytable
add  column  myfield datetime default NOW();
"
but it's dos't work on access 97 .....
i must use the delphi syntax ....
0
 
nico5038Commented:
Even "stronger", it doesn't work on A2000 and A2002 either.

Looks like a bug to me. Perhaps M$ can shed some light on this.

Nic;o)
0
 
dMaAuthor Commented:
;) ..... no idea... i founded mor as 10 question about this problem in the newsgroups ..... no answares or
bullshitt .... M$ rulez .....
0
 
dMaAuthor Commented:
take it ..... ;)
0
 
nico5038Commented:
Can I ask to "leave it" ;-)
I think it's a bit "expensive" for others to pay 10 points for an answer like this one, if OK with you I'll post a Q at Community Support to PAQ this question and do a refund of the points.

Nic;o)
0
 
dMaAuthor Commented:
or open an question for 90 points and send me the link of this message via e-mail
i will add an comment ... and you now ... ;)
0
 
dMaAuthor Commented:
post the link to the message here ... thx
0
 
nico5038Commented:
It's not possible to "exchange" question points with "answer points".
I'll ask a moderator to take care of the PAQ and refund ;-)

Nic;o)
0
 
dMaAuthor Commented:
ok .... ;) as you will .... or forget it ... and enjoy ... the points ;))))
0
 
nico5038Commented:
Have got more then enough of those ;-)

Just want to avoid having Q buyers to be disappointed and you did find the answer more or less by yourself!

C U

Nic;o)
0
 
dMaAuthor Commented:
forget it ..... ;) enjoy it .... ;)))))) *ggg
0
 
wlennonVP of Domestic & Int'l OperationsCommented:
Thia will bs redone, to show an A grade but will have he points.

ComTech
CS Admin @ EE
0
 
reforestCommented:
I want to make sure I understand. Are you saying there is no way to create a column that contains a default value when programming in Delphi and working with an Access 2000 database? What if I have Access 2007? I tried the following and it didn't work:

with MyQuery do
begin
    Close;
    SQL.Clear;
    SQL.Add('ALTER TABLE ' + TableName + ' ');
    SQL.Add('ADD COLUMN Deleted CHAR(1) DEFAULT ''N''');
    ExecSQL;
  end;
0
 
reforestCommented:
Sorry for the question in the comment section. I posted a related question titled 'ADD FIELD SET A DEFAULT VALUE SQL'.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 11
  • 7
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now