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
Solved

ON CREATE FIELD SET A DEFAULT VALUE SQL

Posted on 2002-07-24
22
705 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
  • 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

856 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