Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

ON CREATE FIELD SET A DEFAULT VALUE SQL

Posted on 2002-07-24
22
Medium Priority
?
716 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
Independent Software Vendors: 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 53

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

618 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