What's the syntax for the create table statement in Excel?

Question

I'm using ODBC, OLE is available if needed to dump approx 3 different types of data to different excel sheets. But to use insert statements in excel, the table has to be created first I believe, especially if the sheet doesn't exist yet.

I used to use sql server 2000's import/export wizard that automatically generated the create table statement.

So does anyone know the data types, and syntax for a create statement or the location of a good resource for excel sql create statements?

Answer 1

Try here: Using SQL in VBA

Answer 2

So that this is easy to find here's an example from Leonel Martins' Link:

CREATE TABLE DataSource
(Staff_Nox NUMBER,
Salx CURRENCY,
Namex TEXT,
Boolyx BIT,
Regionx NUMBER,
Datex DATETIME)

Also of note is that for text and datetime it appears to want to wrap data in a special character:

Select Case fGetCellFormat(.Cells(2, viCount))

    Case "TEXT"

        vtWrapChar = """" 

    Case "DATETIME"

        vtWrapChar = "#" 

    Case Else

        vtWrapChar = "" 

End Select

I could swear there were more types, like char and varchar

Answer 3

The create table syntax given to me on that sheet failed with the message Could not modify the design of table 'Data' it is a read-only database.

Using OleDb This worked:

CREATE TABLE `sheetname` (
`DartNumber` VarChar (16)
, `ClientId` VarChar (10)
, `Population` VarChar (30))

but for some reason failed saying read-only using odbc.