Solved

Assign default value when creating table in ACCESS 2003

Posted on 2009-07-09
10
338 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 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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…
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…

832 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