Improve company productivity with a Business Account.Sign Up

x
?
Solved

Access SQL Create Table

Posted on 2013-06-18
8
Medium Priority
?
437 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:Murray Brown
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:Murray Brown
ID: 39258711
Hi

Thanks. What would the full ADO code look like?
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
LVL 20

Assisted Solution

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

-- Graham
0
 

Author Comment

by:Murray Brown
ID: 39258887
Hi

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

Accepted Solution

by:
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"
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:Murray Brown
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

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

608 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