Change column property values using loop method in vb6

Question

0.00/5 (No votes)

See more:

How do I change all the column values (Nullable=True and Allow Zero Length=True) in a table except the 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 for just one table. I need to create more tables so setting Nullable property of each column is not practical.
Please help!

Here is the code:

VB

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 !PersonalID '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

Posted 5-Aug-10 8:44am

darryl_co

Updated 7-Aug-10 1:51am

v4

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

William Winner · Answer 1 · 2010-08-06T11:48:00

I would suggest that instead of trying to create your tables with code, create a blank database with all of the table structures already created. Then, add that db as a reference in your project and then when you want to create a new database, you just write that db to the file system.

And hopefully this is a typo from when you copied if over, but you have:

VB

With !PersonaltID 'AutoNumber.

(you put a "t" in between Personal and ID)

I would also guess that you can't tell it to change the Nullable property. That is set based on the type of the column. For example, an integer cannot be nullable AFAIK.