Set Unicode Compression in code

[dixie]

I am writing some vba to create a new field if it is not already there.
Code follows.

The code I have so far, creates the new field in the table and sets the
AllowZeroLength property = true.

'Start code
Private Sub AddNewField_Click()

On Error GoTo ErrAddNewField_Click
Dim db As DAO.Database
Dim fld As DAO.Field
Dim tdf As DAO.TableDef

Set db = Application.CurrentDb
Set tdf = db.TableDefs("tblTestCode")
Set fld = tdf.CreateField("MyNewField1", dbText, 150)

With fld
.AllowZeroLength = True
End With

With tdf.Fields
.Append fld
.Refresh
End With

Set fld = Nothing
Set tdf = Nothing
Set db = Nothing

Exit Sub

ErrAddNewField_Click:
MsgBox Err.Description
Resume Exit_ErrAddNewField_Click

End Sub
'End code

I have two problems.

1. How do I create this new field ONLY if it does not already exist.
2. This code defaults to Unicode Compression = No for a text field and I
would like it to be Yes. How do I do this?

dixie

[pietlinden]

> I have two problems.

1. How do I create this new field ONLY if it does not already exist.
2. This code defaults to Unicode Compression = No for a text field and I would like it to be Yes. How do I do this?

dixie

Just two? Consider yourself lucky!

1. you check to see if the field exists... I got the TableExists code
from Accessweb and then wrote the FieldExists function by tweaking
that... The debug statements are just so you can see where the
"search" fails - whether the table or the field doesn't exist.

'******************** Code Start ************************
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
'
Function fExistTable(strTableName As String) As Boolean
Dim db As Database
Dim i As Integer
Set db = DBEngine.Workspaces(0).Databases(0)
fExistTable = False
db.TableDefs.Refresh
For i = 0 To db.TableDefs.Count - 1
If strTableName = db.TableDefs(i).Name Then
'Table Exists
fExistTable = True
Exit For
End If
Next i
Debug.Print "Table exists: " & fExistTable
Set db = Nothing
End Function
'******************** Code End ************************

Function fExistField(strTableName As String, strFieldName As String) As
Boolean
On Error GoTo ErrHandler

Dim db As Database
Dim tdf As TableDef
Dim i As Integer

If fExistTable(strTableName) = True Then
Set db = DBEngine.Workspaces(0).Databases(0)
Set tdf = db.TableDefs(strTableName)

For i = 0 To tdf.Fields.Count
If strFieldName = tdf.Fields(i).Name Then
'Field Exists
fExistField = True
Exit For
End If
Next i

Set tdf = Nothing
Set db = Nothing
Else
fExistField = False
End If
Debug.Print "Field exists: " & fExistField

Exit Function

ErrHandler:
If Err.Number = 3265 Then
Debug.Print "Field exists: " & fExistField
fExistField = False
Err.Clear
End If

End Function

[dixie]

Its a lot of code for such a teensy weensy thing :>)

Actually, I already use the If Exists table form of the code in other areas.

If I just put On Error Resume Next at the top of the sub, would that not
work just as well? Because if the field already exists, it will create an
error? It appears to work, but I know it is not good practice and perhaps
there is a down side I cannot see.

Any help with number 2 - setting the Unicode Compression = Yes. I don't
really think it is that important, but I am told all text fields should be
set that way.

dixie

<pi********@hotmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

I have two problems.

1. How do I create this new field ONLY if it does not already exist.
2. This code defaults to Unicode Compression = No for a text field

and I

would like it to be Yes. How do I do this?

dixie

Just two? Consider yourself lucky!

1. you check to see if the field exists... I got the TableExists code
from Accessweb and then wrote the FieldExists function by tweaking
that... The debug statements are just so you can see where the
"search" fails - whether the table or the field doesn't exist.

'******************** Code Start ************************
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
'
Function fExistTable(strTableName As String) As Boolean
Dim db As Database
Dim i As Integer
Set db = DBEngine.Workspaces(0).Databases(0)
fExistTable = False
db.TableDefs.Refresh
For i = 0 To db.TableDefs.Count - 1
If strTableName = db.TableDefs(i).Name Then
'Table Exists
fExistTable = True
Exit For
End If
Next i
Debug.Print "Table exists: " & fExistTable
Set db = Nothing
End Function
'******************** Code End ************************

Function fExistField(strTableName As String, strFieldName As String) As
Boolean
On Error GoTo ErrHandler

Dim db As Database
Dim tdf As TableDef
Dim i As Integer

If fExistTable(strTableName) = True Then
Set db = DBEngine.Workspaces(0).Databases(0)
Set tdf = db.TableDefs(strTableName)

For i = 0 To tdf.Fields.Count
If strFieldName = tdf.Fields(i).Name Then
'Field Exists
fExistField = True
Exit For
End If
Next i

Set tdf = Nothing
Set db = Nothing
Else
fExistField = False
End If
Debug.Print "Field exists: " & fExistField

Exit Function

ErrHandler:
If Err.Number = 3265 Then
Debug.Print "Field exists: " & fExistField
fExistField = False
Err.Clear
End If

End Function