Adox

[Roshec]

I'm trying to alter a column (set a field to nullable) using ADOX with Jet 4.0 OLE DB provider, however I've been getting some errros trying to do it.
After surfing MSDN, I found this --- BUG: Attributes Property of ADOX Columns Collection May Cause Append Method to Fail. In this particular document, the workaround is -- If you need to create a nullable or fixed length column, you must create the table through some mechanism other than ADOX, such as DDL SQL statements (CREATE TABLE, for example).
After a few more surfing, I found yet another document (A More Powerful SQL
The Jet 4.0 ANSI SQL-92 Extensions
by Paul Litwin, Ken Getz, and Mike Gilbert). It discussed Jet 4.0's enhanced ANSI-SQL support including ALTER COLUMN, but didn't have anything about setting (altering) a column Nullable property.
Would anyone out there know what I can do to achieve this???
Thanks heaps!!!

[gab2001uk]

This may be old-fashioned but if you need that much control you need to use good old DAO, as admitted by Micro$oft themselves: http://msdn.microsoft.com/library/de...cesstables.asp

Quote from the bottom of this ADOX page:

You must establish a reference to the Microsoft DAO 3.6 object library and use DAO code to programmatically set these remaining table properties: Description, Filter, OrderBy, LinkChildFields, LinkMasterFields, SubdatasheetExpanded, SubdatasheetName, and SubdatasheetHeight. For information about working with these properties, search Microsoft Access Help.

[oh1mie]

Public Sub subCreateNewDatabase(DbFile As String)
    
    On Local Error Resume Next

    Dim cat As New ADOX.Catalog
    Dim tbl As New ADOX.Table
    Dim idx As New ADOX.Index
   
    'Engine Type=5 = access 200, Engine Type=4 = access 97
    cat.Create "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & DbFile & _
               ";Jet OLEDB:Engine Type=5"
   'adBoolean        Boolean
   'adInteger        Long
   'adSmallInt       Integer
   'adLongVarWChar   Memo
   'adDate           Date
   'adDouble         Double
   'adCurrency       Currency
   'adLongVarBinary  OLE Object
   'adWChar          Text
    
    tbl.Name = "MyTable"
    Set tbl.ParentCatalog = cat
    subAddField tbl, "Timefield", adDate
    subAddField tbl, "Numericfield", adDouble
    subAddField tbl, "MemoField", adLongVarWChar

    cat.Tables.Append tbl
   
    Set cat = Nothing
    
End Sub
Private Sub subAddField(tbl As ADOX.Table, Name As String, DataType As Integer, Optional FieldLenght As Integer, Optional Auto As Boolean)
      
    On Local Error Resume Next
    
    Select Case DataType
    Case adInteger, adDate, adDouble, adCurrency, adSmallInt
      tbl.Columns.Append Name, DataType
      If Auto Then tbl.Columns(Name).Properties("AutoIncrement") = True
      tbl.Columns(Name).Properties("Nullable") = True
    Case adBoolean
      tbl.Columns.Append Name, DataType
    Case adWChar
      tbl.Columns.Append Name, DataType, FieldLenght
      tbl.Columns(Name).Properties("Jet OLEDB:Allow Zero Length") = True
      tbl.Columns(Name).Properties("Nullable") = True
    Case adLongVarWChar, adLongVarBinary
      tbl.Columns.Append Name, DataType
      tbl.Columns(Name).Properties("Jet OLEDB:Allow Zero Length") = True
      tbl.Columns(Name).Properties("Nullable") = True
    End Select

End Sub

[oh1mie]

Here is code to add new column to table with ADOX

   Dim cat     As New ADOX.Catalog
    Dim tbl     As New ADOX.Table
    Dim Col     As New ADOX.Column

    cat.ActiveConnection = strConnectCommand & _
         DbFile & ";"

    Col.name = FieldName
    Col.Attributes = adColNullable
    If Pituus > 0 Then Col.DefinedSize = FieldLenght
    Col.Type = FieldStyle
    cat.Tables.item(Mytable).Columns.Append Col
    cat.Tables.item(Mytable).Columns.item(FieldName).Properties("Jet OLEDB:Allow Zero Length") = True

    Set cat = Nothing

[oh1mie]

Sorry there if one word, what need little bit explanation.

If Pituus > 0 Then Col.DefinedSize = FieldLenght

Id did cut this code from my project and word "Pituus" is finnish and mean lenght. It's optional parameter what is given to sub

[Roshec]

Thanks for the code snippet oh1mie

I'm having trouble with this:

tbl.Columns(Name).Properties("Nullable") = True

I'm getting this error.

-2147217887 Multiple-step OLE DB Operation generated errors. Check each OLE DB status value, if available. No work was done.

Here's my connection string, as you can see it's access 2000.
Also, (this is just a side issue for me) do you know if there's a SEEK function anywhere out there for SQL Server/Oracle?

Provider=Microsoft.Jet.OLEDB.4.0;Password="";User ID=Admin;Data Source=D:\idt_fa\fa-temp2\37309000.MDB;Mode=ReadWrite;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDBatabase Password="";Jet OLEDB:Engine Type=5;Jet OLEDBatabase Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDBon't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False

[oh1mie]

Originally posted by Roshec
Thanks for the code snippet oh1mie

I'm having trouble with this:

tbl.Columns(Name).Properties("Nullable") = True
......

Use on proserude:

On local error Resume next

It passing lines what not able to field style

[Roshec]

Thanks again oh1mie...but I just copied your snippet and it's still giving me problems...

Case adInteger, adDate, adDouble, adCurrency, adSmallInt
Tempfield.Properties("Nullable") = True
Case adVarWChar, adLongVarWChar, adLongVarBinary
Tempfield.Properties("Jet OLEDB:Allow Zero Length") = True
Tempfield.Properties("Nullable") = True

[oh1mie]

Propably you must update your ADO referenses
Do you have a latest MDACs ?
Install allso Microsofts MS Jet 4.0 Service Pack 3