Click here to Skip to main content
15,843,498 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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

1 solution

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]
 
Share this answer
 
v2
Comments
Darrell de Wet 8-Aug-13 5:52am    
Hi
Thanks for the reply.
That produces a Syntax error as well (on the open bracket)
Maciej Los 8-Aug-13 6:42am    
Try to change data type to DECIMAL(10,3). Which version of MS Access?
Darrell de Wet 8-Aug-13 7:05am    
Hi there
Still get Syntax error - this time on the word Decimal. Am using MS Access 2007
Maciej Los 8-Aug-13 7:54am    
See updated answer ;)

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900