Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


add field to database with alter

Posted on 2006-06-09
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 15

Expert Comment

ID: 16872175
Should be :

LVL 15

Accepted Solution

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

LVL 44

Assisted Solution

GRayL earned 1000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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…

610 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