Solved

Access SQL Create Table

Posted on 2013-06-18
8
424 Views
Last Modified: 2013-06-20
Hi
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),

CREATE TABLE tblTest
    (CustomerID INTEGER NOT NULL,
    [Test] DECIMAL (10,2),
    [First Name] TEXT(50) NOT NULL,
    Phone TEXT(10),
    Email TEXT(50))
0
Comment
Question by:murbro
[X]
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
8 Comments
 
LVL 12

Expert Comment

by:pdebaets
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.
0
 
LVL 20

Expert Comment

by:GrahamMandeno
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]
0
 

Author Comment

by:murbro
ID: 39258711
Hi

Thanks. What would the full ADO code look like?
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 20

Assisted Solution

by:GrahamMandeno
GrahamMandeno earned 250 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:
?MakeMyTable

-- Graham
0
 

Author Comment

by:murbro
ID: 39258887
Hi

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

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 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"
cmd.Open

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
cmd.ExecuteNonQuery
0
 

Author Closing Comment

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

Expert Comment

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

Cheers,
Graham
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

724 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