null problem

I used the "create database from code" tutorial... and i came up with a problem.

The code works great, but it doesn't allow fields to be null, or rather, it doesn't allow fields to be empty.
I need to be able to have fields that allow null values. Anyone know what to do?

What method are you using to create your table. With the CREATE TABLE command, you can specify NOT NULL to restrict null records(without this, there should be no default restrictions). If using the ADOX library, the column properties can be used to specify null vs. not null. Can you post the code you are using to create this table.

I am using the adox method, so I have to specify for each field.
I will get my code posted sometime tomorrow...
thanks for your help!

OK, well here as an example, I add a new column to a table..You need to first create the table, and append that to the new ADOX catalog...and then, append your new column to the table. I am assuming for now this is Access, let me know if it's not, because I pass in the Jet Provider zero length properties...



Set col1.ParentCatalog = cat
col1.Name = "Field1"
col1.Type = adVarWChar
col1.Properties("Jet OLEDB:Allow Zero Length") = True
tbl.Columns.Append col1

If you are using an Access database, "Required" is the equivalent of NULLABLE - "Allow Zero Length" will allow empty strings ( "" ), but if the field has REQUIRED=True then you cannot set it to NULL.

Correct...but I assume if he set his fields to required, he wouldn't expect to be able to insert null values at the same time :).

Option Explicit
Private Declare Function DeleteFile Lib "kernel32" Alias "DeleteFileA" (ByVal lpFileName As String) As Long
Dim diagRes
Dim wait
Dim strConnection As String

Private Sub Command2_Click()
Dim objcat As adox.Catalog
Dim objMyTable As New adox.Table
Dim objMyTable2 As New adox.Table

diag.Flags = &H80000 Or &H2000
diag.ShowSave
If diag.FileName = "" Then Exit Sub
diagRes = diag.FileName

DeleteFile (diagRes)

ret:
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & diag.FileName

Set objcat = New adox.Catalog


objcat.Create strConnection
objcat.ActiveConnection = strConnection

If optBoth.Value = True Or optRegular.Value = True And listT1.ListCount > 0 And listT1.List(0) <> "" Then
With objMyTable
.Name = "Table1"
.Properties("Jet OLEDB:Allow Zero Length") = True
.Columns.Append "Date", adVarWChar, 50
.Columns.Append "Time", adVarWChar, 50
.Columns.Append "TimeOfDay", adVarWChar, 50
.Columns.Append "Distance", adVarWChar, 50
.Columns.Append "DistanceType", adVarWChar, 50
.Columns.Append "Comments", adVarWChar, 255
.Columns.Append "Liquids", adVarWChar, 255
.Columns.Append "EnergyBars", adVarWChar, 255
.Columns.Append "Course", adVarWChar, 255
.Columns.Append "Shoes", adVarWChar, 255
.Columns.Append "Partner", adVarWChar, 255
.Columns.Append "Weather", adVarWChar, 255
.Columns.Append "AverageSpeed", adVarWChar, 50
.Columns.Append "Unit", adVarWChar, 15
.Columns.Append "Food", adVarWChar, 255
.Columns.Append "Rating", adVarWChar, 10
.Columns.Append "Minute", adVarWChar, 50
.Columns.Append "Goal", adVarWChar, 255
.Columns.Append "HeartRate", adVarWChar, 10
End With
objcat.Tables.Append objMyTable
datT1.DatabaseName = diagRes
End If

If optBoth.Value = True Or optOther.Value = True And listT2.ListCount > 0 And listT2.List(0) <> "" Then
With objMyTable2
.Name = "Table2"
.Columns.Append "Date", adVarWChar, 50
.Columns.Append "Time", adVarWChar, 50
.Columns.Append "TimeOfDay", adVarWChar, 50
.Columns.Append "Distance", adVarWChar, 50
.Columns.Append "DistanceType", adVarWChar, 50
.Columns.Append "Comments", adVarWChar, 255
.Columns.Append "Liquids", adVarWChar, 255
.Columns.Append "EnergyBars", adVarWChar, 255
.Columns.Append "Course", adVarWChar, 255
.Columns.Append "Shoes", adVarWChar, 255
.Columns.Append "Partner", adVarWChar, 255
.Columns.Append "Weather", adVarWChar, 255
.Columns.Append "AverageSpeed", adVarWChar, 50
.Columns.Append "Unit", adVarWChar, 15
.Columns.Append "Food", adVarWChar, 255
.Columns.Append "Rating", adVarWChar, 10
.Columns.Append "Minute", adVarWChar, 50
.Columns.Append "Goal", adVarWChar, 255
.Columns.Append "HeartRate", adVarWChar, 10
End With
objcat.Tables.Append objMyTable2
datT2.DatabaseName = diagRes
End If

Set objMyTable2 = Nothing
Set objMyTable = Nothing
Set objcat = Nothing

datT1.Refresh
datT2.Refresh

End Sub



It starts the code by asking the user where to create the database, then it deletes the path returned in case it exists, then it creates the database...
hopefully this will help you guys help me...

Also, here are my references used by my program:
Visual Basic For Applications
Visual Basic runtime objects and procedures
Visual Basic objects and procedures
OLE Automation
Microsoft DAO 3.51 Object Library
Microsoft ActiveX Data Objects 2.5 Library
Microsoft Data Binding Collection VB 6.0 (SP4)
Microsoft Data Formatting Object Library 6.0 (SP4)
Windows Media Player
Microsoft Connection Designer Instance 1.0
Microsoft Remote Data Object 2.0
Microsoft Data Report Designer 6.0 (SP4)
Microsoft Internet Controls
Microsoft ADO Ext. 2.7 for DLL and Security
Microsoft Data Source Interfaces

I'm glad you got it working. Good luck

yes, my code DOES work, but I can't get it to set fields as non-required...
maybe you could tell me what i did wrong???
thanks!

Sigh...did you even try the code I posted? Where is it if you did?.....:). After you append the new table to your parent catalog, you need to create a new column object, and assign the properties as I showed...then append the new column to the table.

Make sure any fields you want to accept a zero length string have there required flag set to false.

ok, i changed my code to include your .property method... but I have yet to actually try it... (My project is currently at home, and I am at school) I have one more question before I do:
should I change the "Jet OLEDB:Allow Zero Length" to be included in this statement instead? (sorry if that is a stupid question)
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & diag.FileName

sorry that i am such a pain to work with... I appreciate your patience!

No, that goes with the column object itself. This has nothing to do with the Connection to the mdb file. In the code I create a new ADOX column object, then I assign the properties to that column. Coll.Properties().

Ok, thanks much.

Just implemented your code, works perfectly!
Thanks man!

:). Good luck