add field to database with alter

Posted on 2006-06-09
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 [# IRAQIS EMPLOYED] LONG;"
myDb.Execute "Alter Table PIIN_LOG Add COLUMN Province_City CHAR(50);"
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;"

End Sub
Question by:howcheat
  • 2
  • 2
LVL 15

Expert Comment

ID: 16872175
Should be :

LVL 15

Accepted Solution

jmantha709 earned 250 total points
ID: 16872183
Correction, should be :

LVL 44

Assisted Solution

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.
(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.  



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

Expert Comment

ID: 16877635
Thanks, glad I could help.  Strange that help says it should be Bit, but YesNo also works - Oh well that's MS.

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
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 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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

809 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