-
August 5th, 2010, 01:55 PM
#1
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
-
August 5th, 2010, 07:26 PM
#2
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
-
August 6th, 2010, 12:23 AM
#3
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.
-
August 6th, 2010, 01:47 AM
#4
Re: Change column’s properties values
-
August 7th, 2010, 06:46 AM
#5
Re: Change column’s properties values
-
August 7th, 2010, 12:06 PM
#6
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)
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|