UnicodeCompression again

[Ronald]

Hi.

The answer I got to programmatically set the UnicodeCompression of a text
field seemd right but isn't
Currentdb().TableDefs("Faq0").Fields("fSubject").Properties("UnicodeCompression") = True

When I use it, I get a run-time error 3265: Item not found in this collection.
I also tried: "Unicode Compression", "Unicode-Compression" and
"Unicode_Compression", but that didn't make the error disappear.

It should be a built-in property but maybe it isn't and must I make a
user-defined property of it first.
Any hints?
Thanks!

Ronald.

 

[Ralph]

I created a table named FaQ0 with a field in it named fSubject and it worked
for me. Item not found usually means Access can't find the table or the
field, are you sure about the table and field names??

 

[Ronald]

I have to admit that line of code is not my code.
This is my code:

Set tdfNew = [db].CreateTableDef("tbl" & ![Keuken])
With [tdfNew]
Set fldNew = .CreateField("StraatnaamOfficieel", dbText, 50)
With [fldNew]
.AllowZeroLength = False
.Required = True
.Properties("Unicode compressie") = True
End With
.Fields.Append [fldNew]
Set fldNew = Nothing

..Fields("StraatnaamOfficieel").Properties("UnicodeCompression") = True
.Fields.Refresh
End With
[db].TableDefs.Append [tdfNew]
Set tdfNew = Nothing
[db].TableDefs.Refresh

You see the .Properties in 2 lines. They both give the same error.

Ronald.

 

[John Spencer]

I would suggest that you write a little routine to step through all the
field properties of an existing text field and see what is returned.

Dim fldAny as DAO.Field, I as integer
Set fldANy = Currentdb().TableDefs("SomeTable").Fields("SomeTextField")

For I = 0 to fldAny.Properties.Count - 1

Debug.Print fldany.Properties(I).Name
Next I


That should show you the names of the properties and then you should be
able to pick out UnicodeCompression property.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have to admit that line of code is not my code.
This is my code:

Set tdfNew = [db].CreateTableDef("tbl" & ![Keuken])
With [tdfNew]
Set fldNew = .CreateField("StraatnaamOfficieel", dbText, 50)
With [fldNew]
.AllowZeroLength = False
.Required = True
.Properties("Unicode compressie") = True
End With
.Fields.Append [fldNew]
Set fldNew = Nothing

.Fields("StraatnaamOfficieel").Properties("UnicodeCompression") = True
.Fields.Refresh
End With
[db].TableDefs.Append [tdfNew]
Set tdfNew = Nothing
[db].TableDefs.Refresh

You see the .Properties in 2 lines. They both give the same error.

Ronald.

I created a table named FaQ0 with a field in it named fSubject and it worked
for me. Item not found usually means Access can't find the table or the
field, are you sure about the table and field names??

 

[Ronald]

I added your code to mine and this is the result (Access 2007):
Value
Attributes
CollatingOrder
Type
Name
OrdinalPosition
Size
SourceField
SourceTable
ValidateOnSet
DataUpdatable
ForeignName
DefaultValue
ValidationRule
ValidationText
Required
AllowZeroLength
AppendOnly
FieldSize
OriginalValue
VisibleValue

So NO UnicodeCompression!!!
I did the same on my old computer (with Access 2003) and that gave the same
result (exept AppendOnly is not available).
Both my Office suites are genuine.

If UnicodeCompression should be available what can be the matter with my
computers?

Ronald.

I would suggest that you write a little routine to step through all the
field properties of an existing text field and see what is returned.

Dim fldAny as DAO.Field, I as integer
Set fldANy = Currentdb().TableDefs("SomeTable").Fields("SomeTextField")

For I = 0 to fldAny.Properties.Count - 1

Debug.Print fldany.Properties(I).Name
Next I


That should show you the names of the properties and then you should be
able to pick out UnicodeCompression property.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have to admit that line of code is not my code.
This is my code:

Set tdfNew = [db].CreateTableDef("tbl" & ![Keuken])
With [tdfNew]
Set fldNew = .CreateField("StraatnaamOfficieel", dbText, 50)
With [fldNew]
.AllowZeroLength = False
.Required = True
.Properties("Unicode compressie") = True
End With
.Fields.Append [fldNew]
Set fldNew = Nothing

