Solved

add field to database with alter

Posted on 2006-06-09
4
426 Views
Last Modified: 2012-06-21
I am trying to add the following columns to a table and I get the error message "syntax error in field definition" on the first command.

Public Sub AdColumns()
Dim myDb As DAO.Database
Set myDb = CurrentDb

myDb.Execute "Alter Table PIIN_LOG Add COLUMN HN_IRAQI BUSINESS YES/NO(4);"
myDb.Execute "Alter Table PIIN_LOG Add COLUMN [# IRAQIS EMPLOYED] LONG;"
myDb.Execute "Alter Table PIIN_LOG Add COLUMN Province_City CHAR(50);"
myDb.Execute "Alter Table PIIN_LOG Add COLUMN v_IRAQISEMPLOYED CHAR LONG;"
myDb.Execute "Alter Table PIIN_LOG Add COLUMN [First Tier Subcontractor] YESNO;"
myDb.Execute "Alter Table PIIN_LOG Add COLUMN [# Iraqis employed on first tier subcontract] LONG);"
myDb.Execute "Alter Table PIIN_LOG Add COLUMN [Reason for Non-Award] CHAR(250);"
myDb.Execute "Alter Table PIIN_LOG Add COLUMN Unit CHAR(50);"
myDb.Execute "Alter Table PIIN_LOG Add COLUMN [Dollar value under HN subcontract] MONEY;"

myDb.Close
End Sub
0
Comment
Question by:howcheat
  • 2
  • 2
4 Comments
 
LVL 15

Expert Comment

by:jmantha709
Comment Utility
Should be :

myDb.Execute "Alter Table PIIN_LOG Add COLUMN HN_IRAQI BUSINESS YESNO;"
0
 
LVL 15

Accepted Solution

by:
jmantha709 earned 250 total points
Comment Utility
Correction, should be :

myDb.Execute "Alter Table PIIN_LOG Add COLUMN [HN_IRAQI BUSINESS] YESNO;"
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 250 total points
Comment Utility
With A2K - VB Editor Help

SQL Data Types
The Microsoft Jet database engine SQL data types consist of 13 primary data types defined by the Microsoft® Jet database engine and several valid synonyms recognized for these data types.

The following table lists the primary data types. The synonyms are identified in Microsoft Jet Database Engine SQL Reserved Words.

Data type Storage size Description
BINARY 1 byte per character Any type of data may be stored in a field of this type. No translation of the data (for example, to text) is made. How the data is input in a binary field dictates how it will appear as output.
BIT 1 byte Yes and No values and fields that contain only one of two values.
TINYINT 1 byte An integer value between 0 and 255.
MONEY 8 bytes A scaled integer between
 – 922,337,203,685,477.5808 and 922,337,203,685,477.5807.
DATETIME
(See DOUBLE) 8 bytes A date or time value between the years 100 and 9999.
UNIQUEIDENTIFIER 128 bits A unique identification number used with remote procedure calls.
REAL 4 bytes A single-precision floating-point value with a range of  – 3.402823E38 to  – 1.401298E-45 for negative values, 1.401298E-45 to 3.402823E38 for positive values, and 0.
FLOAT 8 bytes A double-precision floating-point value with a range of  – 1.79769313486232E308 to  – 4.94065645841247E-324 for negative values, 4.94065645841247E-324 to 1.79769313486232E308 for positive values, and 0.
SMALLINT 2 bytes A short integer between  – 32,768 and 32,767. (See Notes)
INTEGER 4 bytes A long integer between  – 2,147,483,648 and 2,147,483,647. (See Notes)
DECIMAL 17 bytes An exact numeric data type that holds values from 1028 - 1 through - 1028 - 1. You can define both precision (1 - 28) and scale (0 - defined precision). The default precision and scale are 18 and 0, respectively.
TEXT 2 bytes per character (See Notes) Zero to a maximum of 2.14 gigabytes.  
IMAGE As required Zero to a maximum of 2.14 gigabytes. Used for OLE objects.
CHARACTER 2 bytes per character (See Notes) Zero to 255 characters.  


--------------------------------------------------------------------------------

Notes

Both the seed and the increment can be modified using an ALTER TABLE statement. New rows inserted into the table will have values, based on the new seed and increment values, that are automatically generated for the column. If the new seed and increment can yield values that match values generated based on the preceding seed and increment, duplicates will be generated. If the column is a primary key, then inserting new rows may result in errors when duplicate values are generated.
To find the last value that was used for an auto-increment column, you can use the following statement: SELECT @@IDENTITY. You cannot specify a table name. The value returned is from the last table, containing an auto-increment column, that was updated.
Characters in fields defined as either TEXT (also known as MEMO) or CHAR (also known as TEXT(n) with a specific length) are stored in the Unicode representation format. Unicode characters uniformly require two bytes to store each character. For existing Microsoft Jet databases that contain predominately character data, this could mean that the database file would nearly double in size when converted to the Microsoft Jet 4.0 format. Yet Unicode representation of many character sets, those formerly denoted as SBCS (Single-Byte Character Sets) can easily be compressed to a single byte. For details, see CREATE TABLE. If you define a CHAR column with the COMPRESSION attribute, data will automatically be compressed as it is stored and uncompressed when it is retrieved from the column.

--------------------------------------------------------------------------------

See Also
Equivalent ANSI SQL Data Types Microsoft Jet Database Engine SQL Reserved Words


I see it as BIT, not YESNO
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
Thanks, glad I could help.  Strange that help says it should be Bit, but YesNo also works - Oh well that's MS.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

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…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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 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 …

763 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

7 Experts available now in Live!

Get 1:1 Help Now