Solved

Assign default value when creating table in ACCESS 2003

Posted on 2009-07-09
10
314 Views
Last Modified: 2013-11-25
Is there a way to create a default value for a column when creating a table in ACCESS 2003?  the only documentation I can find indicates to use the ALTER TABLE statement, but is there a way to simply assign it when first creating the table?  I want the Match column to default to NO.  Also, is there a way to tell the process this should also be a checkbox, which by default is no?
strCreateTable = "CREATE TABLE tblTEMPWeeklyApps (Campaign_ID Integer, SSN Text(12), " & _

    "Booked Integer, Approved Integer, AppDate Date, Age Integer, " & _

    "Reference Text(20), AddrType Integer, DNIS Text(10), AppEID Text(20), " & _

    "Match Bit DEFAULT 'No')"

Open in new window

0
Comment
Question by:ssmith94015
  • 5
  • 3
10 Comments
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 100 total points
ID: 24816119
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 400 total points
ID: 24816295
after you create the table you need more code to set the values properties

dim db as dao.database, fd as dao.field, pt as dao.property

strCreateTable = "CREATE TABLE tblTEMPWeeklyApps (Campaign_ID Integer, SSN Text(12), " & _
    "Booked Integer, Approved Integer, AppDate Date, Age Integer, " & _
    "Reference Text(20), AddrType Integer, DNIS Text(10), AppEID Text(20), " & _
    "Match Bit)"

db.execute strCreateTable

Set fd = db.TableDefs("tblTEMPWeeklyApps").Fields("Match")
    fd.DefaultValue = 0
Set pt = fd.CreateProperty("DisplayControl", dbInteger, 106)
    db.TableDefs("tblTEMPWeeklyApps").Fields("Match").Properties.Append pt
0
 

Author Comment

by:ssmith94015
ID: 24816942
I was afraid I was stuck with the ALTER TABLE so that is the route and simplest.
0
 

Author Comment

by:ssmith94015
ID: 24816962
Capricorn, yours was a way to do it, but more complicated than what I needed.  But thank you.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 119

Expert Comment

by:Rey Obrero
ID: 24816972
what about
< Also, is there a way to tell the process this should also be a checkbox>
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 24817000
i don't think it is fair that you will select a suggestion that only fulfills part of your requirement when there is  a complete solution offered.
0
 

Author Comment

by:ssmith94015
ID: 24818316
OH MY GOSH, I completely missed that.  You are so correct, I will contact Adminiistration here and get some points moved.  
0
 

Author Comment

by:ssmith94015
ID: 24818354
Cap, I have sent a request for attention and am requesting 400 points to be moved to you.  I am so very sorry, it has been a long continuous day-night-day and I missed what you were telling me.  Your response was the complete answer and does work but in the time alloted to get this out the door I did use the ALTER statement.
0
 

Author Closing Comment

by:ssmith94015
ID: 31601735
Thank you both and Cap, I again apologize as your works and is complete.  To be fair,  I did give points to DatabaseMX as that was the route I took at the time.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

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…
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…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

707 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

15 Experts available now in Live!

Get 1:1 Help Now