My Assistant
Site Message
(Message will auto close in 2 seconds)
  |
 Access Reserved Words, Any Version
|
|
 Sep 11 2004, 01:46 PM
Post
#1
|
|
|
UtterAccess VIP Posts: 13,031 From: Leicester, UK  |
After seeing a lot of posts with people using reserved words in field names I have searched the corners of the earth (well Google) (IMG:style_emoticons/default/smile.gif) top
This lists below are words and symbols that should not be used when naming fields (or aliases), objects, controls, procedures or variables because they are "reserved words." Reserved words have a specific meaning to Microsoft Access and can wreak havoc when referenced in queries, VBA and other properties.. Because the Access db engine runs in different modes, it depends on if it is called from Access, DAO, the MS OLE Provider for the Access db engine, or the MS Access ODBC driver. And it can be run in either ANSI mode or non-ANSI, or traditional, mode. And because of these two modes, there results in two slightly different sets of reserved words, a query that uses a reserved word could work in one mode and fail in another. This information is authoritatively referenced from Microsoft's Reserved Words for Access 2002 & Up, Access 2007 Reserved Word & Symbols, Jet 4.0 Reserved Words, Office 2007 SQL Reserved Words and SQL Reserved Words. Allen Browne's Database Issue Checker is pretty good tool for running an entire app through it's regimen of 'issue checking'; including reserved words. Always avoid the characters: ! @ # $ % ^ & * ( ) ? > < + = ' "; : - ~ `\ | [ ] { } in naming conventions. The underscore _ is usually OK, but can get interesting in certain code circumstances. See Naming Conventions and/or Camel Case. And while complete lists are included below, you can more quickly check for a specific word by simply using your browser's 'page search' function. Access 97____Access 2000____Access 2002 (XP)/2003____Access 2007/2010____Access 2007/2010 Database Engine Reserved Words Access 97 ____(back to top) -A ACTION ADD ALL Alphanumeric ALTER AND ANY Application AS ASC Assistant AUTOINCREMENT Avg -B BETWEEN BINARY BIT BOOLEAN BY BYTE -C CHAR, CHARACTER COLUMN CompactDatabase CONSTRAINT Container Count COUNTER CREATE CreateDatabase CreateField CreateGroup CreateIndex CreateObject CreateProperty CreateRelation CreateTableDef CreateUser CreateWorkspace CURRENCY CurrentUser -D DATABASE DATE DATETIME DELETE DESC Description DISALLOW DISTINCT DISTINCTROW Document DOUBLE DROP -E Echo Else End Eqv Error EXISTS Exit -F FALSE Field, Fields FillCache FLOAT, FLOAT4, FLOAT8 FOREIGN Form, Forms FROM Full FUNCTION -G GENERAL GetObject GetOption GotoPage GROUP GROUP BY GUID (7.0, 97) -H HAVING -I Idle IEEEDOUBLE, IEEESINGLE If IGNORE Imp IN INDEX Index, Indexes INNER INSERT InsertText INT, INTEGER, INTEGER1, INTEGER2, INTEGER4 INTO IS -J JOIN -K KEY -L LastModified LEFT Level Like LOGICAL, LOGICAL1 LONG, LONGBINARY, LONGTEXT -M Macro Match Max, Min, Mod MEMO Module MONEY Move -N NAME NewPassword NO Not Note NULL NUMBER, NUMERIC -O Object OLEOBJECT OFF ON OPEN Open OpenRecordset OPTION OR ORDER Outer OWNERACCESS -P Parameter PARAMETERS Partial PERCENT PIVOT PRIMARY PROCEDURE Property -Q Queries Query Quit -R REAL Recalc Recordset REFERENCES Refresh RefreshLink RegisterDatabase Relation Repaint RepairDatabase Report Reports Requery RIGHT -S SCREEN SECTION SELECT SET SetFocus SetOption SHORT SINGLE SMALLINT SOME SQL StDev, StDevP STRING Sum -T TABLE TableDef, TableDefs TableID TEXT TIME, TIMESTAMP TOP TRANSFORM TRUE Type -U UNION UNIQUE UPDATE User -V VALUE VALUES Var, VarP VARBINARY, VARCHAR -W WHERE WITH Workspace -X Xor -Y Year YES YESNO Access 2000 ____(back to top) -A ADD ALL Alphanumeric ALTER AND ANY Application AS ASC Assistant AUTOINCREMENT Avg -B BETWEEN BINARY BIT BOOLEAN BY BYTE -C CHAR, CHARACTER COLUMN CompactDatabase CONSTRAINT Container Count COUNTER CREATE CreateDatabase CreateField CreateGroup CreateIndex CreateObject CreateProperty CreateRelation CreateTableDef CreateUser CreateWorkspace CURRENCY CurrentUser -D DATABASE DATE DATETIME DELETE DESC Description DISALLOW DISTINCT DISTINCTROW Document DOUBLE DROP -E Echo Else End Eqv Error EXISTS Exit -F FALSE Field, Fields FillCache FLOAT, FLOAT4, FLOAT8 FOREIGN Form, Forms FROM Full FUNCTION -G GENERAL GetObject GetOption GotoPage GROUP GROUP BY GUID -H HAVING -I Idle IEEEDOUBLE, IEEESINGLE If IGNORE Imp IN INDEX Index, Indexes INNER INSERT InsertText INT, INTEGER, INTEGER1, INTEGER2, INTEGER4 INTO IS -J JOIN -K KEY -L LastModified LEFT Level Like LOGICAL, LOGICAL1 LONG, LONGBINARY, LONGTEXT -M Macro Match Max, Min, Mod MEMO Module MONEY Move -N NAME NewPassword NO Not Note NULL NUMBER, NUMERIC -O Object OLEOBJECT OFF ON OPEN Open OpenRecordset OPTION OR ORDER Orientation Outer OWNERACCESS -P Parameter PARAMETERS Partial PERCENT PIVOT PRIMARY PROCEDURE Property -Q Queries Query Quit -R REAL Recalc Recordset REFERENCES Refresh RefreshLink RegisterDatabase Relation Repaint RepairDatabase Report Reports Requery RIGHT -S SCREEN SECTION SELECT) SET SetFocus SetOption SHORT SINGLE SMALLINT SOME SQL StDev, StDevP STRING Sum -T TABLE TableDef, TableDefs TableID TEXT TIME, TIMESTAMP TOP TRANSFORM TRUE Type -U UNION UNIQUE UPDATE User -V VALUE VALUES Var, VarP VARBINARY, VARCHAR -W WHERE WITH Workspace -X Xor -Y Year YES YESNO Access 2002 (XP)/2003 ____(back to top) -A ADD ALL Alphanumeric ALTER AND ANY Application AS ASC Assistant AUTOINCREMENT Avg -B BETWEEN BINARY BIT BOOLEAN BY BYTE -C CHAR, CHARACTER COLUMN CompactDatabase CONSTRAINT Container Count COUNTER CREATE CreateDatabase CreateField CreateGroup CreateIndex CreateObject CreateProperty CreateRelation CreateTableDef CreateUser CreateWorkspace CURRENCY CurrentUser -D DATABASE DATE DATETIME DELETE DESC Description DISALLOW DISTINCT DISTINCTROW Document DOUBLE DROP -E Echo Else End Eqv Error EXISTS Exit -F FALSE Field, Fields FillCache FLOAT, FLOAT4, FLOAT8 FOREIGN Form, Forms FROM Full FUNCTION -G GENERAL GetObject GetOption GotoPage GROUP GROUP BY GUID -H HAVING -I Idle IEEEDOUBLE, IEEESINGLE If IGNORE Imp IN INDEX Index, Indexes INNER INSERT InsertText INT, INTEGER, INTEGER1, INTEGER2, INTEGER4 INTO IS -J JOIN -K KEY -L LastModified LEFT Level Like LOGICAL, LOGICAL1 LONG, LONGBINARY, LONGTEXT -M Macro Match Max, Min, Mod MEMO Module MONEY Move -N NAME NewPassword NO Not Note NULL NUMBER, NUMERIC -O Object OLEOBJECT OFF ON Open OPEN OpenRecordset OPTION OR ORDER Orientation Outer OWNERACCESS -P Parameter PARAMETERS Partial PERCENT PIVOT PRIMARY PROCEDURE Property -Q Queries Query Quit -R REAL Recalc Recordset REFERENCES Refresh RefreshLink RegisterDatabase Relation Repaint RepairDatabase Report Reports Requery RIGHT -S SCREEN SECTION SELECT SET SetFocus SetOption SHORT SINGLE SMALLINT SOME SQL StDev, StDevP STRING Sum -T TABLE TableDef, TableDefs TableID TEXT TIME, TIMESTAMP TOP TRANSFORM TRUE Type -U UNION UNIQUE UPDATE USER -V VALUE VALUES Var, VarP VARBINARY, VARCHAR -W WHERE WITH Workspace -X Xor -Y Year YES YESNO Also the following characters are reserved . / * ; : ! # & - ? " ' $ You may also want to check Allen's site HERE for an updated version of reserved words. Edited by: GlenKruger on Wed Jul 23 11:35:19 EDT 2008. Access 2007/2010 ____(back to top) CODE -A The reserved symbols are the same as the previous versions. ADD ALL Alphanumeric ALTER AND ANY Application AS ASC Assistant AUTOINCREMENT Avg -B BETWEEN BINARY BIT BOOLEAN BY BYTE -C CHAR, CHARACTER COLUMN CompactDatabase CONSTRAINT Container Count COUNTER CREATE CreateDatabase CreateField CreateGroup CreateIndex CreateObject CreateProperty CreateRelation CreateTableDef CreateUser CreateWorkspace CURRENCY CurrentUser -D DATABASE DATE DATETIME DELETE DESC Description DISALLOW DISTINCT DISTINCTROW Document DOUBLE DROP -E Echo Else End Eqv Error EXISTS Exit -F FALSE Field, Fields FillCache FLOAT, FLOAT4, FLOAT8 FOREIGN Form, Forms FROM Full FUNCTION -G GENERAL GetObject GetOption GotoPage GROUP GROUP BY GUID -H HAVING -I Idle IEEEDOUBLE, IEEESINGLE If IGNORE Imp IN INDEX Index, Indexes INNER INSERT InsertText INT, INTEGER, INTEGER1, INTEGER2, INTEGER4 INTO IS -J JOIN -K KEY -L LastModified LEFT Level Like LOGICAL, LOGICAL1 LONG, LONGBINARY, LONGTEXT -M Macro Match Max, Min, Mod MEMO Module MONEY Move -N NAME NewPassword NO Not Note NULL NUMBER, NUMERIC -O Object OLEOBJECT OFF ON OpenRecordset OPTION OR ORDER Orientation Outer OWNERACCESS -P Parameter PARAMETERS Partial PERCENT PIVOT PRIMARY PROCEDURE Property -Q Queries Query Quit -R REAL Recalc Recordset REFERENCES Refresh RefreshLink RegisterDatabase Relation Repaint RepairDatabase Report Reports Requery RIGHT -S SCREEN SECTION SELECT SET SetFocus SetOption SHORT SINGLE SMALLINT SOME SQL StDev, StDevP STRING Sum -T TABLE TableDef, TableDefs TableID TEXT TIME, TIMESTAMP TOP TRANSFORM TRUE Type -U UNION UNIQUE UPDATE USER -V VALUE VALUES Var, VarP VARBINARY, VARCHAR -W WHERE WITH Workspace -X Xor -Y Year YES YESNO Access 2007/2010 Database Engine Reserved Words ____(back to top) CODE -A
ABSOLUTE ACTION ADD ADMINDB ALL ALLOCATE ALPHANUMERIC ALTER AND ANY ARE AS ASC ASSERTION AT AUTHORIZATION AUTOINCREMENT AVG -B BAND BEGIN BETWEEN BINARY BIT BIT_LENGTH BNOT BOR BOTH BXOR BY BYTE -C CASCADE CASCADED CASE CAST CATALOG CHAR CHARACTER CHAR_LENGTH CHARACTER_LENGTH CHECK CLOSE COALESCE COLLATE COLLATION COLUMN COMMIT COMP COMPRESSION CONNECT CONNECTION CONSTRAINT CONSTRAINTS CONTAINER CONTINUE CONVERT CORRESPONDING COUNT COUNTER CREATE CREATEDB CROSS CURRENCY CURRENT CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR -D DATABASE DATE DATETIME DAY DEALLOCATE DEC DECIMAL DECLARE DEFAULT DEFERRABLE DEFERRED DELETE DESC DESCRIBE DESCRIPTOR DIAGNOSTICS DISALLOW DISCONNECT DISTINCT DOMAIN DOUBLE DROP -E ELSE END END-EXEC ESCAPE EXCEPT EXCEPTION EXCLUSIVECONNECT EXEC EXECUTE EXISTS EXTERNAL EXTRACT -F FALSE FETCH FIRST FLOAT FLOAT4 FLOAT8 FOR FOREIGN FOUND FROM FULL -G GENERAL GET GLOBAL GO GOTO GRANT GROUP GUID -H HAVING HOUR -I IDENTITY IEEEDOUBLE IEEESINGLE IGNORE IMAGE IMMEDIATE ININDEX INDICATOR INHERITABLE INITIALLY INNER INPUT INSENSITIVE INSERT INT INTEGER INTEGER1 INTEGER2 INTEGER4 INTERSECT INTERVAL INTO IS ISOLATION -J JOIN -K KEY -L LANGUAGE LAST LEADING LEFT LEVEL LIKE LOCAL LOGICAL LOGICAL1 LONG LONGBINARY LONGCHAR LONGTEXT LOWER -M MATCH MAX MEMO MIN MINUTE MODULE MONEY MONTH -N NAMES NATIONAL NATURAL NCHAR NEXT NO NOT NOTE NULL NULLIF NUMBER NUMERIC -O OBJECT OCTET_LENGTH OFOLEOBJECT ONONLY OPEN OPTION ORORDER OUTER OUTPUT OVERLAPS OWNERACCESS -P PAD PARAMETERS PARTIAL PASSWORD PERCENT PIVOT POSITION PRECISION PREPARE PRESERVE PRIMARY PRIOR PRIVILEGES PROC PROCEDURE PUBLIC -Q -R READ REAL REFERENCES RELATIVE RESTRICT REVOKE RIGHT ROLLBACK ROWS -S SCHEMA SCROLL SECOND SECTION SELECT SELECTSCHEMA SELECTSECURITY SESSION SESSION_USER SET SHORT SINGLE SIZE SMALLINT SOME SPACE SQL SQLCODE SQLERROR SQLSTATE STRING SUBSTRING SUM SYSTEM_USER -T TABLE TABLEID TEMPORARY TEXT THEN TIME TIMESTAMP TIMEZONE_HOUR TIMEZONE_MINUTE TO TOP TRAILING TRANSACTION TRANSFORM TRANSLATE TRANSLATION TRIM TRUE -U UNION UNIQUE UNIQUEIDENTIFIER UNKNOWN UPDATE UPDATEIDENTITY UPDATEOWNER UPDATESECURITY UPPER USAGE USER USING -V VALUE VALUES VARBINARY VARCHAR VARYING VIEW -W WHEN WHENEVER WHERE WITH WORK WRITE -X -Y YEAR YESNO -Z ZONE This post has been edited by CyberCow: May 7 2013, 09:26 PM
Reason for edit: Added newer Microsoft references and included some Naming Convention referennces.
|
 |
 
|
 |
|
Jump To Forum:
|
Go to Top · Lo-Fi Version | Time is now: 25th May 2013 - 12:05 PM |