CREATE TABLE Statement

[Lou Civitella]

Can some one show me an example of a CREATE TABLE statement. I am having
some difficulty with adding default values and field formats.

This is what I have so far.

dbs.execute "CREATE TABLE Table1 (Field1 Text(10), Field2 Double)"

What I would like to do is add default values to both fields and also to add
a Currency format to Field2. I want to leave Field2 as a Double, I just want
to change its format to Currency.

Thanks In Advance,
Lou

 

[peregenem]

Can some one show me an example of a CREATE TABLE statement. I am having
some difficulty with adding default values and field formats.

This is what I have so far.

dbs.execute "CREATE TABLE Table1 (Field1 Text(10), Field2 Double)"

What I would like to do is add default values to both fields and also to add
a Currency format to Field2. I want to leave Field2 as a Double, I just want
to change its format to Currency.

CurrentProject.Connection.Execute _
"CREATE TABLE MyTable (Field1 Text(10), Field2 DECIMAL(18, 6) DEFAULT 0
NOT NULL)"

Do not use an inexact data type for currency!

 

[Lou Civitella]

OK. That worked great but it only works for the current project. I am trying
to do it on an external Access database

Here is the code I have exactly:
Dim dbs As Database

Set dbs = OpenDatabase("C:\db1.mdb")

dbs.Execute "CREATE TABLE MyTable (Field1 Text(10), Field2 DECIMAL(18,
6) DEFAULT 0 NOT NULL)"

When I run the code I get this message:
Syntex Error in field definition.

I even tried to change the Decimal to Double and then got this message:
Syntex error in CREATE TABLE statement.

How can I do this on an external table.

Thanks Again,
Lou

 

[peregenem]

OK. That worked great but it only works for the current project. I am trying
to do it on an external Access database

Here is the code I have exactly:
Dim dbs As Database

Set dbs = OpenDatabase("C:\db1.mdb")

dbs.Execute "CREATE TABLE MyTable (Field1 Text(10), Field2 DECIMAL(18,
6) DEFAULT 0 NOT NULL)"

You need to use the OLE DB provider e.g.

Dim con As Object
Set con = CreateObject("ADODB.Connection")
With con
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\db1.mdb" & _
.Open
.Execute _
"CREATE TABLE MyTable (" & _
" Field1 Text(10)," & _
" Field2 DECIMAL(18,6) DEFAULT 0 NOT NULL);"
.Close
End With

 

[Lou Civitella]

that worked great. I just have one more question if you don't mind.

I add a Yes/No field to a table via:
..Execute "ALTER TABLE Table1 ADD COLUMN ysnKit YESNO DEFAULT No"

When I looked at the table it in text box mode not check box. How can I
change this to make it look like a check box. I want to also do this
programmatically. I know about the Display Control property but how do I
change this?

Thanks Again,
Lou

 

[peregenem]

When I looked at the table it in text box mode not check box. How can I
change this to make it look like a check box. I want to also do this
programmatically.

Sorry, I am not familiar with Access Forms. Perhaps change the text
box's font to Wingdings and use

SELECT CHR$(IIF(ysnKit, 254, 111)) AS ysnKit_char FROM Table1

 

[Brendan Reynolds]

You can set the DisplayControl property of a field via DAO ...

CurrentDb.TableDefs("tblTarget").Fields("TestBool").Properties("DisplayControl")
= acCheckBox