How do I create a decimal field in Access with Alter Table?

by

The decimal data type isn’t supported in the default Jet 4.0 mdb file. You have to use the SQL Server compatibility syntax (ANSI 92) setting to use the decimal data type in the SQL Window.

Click on the menu, Tools > Options. Click on the Tables/Query tab. Mark the check box for “This database” in the SQL Server compatibility syntax (ANSI 92) section. This mode will affect the entire db, including queries with wildcards, so you may want to try this on a copy of your db.

Paste this into the SQL window:

ALTER TABLE MyTable
  Add COLUMN MyField DECIMAL (9,4) NULL;

If you don’t want to alter the mode of your database, you must use vba code with the adodb library:

Dim conn As ADODB.Connection

Set conn = CurrentProject.Connection
conn.Execute "ALTER TABLE MyTable " _
    & "ADD COLUMN MyField DECIMAL (9,4) NULL;"
conn.Close

More Related Contents:

  1. SQL Access ” Count “? [closed]
  2. How do I use parameters in VBA in the different contexts in Microsoft Access?
  3. Upserting in MS-access
  4. How to get matching data from another SQL table for two different columns: Inner Join and/or Union?
  5. How to simulate UNPIVOT in Access?
  6. Case expressions in Access
  7. SQL Query – Using Order By in UNION
  8. How do I count unique items in field in Access query?
  9. Date Difference between consecutive rows
  10. Pivoting data in MS Access
  11. coalesce alternative in Access SQL
  12. Using Excel VBA to export data to MS Access table
  13. Top n records per group sql in access
  14. Access-SQL: Inner Join with multiple tables
  15. Address standardization within a database
  16. Does MS Access support “CASE WHEN” clause if connect with ODBC?
  17. Why does a LIKE query in Access not return any records?
  18. How to SUM two fields within an SQL query
  19. How to use system username directly in MS Access query?
  20. Achieving ROW_NUMBER / PARTITION BY in MS Access
  21. Query for getting value from another record in same table and filter by difference greater than a gap threshold
  22. SQL Updating from an inner join
  23. How to delete in MS Access when using JOIN’s?
  24. Lightweight SQL database which doesn’t require installation [closed]
  25. HOW TO SELECT PREVIOUS ROW VALUE? [duplicate]
  26. Editing Record issues in Access / SQL (Write Conflict)
  27. How do I create a table alias in MySQL
  28. SQL SET DEFAULT not working in MS Access
  29. CONCAT equivalent in MS Access
  30. Access substitute for EXCEPT clause

Leave a Comment