.Fields("StraatnaamOfficieel").Properties("UnicodeCompression") = True
.Fields.Refresh
End With
[db].TableDefs.Append [tdfNew]
Set tdfNew = Nothing
[db].TableDefs.Refresh

You see the .Properties in 2 lines. They both give the same error.

Ronald.

I created a table named FaQ0 with a field in it named fSubject and it worked
for me. Item not found usually means Access can't find the table or the
field, are you sure about the table and field names??

:

Hi.

The answer I got to programmatically set the UnicodeCompression of a text
field seemd right but isn't:
Currentdb().TableDefs("Faq0").Fields("fSubject").Properties("UnicodeCompression") = True

When I use it, I get a run-time error 3265: Item not found in this collection.
I also tried: "Unicode Compression", "Unicode-Compression" and
"Unicode_Compression", but that didn't make the error disappear.

It should be a built-in property but maybe it isn't and must I make a
user-defined property of it first.
Any hints?
Thanks!

Ronald.

 

[John Spencer]

That is strange.

I get the UnicodeCompression property for all JET databases where the field
type is text or memo. I am not using Access 2007 actively. My office is
slowly starting to upgrade some computers to Office 2007 (5% of users to date)
- but is still sticking to Access 2000 to 2003 for almost all databases.

If you open the table in design view, does the property show?

If you create the field, save it, and then try to modify the property does
that work?

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Ronald]

Yes, result!!!
With an existing table the properties are:
Value
Attributes
CollatingOrder
Type
Name
OrdinalPosition
Size
SourceField
SourceTable
ValidateOnSet
DataUpdatable
ForeignName
DefaultValue
ValidationRule
ValidationText
Required
AllowZeroLength
AppendOnly
FieldSize
OriginalValue
VisibleValue
ColumnWidth
ColumnOrder
ColumnHidden
DisplayControl
IMEMode
IMESentenceMode
UnicodeCompression
TextAlign
AggregateType

But it is still very strange, because now there are 9 more properties than
the first time and what do I have to do to get them (the fist time I put your
code after adding and refreshing the field and tabledef so the table/database
should be updated completely).

Now I've tried several options with no result. The only way to get the
'extra' properties is to manually open the new table in design mode, set
UnicodeCompression to Yes and confirm the change when closing. (?)

This is my test code. Maybe you can try it on your system to confirm my
results:

Private Sub basTestCreateTable()

Dim db As Database
Dim tdfNew As TableDef
Dim fldNew As Field
Dim idxNew As Index

Set db = CurrentDb
With [db]
If (TableExists("tblAmsterdam") = True) Then .TableDefs.Delete
("tblAmsterdam")

Set tdfNew = [db].CreateTableDef("tblAmsterdam")
With [tdfNew]
Set fldNew = .CreateField("StraatnaamOfficieel", dbText, 50)
With [fldNew]
.AllowZeroLength = False
.Required = True
' .Properties("UnicodeCompression") = True
End With
.Fields.Append [fldNew]
Set fldNew = Nothing
' .Fields("StraatnaamOfficieel").Properties("UnicodeCompression") = True
.Fields.Refresh
End With
[db].TableDefs.Append [tdfNew]
Set tdfNew = Nothing
[db].TableDefs.Refresh

Dim fldAny As DAO.Field, I As Integer

Set fldAny = .TableDefs("tblAmsterdam").Fields("StraatnaamOfficieel")
For I = 0 To fldAny.Properties.Count - 1
Debug.Print fldAny.Properties(I).Name
Next I
Debug.Print "------------------------------"

.Close
End With
Set db = Nothing

End Sub

Private Sub basTestProperty()

Dim db As Database
Dim fldAny As Field
Dim I As Integer

Set db = CurrentDb
With [db]
Set fldAny = .TableDefs("tblAmsterdam").Fields("StraatnaamOfficieel")
For I = 0 To [fldAny].Properties.Count - 1
Debug.Print [fldAny].Properties(I).Name
Next I
Set fldAny = Nothing
Debug.Print "------------------------------"
.Close
End With
Set db = Nothing

End Sub

Private Function TableExists(strTablename As String) As Boolean

If (IsNull(DLookup("Name", "MSysObjects", "([Type] IN (1, 4, 6)) AND
([Name] = """ & strTablename & """)")) = True) Then
TableExists = False
Else
TableExists = True
End If

End Function



Ronald.