Change columnís properties values
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 6 of 6

Thread: Change columnís properties values

  1. #1
    Join Date
    Aug 2010
    Posts
    6

    Change columnís properties values

    How do I change all the column values (Nullable=True and Allow Zero Length=True) in a table except PersonalID column? When the script is executed I get the error "-2147217887 - Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done." This is the code only for one table.I need to create more tables so setting Nullable property of each column is not practical.Please Help!
    Here is the code

    Public Sub DBcreation()
    Dim tbl As New Table
    Dim cat As New ADOX.Catalog
    Dim col As ADOX.Column
    Dim prp As ADOX.Property
    Dim oCn As ADODB.Connection
    Dim sConStr As String


    'Set Connection string
    sConStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & App.Path & "\mydbase.mdb" & ";" & _
    "Jet OLEDB:Engine Type=4;"
    ' 1) Create the DataBase
    cat.Create sConStr

    ' 2) Create Table name "PDetails"
    tbl.Name = "PDetails"
    ' 3) Add Fields
    With tbl.Columns
    .Append "PersonalID", adInteger 'Number
    .Append "GHName", adVarWChar, 50 'Text
    .Append "FirstName", adVarWChar, 50 'Text
    .Append "FHName", adVarWChar, 50 'Text
    .Append "Surname", adVarWChar, 50 'Text
    .Append "BirthDate", adDate
    .Append "Gender", adVarWChar, 10 'Text
    .Append "Address", adLongVarWChar 'Memo
    .Append "Pincode", adInteger 'Number
    .Append "MobileNo", adInteger 'Number
    .Append "HomeNo", adInteger 'Number
    .Append "MaritalStatus", adVarWChar, 10 'Text
    .Append "Profession", adVarWChar, 50 'Text
    .Append "BloodGroup", adVarWChar, 10 'Text
    .Append "Photo", adVarWChar, 50 'Text

    ' 4) 'Set the field properties.


    With !PersonaltID 'AutoNumber.
    .ParentCatalog = cat
    .Properties("Autoincrement") = True
    .Properties("Description") = "Automatically " & _
    "generated unique identifier for this record."
    End With

    With !BirthDate
    Set .ParentCatalog = cat
    .Properties("Jet OLEDB:Column Validation Rule") = _
    "Is Null Or <=Date()"
    .Properties("Jet OLEDB:Column Validation Text") = _
    "Birth date cannot be future."
    End With

    End With
    ' 5) Save the Table to the DataBase
    cat.Tables.Append tbl
    ' 6) Set Column Properties
    For Each col In tbl.Columns
    For Each prp In col.Properties
    If col.Name <> "PersonalID" Then
    If prp.Name = "Nullable" Then
    prp.Value = True 'error generated
    '-2147217887 - Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
    End If
    End If
    Next
    Next

    'Clean up

    Set tbl = Nothing
    Set cat = Nothing
    set prp = Nothing
    End Sub

  2. #2
    Join Date
    Jan 2006
    Location
    Chicago, IL
    Posts
    14,998

    Re: Change columnís properties values

    Here's another approach...

    Code:
    Option Explicit
    
    ' Add a reference to Microsoft ActiveX Data Objects 2.x Library
    ' and Microsoft ADO Ext 2.x for DDL and Security
    
    Private Sub Form_Load()
    '=============================
    Dim tbl As New ADOX.Table
    Dim cat As New ADOX.Catalog
    Dim adoConn As New ADODB.Connection
    Dim strConString$
    Dim sSQL$, strLine$
    
        If Dir(App.Path & "\NewDB.mdb") = "" Then
            sSQL = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                   "Data Source=" & App.Path & "\NewDB.mdb;" & _
                   "Jet OLEDB:Engine Type=5"
            cat.Create sSQL
            
            tbl.Name = "Table1"
            tbl.Columns.Append "Field1", adInteger
            tbl.Keys.Append "PrimaryKey", adKeyPrimary, "Field1"
            tbl.Columns.Append "Date", adDBTimeStamp
            cat.Tables.Append tbl
            
            Set cat = Nothing
            Set tbl = Nothing
        End If
        
        strConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
                        & App.Path & "\NewDB.mdb;Persist Security Info=False"
        adoConn.Open strConString
        Dim x As Integer
        x = 1
        Open App.Path & "\test.txt" For Input As #1
            Do While Not EOF(1)
                Line Input #1, strLine
                If strLine <> "" Then
                    sSQL = "Insert Into Table1 (Field1,[Date]) Values (" & _
                       CInt(strLine) & ",#" & _
                       CDate(Now) & "#)"
               
               '     sSQL = "Insert Into Table1 (Field1) Values  (" & Chr(34) & strLine & Chr(34) & ")"
                    Debug.Print sSQL
                    adoConn.Execute sSQL
                End If
                x = x + 1
            Loop
        Close #1
        
        adoConn.Close
        Set adoConn = Nothing
    
    End Sub
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  3. #3
    Join Date
    Aug 2010
    Posts
    6

    Re: Change columnís properties values

    after execution of cat.Tables.Append tbl I get the error message "-2147217859 - Type is invalid."Even after adding on error resume next the loop is infinite. I even changed "Jet OLEDB:Engine Type=5" to Type=4 but the error is same.

  4. #4
    Join Date
    Jan 2006
    Location
    Chicago, IL
    Posts
    14,998

    Re: Change columnís properties values

    With my code?
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  5. #5
    Join Date
    Aug 2010
    Posts
    6

    Re: Change columnís properties values

    yes

  6. #6
    Join Date
    Jan 2006
    Location
    Chicago, IL
    Posts
    14,998

    Re: Change columnís properties values

    You've corrupted that table, as it must not have Table1 in it.

    Unzip the NewDB.mdb, and put it in the program's folder.

    Debug.Print:

    Insert Into Table1 (Field1,[Date]) Values (4,#8/7/2010 12:03:59 PM#)
    Insert Into Table1 (Field1,[Date]) Values (5,#8/7/2010 12:03:59 PM#)
    Insert Into Table1 (Field1,[Date]) Values (6,#8/7/2010 12:03:59 PM#)
    Insert Into Table1 (Field1,[Date]) Values (7,#8/7/2010 12:03:59 PM#)
    Insert Into Table1 (Field1,[Date]) Values (8,#8/7/2010 12:03:59 PM#)
    (my code running under Windows 7 x64)
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center