CREATE TABLE

This statement creates a new table.

Syntax

CREATE TABLE tablename (fieldname fieldtype [,fieldname fieldtype])

Parameters

tablename

Specifies the name for the new table.

fieldname

Specifies the name of the column to create in the table.

fieldtype

Specifies the data type for the column. It can be one of the values described in the following table.

Varchar[(n)]

Null-terminated Unicode character string of length n, with a maximum of 255 characters. If n is not supplied, then 1 is assumed, as per ANSI SQL.

Text

Variable length string that can hold as many as 32,000 characters, typically used for more than 255 characters.

Varbinary[(n)]

Binary value of length n <256. If n is not specified, the default is 1, as per ANSI SQL.

Long Varbinary

Binary value of length <65,469 bytes. This type is also known as OLE Object.

integer, int

4-byte signed integer.

smallint

2-byte signed integer.

float

Double-precision floating point value.

datetime

Date value.

bit

Logical or Boolean value — zero for False, nonzero for True.

uint

Unsigned 4-byte integer — provided for backward compatibility only.

usmallint

Unsigned 2-byte integer — provided for backward compatibility only.

The SQL data types uint and usmallint are provided solely for backward compatibility with existing Windows CE-based tables. However, this compatibility does not apply to the conversion and filter tools and should not be generally relied upon. You should not use the uint or usmallint datatypes. Instead, use the signed equivalents of int and smallint.

Return Values

One of the following error values can be returned:

• E_OUTOFMEMORY
• DB_E_ERRORSINCOMMAND
• DB_E_DUPLICATETABLEID
• DB_E_DUPLICATECOLUMNID

Remarks

In Windows CE, you cannot have two tables with the same name on the same device.

Types uint and usmallint are not standard data types on other database systems. It is recommended that users should not use these data types; they are included here for completeness, but may not be supported on future versions of ADOCE or Windows CE.

Example

Dim rs, i, sql(12), sqlcmd
Set rs = CreateObject("adoce.recordset")
sql(0) = "create table allfields ("
sql(1) = "f1 varchar ,"     'adVarWChar
sql(2) = "f2 varchar(30),"  'adVarWChar
sql(3) = "f3 text ,"        'adLongVarWChar
sql(4) = "f4 varbinary ,"   'adVarBinary
sql(5) = "f5 varbinary (30) ," 'adVarBinary
sql(6) = "f6 long varbinary ," 'adLongVarBinary
sql(7) = "f7 int ,"         'adInteger
sql(8) = "f8 smallint ,"    'adSmallInt
sql(9) = "f9 float ,"       'adDouble
sql(10) = "f10 datetime ,"  'adDate
sql(11) = "f11 bit"         'adBoolean
sql(12) = ")"
For i = 0 To 12
sqlcmd = sqlcmd & sql(i)
Next
rs.open sqlcmd
rs.open "allfields"
MsgBox rs.fields.Count, , "Fields"
rs.Close
rs.open "drop table allfields"
Set rs = Nothing