Solved

ON CREATE FIELD SET A DEFAULT VALUE SQL

Posted on 2002-07-24
22
707 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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 52

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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 …

732 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