Solved

ON CREATE FIELD SET A DEFAULT VALUE SQL

Posted on 2002-07-24
22
702 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
 
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 49

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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now