?
Solved

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

Posted on 2003-10-28
21
Medium Priority
?
1,057 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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
 

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 1000 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

649 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