Solved

add field to database with alter

Posted on 2006-06-09
4
429 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
ID: 16872175
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
ID: 16872183
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
ID: 16873382
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
ID: 16877635
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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…
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 …

895 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

13 Experts available now in Live!

Get 1:1 Help Now