Create MsAccess (accdb) table datatype Double with decimal places

Question

0.00/5 (No votes)

See more:

Good morning everyone

I am attempting to create an accdb table with the following code :

VB

Private Sub CreateTable(TbleName)

    cn.ConnectionString = "Provider=" & cp & " Data Source=" & tss_DB.Text & ";Jet OLEDB:Database Password=Password;"
    cn.Open()

    cmd.Connection = cn
    cmd.CommandText = "CREATE Table Aa_News (ID Counter, Field1 Double)"
    cmd.ExecuteNonQuery()

    cn.Close()

End Sub

This does successfully create the table but Field1 has the following properties :
Field Size : Double
Format :
Decimal Places : Auto
Input Mask :
Caption :
Default Value :
Validation Rule:
Validation Text:
Required : No
Indexed : No
Smart Tags :
Text Align : General

I need it to be it to be :

Field Size : Double
Format : Fixed
Decimal Places : 3
Input Mask :
Caption :
Default Value :
Validation Rule:
Validation Text:
Required : Yes
Indexed : No
Smart Tags :
Text Align : General

Though I have looked, I can only find setting the TEXT type field size.
Could anyone please advise me how I specify properties for different Data Types but specifically DECIMAL.

If I use

SQL

CREATE Table Aa_News (ID Counter,Field1 double(Fixed,3) )

I get a syntax error.

Your assistance greatly appreciated

Darrell

Posted 7-Aug-13 22:08pm

Darrell de Wet

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-08-07T23:38:00

Instead:

SQL

CREATE Table Aa_News (ID Counter,Field1 double(Fixed,3) )

try:

SQL

CREATE Table Aa_News (ID Counter,Field1 double(10,3) )

or

SQL

CREATE Table Aa_News (ID Counter,Field1 DECIMAL(10,3) )

[EDIT]
It's strange, but using VBA and MS ADO 2.8 Library below code executes without errors:

VB

CurrentProject.Connection.Execute "CREATE TABLE AAA (ID COUNTER, Field1 DECIMAL(10,3))"

So, probably, it's not possible to define the size and decimal places for field in DDL query using .NET drivers (Jet OLEDB). Please, read this: Allen Browne - MS Access Tips: DDL Code Examples[^]

Allen Browne wrote:
While DDL is important in some larger databases, it is of limited use in Access. You can create a Text field, but you cannot set the Allow Zero Length property to No, so any query criteria must test for both Nulls and zero-length strings. You can create a Yes/No field, but you get a text box, not a check box, since you cannot set the Display Control property with DDL. You can create a Date/Time field, but you cannot set the Format property. DDL cannot create Hyperlink fields, Attachment fields, or the complex data types at all.

Ultimately, you execute your DDL query under DAO or ADO. For DAO, use:

VB
dbEngine(0)(0).Execute strSql, dbFailOnError
For ADO, use:

VB
CurrentProject.Connection.Execute strSql
Some features of JET 4 (Access 2000 and later) are supported under ADO only. These queries fail if you paste the SQL statement into the query designer in Access, since the Access interface uses DAO.

One case where DDL is really useful it to change a field's data type or size. You cannot do this in DAO or ADOX, so DDL is your the only practical approach (other than copying everything to another field and deleting the old one.) Other than that, Access developers use DDL infrequently.

Also, alternatively, you can create table without Field1 and then try to add column in DDL query:

SQL

ALTER TABLE Aa_News ADD COLUMN Field1 DECIMAL(10,2);

As per i understand, it could not work in some cases.

Another way is to use
1) DAO:
http://stackoverflow.com/questions/16292960/is-it-possible-to-change-the-double-column-decimal-size[^]
or
2) ADODB command:
Using Adodb.Command.Execute Method in C# and ASp.net[^]

[/EDIT]