Solved

ON CREATE FIELD SET A DEFAULT VALUE SQL

Posted on 2002-07-24
22
706 Views
Last Modified: 2008-02-01
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
Comment
Question by:dMa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 7
  • 2
  • +2
22 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 7175380
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
 
LVL 2

Author Comment

by:dMa
ID: 7175390
on access 2000 it's posiblle ....
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7175797
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Author Comment

by:dMa
ID: 7175814
ALTER TABLE mytable
add  column  myfield datetime default NOW();
dosn't work .... and an function ..... are uninterestingly
0
 
LVL 2

Author Comment

by:dMa
ID: 7175819
ALTER TABLE mytable
add  column  myfield datetime default NOW();
dosn't work .... and an function ..... are uninterestingly
thx ....
0
 
LVL 51

Expert Comment

by:Ryan Chong
ID: 7176523
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
 
LVL 2

Author Comment

by:dMa
ID: 7176531
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
 
LVL 54

Expert Comment

by:nico5038
ID: 7176859
Then check the oracle syntax and use a passthrough query.

Nic;o)
0
 
LVL 2

Author Comment

by:dMa
ID: 7176887
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
 
LVL 54

Accepted Solution

by:
nico5038 earned 0 total points
ID: 7176926
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
 
LVL 2

Author Comment

by:dMa
ID: 7176938
;) ..... no idea... i founded mor as 10 question about this problem in the newsgroups ..... no answares or
bullshitt .... M$ rulez .....
0
 
LVL 2

Author Comment

by:dMa
ID: 7176939
take it ..... ;)
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7177010
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
 
LVL 2

Author Comment

by:dMa
ID: 7177059
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
 
LVL 2

Author Comment

by:dMa
ID: 7177060
post the link to the message here ... thx
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7177107
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
 
LVL 2

Author Comment

by:dMa
ID: 7177121
ok .... ;) as you will .... or forget it ... and enjoy ... the points ;))))
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7177176
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
 
LVL 2

Author Comment

by:dMa
ID: 7177194
forget it ..... ;) enjoy it .... ;)))))) *ggg
0
 
LVL 13

Expert Comment

by:wlennon
ID: 7178266
Thia will bs redone, to show an A grade but will have he points.

ComTech
CS Admin @ EE
0
 

Expert Comment

by:reforest
ID: 21775857
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
 

Expert Comment

by:reforest
ID: 21775912
Sorry for the question in the comment section. I posted a related question titled 'ADD FIELD SET A DEFAULT VALUE SQL'.
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question