How do I create an "Allow Zero Length" text field from VBScript?

I'm trying to create a Microsoft Access database with a text field having its "Allow Zero Length" property set to True. I want to do this in ASP, using VBScript and ADOX.

This code shows me that I can display the columns properly, because it shows me the column names and types:

For Each col In .Columns
  Response.Write(col.Name & ", " & col.Type & "<br>")
'  If col.Name = "Phone" Then
'  col.Properties("Jet OLEDB:Allow Zero Length") = True
'  End If
Next

(previously in the code, "col" was set with "Set col = CreateObject ("ADOX.Column")")

But when I uncomment those three lines, I get the following error from the middle line:

Error Type:
ADODB.Properties (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.

Why doesn't this code work?
bmihuraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter57rCommented:
I think I can give you the general idea but maybe not the code you need.
The issue you have is that the properies you see for a field in the Access table design view are ACCESS properties, not JET properties. (They only exist if you create them through MS-ACCESS table designer).

If you want to set the property on a field you create in code you also have to create the property before you can read/write it.

In VBA the code you would need would be similar to this:

Set prop = fld.CreateProperty("Format", dbText, "Fixed")
fld.Properties.Append prop

But I'm not sure whether/how this translates into your code environment.

Pete
0
bmihuraAuthor Commented:
Bleah, ADOX doesn't have the CreateProperty method nor "fields".

Concerning code environments, I'd settle for *any* means of creating a Microsoft Access table with "Allow Zero Length" text field from my ASP web page, it doesn't have to be ADOX or VBScript.
0
stevbeCommented:
when i used this function to add a field to a table and then look at it in Access the "Allow Zero Length" is YES so it appears as if it is the default anyway. Are you creating new columns or trying to modify existing column?

Public Function AddField()
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column
Dim prp As ADOX.Properties

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

Set tbl = cat.Tables("Table1")
Set col = New ADOX.Column
col.Name = "ZLT"
col.Type = adVarWChar
tbl.Columns.Append col

End Function

Steve
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

stevbeCommented:
you need to reference the property through the Item collection

col.Properties.Item("Jet OLEDB:Allow Zero Length") = False

Steve
0
bmihuraAuthor Commented:
Hey Steve, your 09:19AM post looks like VB code, not VBScript, as it doesn't like the variables to be typecast. The error:

Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/vi_techsurvey/TMP14vehnj5dl.asp, line 4, column 8
Dim cat As ADOX.Catalog
-------^

**************

I was betting your 09:25AM line of code would work, but no-- I've shown the
error as a comment in the source code below.

This is the [dysfunctional] function CreateTable, which assumes a new, empty Microsoft Access database is here C:\temp\test.mdb, and tries to create the table "Contacts" from scratch:

sub CreateTable
 dim catDB
 dim tblNew
 Set catDB = CreateObject ("ADOX.Catalog")
 Set col = CreateObject ("ADOX.Column")
 
 catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= C:\temp\test.mdb;User Id=admin;Password="
 Set tblNew = CreateObject ("ADOX.Table")
 With tblNew
  .Name = "Contacts"
  With .Columns
   .Append "ID", adInteger
   .Append "FirstName", adVarWChar, 50
   .Append "LastName", adVarWChar, 50
   .Append "Phone", adVarWChar, 50
  End With
  With .Keys
   .Append "PK_ID", 1, "ID"
  End With
  For Each col In .Columns
   Response.Write(col.Name & ", " & col.Type & "<br>")
   If col.Name = "Phone" Then
    col.Properties.Item("Jet OLEDB:Allow Zero Length") = True
' The previous line causes this error:
' Error Type:
' ADODB.Properties (0x800A0CC1)
' Item cannot be found in the collection corresponding to the requested name or ordinal.
   End If
  Next
 End With
 catDB.Tables.Append tblNew
 Set tblNew = nothing
 Set catDB = Nothing
 Set col = Nothing
end sub
0
peter57rCommented:
Have a look here:
its rather a long reference:

http://groups.google.com/groups?q=set++ado+OR+asp+%22allow+zero+length%22+group:*access*&hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=ehlXHFg7AHA.1720%40tkmsftngp04&rnum=22

about a thid way down there is this
 ' ----------------------------------------------------
    If [Include] = True Then ' add new field
    ' ----------------------------------------------------
Look about a dozen lines below that.

Pete
0
peter57rCommented:
The issue appears to be that you have to set the property BEFORE appending the field - that's how I am reading it anyway.

Pete

0
bmihuraAuthor Commented:
Hey Pete,

I tried the concept of setting the property before appending the field.

This code works fine until I uncomment that one line:

sub CreateTable
 dim catDB
 dim tblNew
 Set catDB = CreateObject ("ADOX.Catalog")
 Set col = CreateObject ("ADOX.Column")
 
 catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= C:\temp\test.mdb;User Id=admin;Password="
 Set tblNew = CreateObject ("ADOX.Table")
 With tblNew
  .Name = "Contacts"
  With .Columns
   .Append "ID", adInteger
   .Append "FirstName", adVarWChar, 50
   .Append "LastName", adVarWChar, 50
    col.Name = "Phone"
    col.Type = adVarWChar
    col.DefinedSize = 50
'    col.Properties("Jet OLEDB:Allow Zero Length") = True
   .Append col
  End With
 Response.Write(tblNew.Name)
 End With
 catDB.Tables.Append tblNew
 Set tblNew = nothing
 Set catDB = Nothing
 Set col = Nothing
end sub

Here's the exact error message on that line when it's uncommented:
Error Type:
ADODB.Properties (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.

Bruce
0
peter57rCommented:
I've run your code in Access and apart from a missing 'dim col' statement it runs OK.

This now looks as if it may be a wild goose chase.
When I open the database after running the code, the Allow Zero Length property is set to True.
(With the line still commented out)
Of course I can't be sure that it's not tha act of openeing it in ACcess that is resolving this but I'll see what the properties say in DAO without opening the databse in Access.

Back shortly

Pete

0
peter57rCommented:
Sorry. A false dawn.
If I don't open the test.mdb in Access then the property value returned in code is 'false.'
Once I have opened the database in Access it gets set to True.
Isn't that a b*7^%5%5.

Can't give up now!

Pete

0
bmihuraAuthor Commented:
Weird! My "Microsoft Access 2000 (9.0.2720) does not set that property to True when I open it in Access.

I'll be on guard for that "feature" though.

Bruce
0
bmihuraAuthor Commented:
Also, I wonder why it would run on your computer (except for the "Dim col", I forgot to "Option Explicit") and not mine?

Bruce
0
peter57rCommented:
I'm testing on Access 2002.  
Maybe there's yet another factor in the mix!

I now know more than I ever want to about this topic and I'm still nowhere.
There seems to be a lot of people with similar problems and there appears to be quite a deal of inconsistency in results.  I'm also fascinated to read that if you  create a field through SQL DDL , and then test the setting of AllowZeroLength you get back the WRONG answer.

But I'm afraid it's bedtime for me, so I'll have another look in the morning if it hasn'rt been solved by then.

Pete
0
bmihuraAuthor Commented:
Well, I appreciate your looking into it.

I searched Microsoft's support site in case this is a bug-- there were some "Allow Zero Length"-related bugs, but none seemed related to VBScript.

Bruce
0
stevbeCommented:
I believe I have found the information required to make this work ... the column must be appended to the table and you have to set the parent catalog property of the column before setting Access specific properties.

Sub CreateTable()
 Dim cat    'As ADOX.Catalog
 Dim tbl    'As ADOX.Table
 Dim col    'As ADOX.Column
 
 Set cat = CreateObject("ADOX.Catalog")
 cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= C:\temp\test.mdb;User Id=admin;Password="
 Set tbl = CreateObject("ADOX.Table")
 
 With tbl
  .Name = "Contacts"
  With .Columns
    Set col = New ADOX.Column
    col.Name = "Phone"
    col.Type = adVarWChar
    col.DefinedSize = 50
    .Append col
    col.ParentCatalog = cat
    col.Properties.Item("Jet OLEDB:Allow Zero Length") = False
  End With
 End With
 cat.Tables.Append tbl
 cat.Tables.Refresh
 
 Set tbl = Nothing
 Set cat = Nothing
 Set col = Nothing
End Sub

Steve
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
peter57rCommented:
Thank goodness for that.(:-)

Pete
0
stevbeCommented:
lol ... good morning Pete
0
bmihuraAuthor Commented:
You figured it out, Steve! Wow, that was tricky.

One trivial question, only if you happen to know it:

I had to replace your line

Set col = CreateObject ("ADOX.Column")

with

Set col = New ADOX.Column

Otherwise I get this error on your version of the line:

Microsoft VBScript runtime (0x800A01FA)
Class not defined: 'ADOX'

I'm already including "adovbs.inc", but is there some ADOX-related include file you're using that defines that class?
0
stevbeCommented:
I think you may have posted you last question backwards ... you had to replace

Set col = New.ADOX.Column

with

Set col = CreateObject("ADOX.Column")

this was simply an oversight on my part when I converted from Early bound (ADOX.xxx) to Late Bound CreateObject("ADOX.xxx")

Steve
0
bmihuraAuthor Commented:
Steve,

Thanks again for figuring that out, who would have though it would be so difficult to create a simple database with "Allow Zero Length" text fields?

Bruce
0
stevbeCommented:
You are welcome, we all know the "secret" now :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.