Create table using ADOX, "required"property question 4

[Vie]

Hi everyone,

I noticed that when creating a new table from a module using ADOX, the "Required" property for every field ("column" to ADO) in the table is automatically set to Yes.

Is there a way to change this property to No/False from the create table sub?

With tblApartmentsTest.Columns("ProjectID")
     .Properties("Required") = False
End With

Thanks,
Vie

 

[sucoyant]

Do you need to use ADOX? Are you using Access?

________________________________________

Buddha. Dharma. Sangha.

 

[Vie]

Yes, Access. I'm using ADOX to build a temporary table (killed after the module closes) and load data into it on the fly. The data for some of the fields is added at a later step. When I try to insert data into the other rows, because the Required property of all of the fields is set to Yes, it debugs out of code before any data is inserted, saying the other fields -- well, obviously - "require" data.

It isn't absolutely crucial to set the Required property in this exact spot. I can think of other ways around the problem but it would be nicer and easier to maintain if the code for this particular set of actions is in the same module. I figure there's got to be a way to set this property from code but I'm not sure how to reference it. Every permutation I've tried so far hasn't got the job done.

Thanks for replying!
Vie

 

[sucoyant]

The reason I ask is because I'm crating a table programmatically like so:

Dim str1 As String
Dim str0 As String
Dim varItem As Variant
Dim TD As ListBox

txtGrpName.SetFocus
str0 = Me.txtGrpName.Text

Set TD = Me!lstTableData

strSQL = "CREATE TABLE " & str0 & " (Employee TEXT(255),AccountNum TEXT(50), theDate DATETIME, Reviewer TEXT(255),Comments MEMO, GoodBad YESNO, Cust_LName TEXT(255), Cust_FName TEXT(255)"

For j = 0 To TD.ListCount - 1
    TD.Selected(j) = True
Next j

For Each varItem In TD.ItemsSelected
strAppendSQL = strAppendSQL & ", " & Me.lstTableData.ItemData(varItem) & " YESNO"
Next varItem

strSQL = strSQL & strAppendSQL & ")"

DoCmd.RunSQL strSQL

MsgBox "Table Created"

I simply used a "CREATE TABLE" statement.
First I grab the items that the user entered into a listbox, then I append that to the end of 'strSQL' because the table needs to have the fields in 'strSQL'. That way I have all the fields I need, and then all the user defined fields.

I'm not sure if this will help...

________________________________________

Buddha. Dharma. Sangha.

 

[sucoyant]

A bit more info on the sql CREATE TABLE from w3 schools

http://www.w3schools.com/sql/sql_create.asp

________________________________________

Buddha. Dharma. Sangha.

 

[vbajock]

Required is set via Nullable property:

tbl.Columns(Fld).Properties("Nullable") = False

 

[Vie]

Thanks for your suggestion sucoyant but I think I'll try to stick with the same horse for now.


Thank you too, vbajock -- I'm getting run-time error 3265 "ADO could not find the object in the collection corresponding to the name or ordinal reference requested by the application."

Vie

 

[cmmrfrds]

Is this using the Access OLEDB Provider - I assume Jet.

In any case, you can iterate through the property collection to find the correct property name.

Set cg.ActiveConnection = CurrentProject.Connection
'-- the above could be any provider.
Set tb = cg.Tables("test")
Debug.Print "table name = "; tb.Name
Dim pp As Property
For Each cl In tb.Columns
Debug.Print "name = "; cl.Name
Debug.Print "type = "; cl.type
For Each pp In cl.Properties
Debug.Print "property name = "; pp.Name
Debug.Print "property value = "; pp.Value
Next
Next

Did you use quotes on the field name.
tbl.Columns("Fld").Properties("Nullable") = True

OR it may need to be the zero length property.
tbl.Columns("Fld").Properties("Jet OLEDB:Allow Zero Length") = True

 

[Vie]

hi cmmrfrds,

Yes, I put the field name in quotes and, yes, the provider is Jet.

Thanks for the excellent test code - I can see that's going to be a very useful tool!

In any case, both properties are there:

Nullable set to True
Jet OLEDB:Allow Zero Length set to False

So it seems it's the Jet OLEDB property that is responsible for "Required" being set to false.

tbl.Columns("BuildingID").Properties("Jet OLEDB:Allow Zero Length") = True

Still gets error 3265.

Do I maybe need to use a particular keyword or structure or ??? to set this?

Vie

 

[cmmrfrds]

Here is the syntax I have used.

Dim cl As ADOX.Column, tb as ADOX.Table
Set tb.Name = "YourTable"
Set cl = tb("BuildingID")
Debug.Print cl
cl.Properties("Jet OLEDB:Allow Zero Length") = True

 

[Vie]

cmmrfrds,

I tried your syntax and still got the error. So I tried

With tbl.Columns("BuildingID")
.Properties("Jet OLEDB:Allow Zero Length") = True
End With

and I still get the error.

I've been developing this database since December or so and at this point it's getting pretty buggy. It's not terribly complex but I've had a lot of inexplicable and mysterious problems. Like, since yesterday I am unable to compact and repair the database because it now tells me 'Admin' is using it. 'Admin' is me. There are, at this point, no other users. Three days ago, compact/repair worked.

I tried to download the 1a service pack from Microsoft the other day hoping to fix some of the bugginess. It took three hours but it never completed the final reboot step. When I try to rerun it (and you are supposed to be able to run it twice) it tells me it's already installed and won't let me do anything with the exe file. Meanwhile, none of the things it was supposed to fix are fixed.

Argghh....just needed to vent after a long day.

Anyway, thanks for all your help. I'm sure your code is right - it's probably just a matter of remedying my MS Office Pro ills.

Vie

 

[cmmrfrds]

Create a new mdb and then import all your objects into the new mdb.

 

[cmmrfrds]

I didn't see how you are creating the catalog object, but that may be the problem. Check out the following Microsoft Link.

http://support.microsoft.com/defaul...port/kb/articles/q201/8/26.asp&NoWebContent=1

 

[cmmrfrds]

I think this is the key. You are trying to do everything in one session where in my example I had already created the table and came back and set the property. Apparently, in your case the parent catalog must be set.

Excerpt from Microsoft.
"Some data providers allow provider-specific property values to be written only at creation (when a table or column is appended to its Catalog collection). To access these properties before appending these objects to a Catalog, specify the Catalog in the ParentCatalog property first."

 

[Vie]

Thanks so much, cmmrfrds. In a shortened version of my sub (code relevant to problem only), thanks to your advice, here's what works:

Dim cat As ADOX.Catalog

    Set cat = New ADOX.Catalog
    cat.ActiveConnection = CurrentProject.Connection
    Set cnn = cat.ActiveConnection

    With tbl.Columns("BuildingID")
          .ParentCatalog = cat 
          .Properties("Jet OLEDB:Allow Zero Length") = True
    End With

I think I'll actually be able to get some sleep tonight after all!

ps - I will try copying all my objects and data over to a new project. Hopefully that will cure some of the kookiness.

Thanks again,
Vie