Solved

Assign default value when creating table in ACCESS 2003

Posted on 2009-07-09
10
350 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 - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 100 total points
ID: 24816119
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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
 
LVL 120

Expert Comment

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

Expert Comment

by:Rey Obrero (Capricorn1)
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

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

Suggested Solutions

Title # Comments Views Activity
Undo Button 1 35
Clear Current Value from Combobox 2 26
Trying to export a table to another Access database file 2 19
Access table not showing correct column 6 25
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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 the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

730 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