PRB: ADOXCE Error Message: "..Specified type was invalid" for SQL Server CE Table (274255)

MORE INFORMATION

The following code demonstrates the error. Replace the data source name in the connection string with an existing database name.

Dim cat, tbl, col, stTableName, strConn  

    Set cat = CreateObject("adoxce.catalog.3.1")
    Set col = CreateObject("adoxce.column.3.1")
    Set tbl = CreateObject("adoxce.table.3.1")

    strConn = "Provider=Microsoft.SQLServer.OLEDB.CE.1.0;Data Source=\Test.sdf;"

    col.Name = "Col1"    
    ' default Type is 202 adVarWChar, and DefinedSize is not specified

    tbl.Name = "temp_table"
    tbl.Columns.Append col
    
    cat.ActiveConnection = strConn
    If adox_ExistsTable(cat, tbl.Name) Then
        cat.Tables.Delete tbl.Name
    End If
    
    On Error Resume Next
    cat.Tables.Append tbl       
    '
    'An error was encountered while running this program, A specified type was invalid
     
      Debug.Print Hex(Err.Number) & " -  " & Err.Description

'Err.Number : -2147217859 
'Err.Description : A specified type was invalid 
				

The same error occurs if the length was not specified, that is: The following code corrects the problem and creates the table. Replace the data source name in the connection string with an existing database name.

    Dim cat, tbl, col, stTableName, strConn  

    Set cat = CreateObject("adoxce.catalog.3.1")
    Set col = CreateObject("adoxce.column.3.1")
    Set tbl = CreateObject("adoxce.table.3.1")

    strConn = "Provider=Microsoft.SQLServer.OLEDB.CE.1.0;Data Source=\Test.sdf;"
    
    col.Type = 202 'which is adVarWChar
    col.Name = "Col1"
    col.DefinedSize = 10

    tbl.Name = "temp_table"
    tbl.Columns.Append col
    
    cat.ActiveConnection = strConn
    If adox_ExistsTable(cat, tbl.Name) Then
        cat.Tables.Delete tbl.Name
    End If
    
    On Error Resume Next
    cat.Tables.Append tbl        
   
    Debug.Print Hex(Err.Number) & " -  " & Err.Description

'Err.Number : 0
'Err.Description : Empty