Not Geeky!

Once every now and then I’m called by a client to tart up an old Ms Access Database up. Quite often it needs to be normalised and almost even so often it becomes a lot smaller after a repair and compact.

One other thing that bugged me was that the Unicode Compression was often not set for the field and therefore the data was taking up a lot more space than strictly neccessary. If you try to alter this by hand you get an error message saying it can’t change the datatype due to lack of memory or diskspace.

If you try it using VBA however there is no problem whatsoever….

————————————————————————-
Option Compare Database

Public Sub SetTableField(ByVal strTableName As String, _
ByVal strFieldName As String, _
ByVal strProperty As String, _
ByVal vntState As Variant, _
Optional ByVal vntDatabasePathAndName As Variant)

Dim tdfTableDef As DAO.TableDef
Dim dbsThisDatabase As DAO.Database

On Error GoTo ErrorHandler

‘If the Database Path/Name was NOT passed…
If IsMissing(vntDatabasePathAndName) Then
‘Get a pointer to the local Database.
Set dbsThisDatabase = CurrentDb()
Else
‘Get a pointer to the linked Database.
Set dbsThisDatabase = DBEngine.Workspaces(0).OpenDatabase(vntDatabasePathAndName)
End If

‘Set a pointer to that Table’s definitions in that Database.
Set tdfTableDef = dbsThisDatabase.TableDefs(strTableName)

‘Operate on the Property of that Table in that Database.
tdfTableDef.Fields(strFieldName).Properties(strProperty) = vntState

ExitProcedure:
‘Set error handling off, we can’t afford another error here.
On Error Resume Next
Set tdfTableDef = Nothing
Set dbsThisDatabase = Nothing
Exit Sub

ErrorHandler:
MsgBox “Error in Sub SetTableField” & vbNewLine & “Module mdlGlobal” & vbNewLine & vbNewLine & _
“Error number : ” & Err.Number & vbNewLine & _
“Error description : ” & Err.Description

Resume ExitProcedure

End Sub
————————————-

Use :

SetTableField “Table”, “FieldName”, “UnicodeCompression”, True

and presto!

Moreover you can use this code also for removing/applying some restrictions on the fields:

eg

SetTableField “Table”, “FieldName”, “AllowZeroLength”, True

and

SetTableField “Table”, “FieldName”, “Required”, True

(Replace “Table” with your own table and “FieldName” with your own field)

So if you want to apply this to all the textfields in a table, you can use this:

Sub SetUnicodeTrueForAll(StrTable As String)

Dim Rs As Recordset

On Error GoTo Err

Set Rs = CurrentDb.OpenRecordset(StrTable)

With Rs
For IntI = 0 To .Fields.Count - 1
If .Fields(IntI).Type = dbText Then
SetTableField StrTable, .Fields(IntI).Name, “UnicodeCompression”, True
End If
Next
End With

Sub_Exit:
Rs.Close
Exit Sub

Err:
MsgBox Error$
Resume Next

End Sub

From time to time it happens: Ms Access goes into debug mode without an error. Saving, recompiling, compact/repair, nothing seems to cure the problem. The problem keeps on coming back at the same piece of code.

What seems to happen is that you can’t erase a breakpoint that you’ve created before. Somehow you need to decompile the code.

A very easy workaround is cut the code subroutine/function where it keeps on breaking, press save and paste the code again. Save and compile the code again. Ms Access will no longer go into debug mode!

Cheers,

Edwin