Programmatically change Unicode Compression setting

[Rachael]

Hi All and thanks for taking a look at my problem,

Im trying to programmatically change the Unicode compression setting in my tables but can't seem to get it to work.

Here's what I'm doing:

Dim I As Integer, J As Integer
Dim db As DAO.Database, td As TableDef, fld As Field

Set db = CurrentDb()

On Error Resume Next
For I = 0 To db.TableDefs.Count - 1
Set td = db(I)

For J = 0 To td.Fields.Count - 1
Set fld = td(J)
If (fld.Type = DB_TEXT Or fld.Type = DB_MEMO) Then

fld.Properties("UniCodeCompression") = True

End If
Next J
Next I
MsgBox "Done"
db.Close


The code runs fine but the Unicode compression setting won't change, I did think it worked once and I think the first table changed but am not sure now as none of the others will change. I have used msgbox to check that it's looping correctly and everything seems fine except that the property won't change. I've about 150 tables.

Thanks in advance, Rachael

 

[Galaxiom]

You cannot possibly tell how the code runs until you get rid of the On Error Resume Next because it will ignore all errors. Change that and it might tell you the problem.

 

[Rachael]

Ah thanks for that! I'd glazed over that- you're absolutely correct - I atleast now get an error 'property not found'

Do you or anyone else know how to properly refer to the Unicode property - my internet research indicated that what I've done was correct but it doesn't appear to be.

thank you

 

[Galaxiom]

I expect you are hitting system tables with your code. Skip tables whose names start with "MSys" or "~"

BTW you can simplify your loop code with For Each loops.

 

[Rachael]

Hi and thanks for your replies,

Here's where I'm up to, can get it to run but the Unicode value will not changed


Dim db As DAO.Database, td As TableDef, fld As Field, prp As Property

Set db = CurrentDb()

For Each td In db.TableDefs
If Not (td.Name Like "Msys*") Then

For Each fld In td.Fields
If (fld.Type = DB_TEXT Or fld.Type = DB_MEMO) Then

For Each prp In fld.Properties

If prp.Name = "UniCodeCompression" Then
fld.Properties("UniCodeCompression") = True
Debug.Print td.Name & fld.Name
End If
Next
End If
Next
End If

Next
MsgBox "Done"
db.Close

thanks Rachael

 

[jdraw]

see this by allen browne