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

Not even that long ago, in the summer of 2007, the BBC started distributing their beta version of IPlayer . I subscribed, and was ‘invited’ to download their iPlayer. These days however it is a lot easier, to just browse to the BBC website and watch the program from there. No need to download, unless you prefer higher quality or want to watch it from a portable player.

Not only the BBC but also ITV has an archive of broadcasted programs. On top of that you can watch live programs. My favorite is Channel 4 though. You can catch-up on one of their programs in their 30-day archive. On top of that they have another huge archive, which requires to download their player. On top of a lot of programs (whole seasons from popular series!!) from the past it also allows you to watch recent movies for a small fee.

However, if you just want to watch live television TV Catchup is great. The name is a bit misleading, because all they offer is live television. It’s 18 different channels though!

Now, if you’re abroad, or you don’t live in the UK at all and you want to watch a program from one of these websites you’ll find that you can’t, because of your foreign IP address. However if you make use of a UK based Proxy Server this is very well possible. There are lot’s of free ones, quite often lousy quality, and paid ones such as VPNGates. For a small monthly fee, this allows you to watch your fav program live. Rumour has it that this works perfectly except for when a new Top Gear Episode is aired.

Enjoy!

Edwin

Priceless…

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

For A. Cause she kept on asking for this song…
(Click on the little icon to the left of HQ for full screen)