Access SQL Create Table

Posted on 2013-06-18
Medium Priority
Last Modified: 2013-06-20
I am trying to create a table with a Decimal column called "Test"
using the following code, but am getting a syntax error with:
[Test] DECIMAL (10,2),

    [Test] DECIMAL (10,2),
    [First Name] TEXT(50) NOT NULL,
    Phone TEXT(10),
    Email TEXT(50))
Question by:Murray Brown
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
LVL 12

Expert Comment

ID: 39257741
You can use CURRENCY which should automatically give you 4 decimal places, or DOUBLE, or SINGLE.

If you want 2 decimal places, use the table field DecimalPlaces property, Format property, or the format function.
LVL 20

Expert Comment

ID: 39257991
A field of type DECIMAL cannot be created in an Access database using DAO, but it can be done with ADO.

Instead of CurrentDb.Execute strDDL,
use CurrentProject.Connection.Execute strDDL

Best wishes,
Graham Mandeno [Access MVP 1996-2013]

Author Comment

by:Murray Brown
ID: 39258711

Thanks. What would the full ADO code look like?
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

LVL 20

Assisted Solution

GrahamMandeno earned 1000 total points
ID: 39258825
You can't put the code in a query because that will execute using DAO.  Create a VBA module with code like this:

Public Sub MakeMyTable()
Dim strDDL as String
strDDL = "CREATE TABLE tblTest" _
    & " (CustomerID INTEGER NOT NULL," _
    & " [Test] DECIMAL (10,2)," _
    & " [First Name] TEXT(50) NOT NULL," _
    & " Phone TEXT(10)," _
    & " Email TEXT(50)) "
CurrentProject.Connection.Execute strDDL
End Sub

Open in new window

Then you can execute your procedure, either from some other VBA code, or from the Immediate window:

-- Graham

Author Comment

by:Murray Brown
ID: 39258887

I should have mentioned that I am building this in VB.net/VSTO
LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
ID: 39261713
I am building this in VB.net/VSTO
Then create an OleDbConnection and use the ExecuteNonQuery method to run the SQL that Graham provided:

Dim cmd As New System.Data.OleDbConnection
cmd.ConnectionString = "Your Access Connection"

Dim strDDL as String
strDDL = "CREATE TABLE tblTest" _
    & " (CustomerID INTEGER NOT NULL," _
    & " [Test] DECIMAL (10,2)," _
    & " [First Name] TEXT(50) NOT NULL," _
    & " Phone TEXT(10)," _
    & " Email TEXT(50)) "

cmd.CommandText = strDDL

Author Closing Comment

by:Murray Brown
ID: 39261753
Thanks very much. Should have realised that. Got into a tangle with all the different approaches on the net.
LVL 20

Expert Comment

ID: 39261823
Thanks, Scott.  I've been out all day so I wasn't here to fill in the cracks :-)


Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

770 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