Solved

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

Posted on 2003-10-28
21
929 Views
Last Modified: 2009-12-16
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?
0
Comment
Question by:bmihura
  • 8
  • 7
  • 6
21 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 9640580
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
 

Author Comment

by:bmihura
ID: 9642918
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
 
LVL 39

Expert Comment

by:stevbe
ID: 9643647
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
 
LVL 39

Expert Comment

by:stevbe
ID: 9643699
you need to reference the property through the Item collection

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

Steve
0
 

Author Comment

by:bmihura
ID: 9643955
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
 
LVL 77

Expert Comment

by:peter57r
ID: 9643982
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
 
LVL 77

Expert Comment

by:peter57r
ID: 9644052
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
 

Author Comment

by:bmihura
ID: 9644713
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
 
LVL 77

Expert Comment

by:peter57r
ID: 9645104
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
 
LVL 77

Expert Comment

by:peter57r
ID: 9645187
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:bmihura
ID: 9645288
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
 

Author Comment

by:bmihura
ID: 9645315
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
 
LVL 77

Expert Comment

by:peter57r
ID: 9645504
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
 

Author Comment

by:bmihura
ID: 9645621
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
 
LVL 39

Accepted Solution

by:
stevbe earned 250 total points
ID: 9649540
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
 
LVL 77

Expert Comment

by:peter57r
ID: 9649767
Thank goodness for that.(:-)

Pete
0
 
LVL 39

Expert Comment

by:stevbe
ID: 9649797
lol ... good morning Pete
0
 

Author Comment

by:bmihura
ID: 9651014
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
 
LVL 39

Expert Comment

by:stevbe
ID: 9651174
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
 

Author Comment

by:bmihura
ID: 9651440
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
 
LVL 39

Expert Comment

by:stevbe
ID: 9652948
You are welcome, we all know the "secret" now :-)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now