Management of controls' metadata (tooltips, captions etc.) and international support in your MS Access applications.

A complex access application can have hundreds of controls and the
problem you face is how to smartly manage controls metadata such as
tool tips, captions etc? A flexible solution would allow international
support and update of metadata also by the user himself.

As the standard each control has a number of textual properties and a
developer can assign a value to the tool tip text. Handmade management
of these properties will soon become complex task because of the
growing number of controls. it also makes further customization such
as international support.

An effective solution to this problem is to separate metadata from the
controls and manage it in a separate table(s). Here is a simplified
solution.
On form load event a function GetTooltip is called. The argument of
this function is the control name.

Example:

Private Sub Form_Load()
For Each CTL In Me.Controls
On Error Resume Next
CTL.ControlTipText = GetTooltip(CTL.Name)
Next
End Sub

Public Function GetTooltip(CTRL As String)
GetTooltip = Nz(DLookup("[CTRL_TOOLTIP_NL]", "CONTROLS",
"UCASE([CTRL_NAME])='" & UCase(CTRL) & "'"), "")
End Function

As you can see the function GetTooltip runs a dlookup function that
finds necessary metadata (in this case dutch translation of the
tooltip text) in the metadata table CONTROLS.

The metadata table look like this:

CTRL_NAME CTRL_TOOLTIP_NL CTRL_TOOLTIP_EN

cmdRefresh Gegevens Vernieuwen Refresh data
cmdRepAgents Rooster Rapport Roster Report
imgAgentsForm Medewerker Gegevens Employee Data
cmdAgentRooster Medewerker Bruto Rooster Employee Bruto Schedule
cmdAgentSkill Medewerker Skills Employee Skills

A nice thing about Dlookup is that you can compute the searched
column. For example if the application has a variable ULan that
carries user language settings, the call of dlookup can be adjusted to
the current user language settings like that:

GetTooltip = Nz(DLookup("[CTRL_TOOLTIP_" & ULAN & "]", "CONTROLS",
"UCASE([CTRL_NAME])='" & UCase(CTRL) & "'"), "")

In this case ULAN shoul be assigned a standard language abbreviation
(NL - for dutch, EN for English etc).

In a similar manner all labels, captions and other textual metadata
can be pulled out of the controls metadata table.

This works great for me, why not trying this solution in your applications?

4SUC6

Quantity of Database Solutions

As illustrated in the database pyramid earlier in this paper, there
are more small databases than large ones. The following table shows an
estimate of the relative number of database solutions by platform in a
large organization.
Platform Quantity
Excel 50,000
Access individual 5,000
Access simple multiple user 1,000
Access workgroup or department 500
Visual Basic or Jet 100
Visual Basic, Visual Studio .NET, Java, or SQL Server 50
Oracle or IBM DB2 25
SAP, Tandem, and others 10

http://www.4suc6.com
Share your goals!

Access solution Costs

I found this cost table quite relevant for ROI.

(from http://download.microsoft.com/download/a/4/7/a47b7b0e-976d-4f49-b15d-f02ade638ebe/SQLAccessWhatsRight.doc#_Toc90802236)

The solutions and the costs vary significantly based on the platform
selected. The following table shows approximate numbers.
Platform Average Cost
Excel $500
Access individual $3,000
Access simple multiple user $10,000
Access workgroup or department $50,000
Visual Basic or Jet $200,000
Visual Basic, Visual Studio .NET, Java, or SQL Server $500,000
Oracle or IBM DB2 $2,000,000
SAP, Tandem, and others $10,000,000 or more

Although there are $1,000,000 Access applications and $20,000 .NET
applications, the numbers in the preceding table show order of
magnitude for a large organization, and what they generally spend for
solutions on those platforms.

Met vriendelijke groet,
Maxim

http://www.4suc6.com
Share your goals!

MS Access vba transpose columns into rows or rows into columns

in one of tables similar statistics were storen in columns per empoloyee per week.

EMPL WEEK T_N1 T_N2 T_N3 etc
aaa 200712 5 0 0

I need a tnrasnformed form like this

EMPL WEEK STAT N
aaa 200712 T_N1 5

By using this transformed form it becomes possible to extend facts with additional information per statistics by making a join on STAT field.

So here is the VBA code to make the tranformation.


Attribute VB_Name = "mdlTranspose"
Option Compare Database

Function TransColRow(strSource As String, strTarget As String, nF As Integer)
' transpose columns into rows
' first nF rows are not ransposed
' names of the transposed column are saved into the FIELD in the target table
' Numerical values of the transposed columns saved into target field N
' if the transposed fields are not numeric change dbInteger into the right datatype

Dim db As DAO.Database
Dim tdfNewDef As DAO.TableDef
Dim fldNewField As DAO.Field
Dim rstSource As DAO.Recordset, rstTarget As DAO.Recordset
Dim i As Integer, j As Integer, k As Integer

On Error GoTo Transposer_Err

Set db = CurrentDb()
Set rstSource = db.OpenRecordset(strSource)
rstSource.MoveLast

' Create a new table to hold the transposed data.
' Create non transposable fields from the original table.
Set tdfNewDef = db.CreateTableDef(strTarget)

For i = 0 To nF - 1
Set fldNewField = tdfNewDef.CreateField(rstSource.Fields(i).Name, rstSource.Fields(i).Type)
tdfNewDef.Fields.Append fldNewField
Next i
Set fldNewField = tdfNewDef.CreateField("FIELD", dbText)
tdfNewDef.Fields.Append fldNewField

Set fldNewField = tdfNewDef.CreateField("N", dbInteger)
tdfNewDef.Fields.Append fldNewField

db.TableDefs.Append tdfNewDef
Set rstTarget = db.OpenRecordset(strTarget)

rstSource.MoveFirst
Do While Not rstSource.EOF
For j = nF To rstSource.Fields.Count - 1
With rstTarget
If rstSource.Fields(j) > 0 Then
.AddNew
For k = 0 To nF - 1
.Fields(k) = rstSource.Fields(k)
Next k
.Fields(nF) = rstSource.Fields(j).Name
.Fields(nF + 1) = rstSource.Fields(j)
.Update
End If
End With
Next j

rstSource.MoveNext
Loop
rstSource.Close
rstTarget.Close
db.Close

Exit Function

Transposer_Err:

Select Case Err
Case 3010
MsgBox "The table " & strTarget & " already exists."
Case 3078
MsgBox "The table " & strSource & " doesn't exist."
Case Else
MsgBox CStr(Err) & " " & Err.Description
End Select

Exit Function

End Function







' reverse transofmation (rows into columns )


Function Transposer(strSource As String, strTarget As String)

Dim db As DAO.Database
Dim tdfNewDef As DAO.TableDef
Dim fldNewField As DAO.Field
Dim rstSource As DAO.Recordset, rstTarget As DAO.Recordset
Dim i As Integer, j As Integer

On Error GoTo Transposer_Err

Set db = CurrentDb()
Set rstSource = db.OpenRecordset(strSource)
rstSource.MoveLast

' Create a new table to hold the transposed data.
' Create a field for each record in the original table.
Set tdfNewDef = db.CreateTableDef(strTarget)
For i = 0 To rstSource.RecordCount
Set fldNewField = tdfNewDef.CreateField(CStr(i + 1), dbText)
tdfNewDef.Fields.Append fldNewField
Next i
db.TableDefs.Append tdfNewDef

' Open the new table and fill the first field with
' field names from the original table.
Set rstTarget = db.OpenRecordset(strTarget)
For i = 3 To rstSource.Fields.Count - 1
With rstTarget
.AddNew
.Fields(0) = rstSource.Fields(i).Name
.Update
End With
Next i

rstSource.MoveFirst
rstTarget.MoveFirst
' Fill each column of the new table
' with a record from the original table.
For j = 0 To rstSource.Fields.Count - 1
' Begin with the second field, because the first field
' already contains the field names.
For i = 1 To rstTarget.Fields.Count - 1
With rstTarget
.Edit
.Fields(i) = rstSource.Fields(j)
rstSource.MoveNext
.Update
End With

Next i
rstSource.MoveFirst
rstTarget.MoveNext
Next j

db.Close

Exit Function

Map Sequrity settings for access mdb

keywords: access mdb mde security network windows map folder multi user group

When your Microsoft Access aplication is ready it is time to place on the network map where the end users can access it. Let's call it Access map. This is the moment to think about security. Personaly I prefer to use map security instead of built-in Access security for one simple reason - Network security is already there.
This includes define user names and groups of users. So if you need to give access to a whole department then it is optimal to find a network group name with the writing rights and add this group to your security settings. Let's take a look at how this can be done.

First you need to copy you application in the access map with the following minimal sequrity settings:
- all users have can see the content of the map. This means that above maps allow the target user group to browse through. If Map B is inside of map A and map A does not allow to see the content, no matter what security has B, A will block the users.
- you have an administratory rights (or have a good relation with administrators :) . We are going to assign special security settings for the acces map. And we need admistrator rights for that.

The access map is \DATAMART\ACCESS MAP and the access application is called FORMS.mdb.

First I add myself via the map properties . (right click on the map and select properties Sorry , I have it in dutch here but this is the way it is. ).
Add user

The default security settings for the new user does not allow to edit or administrate the map. If if try to open the access application with this settings than surprisingly or not we also cannot add any information because we have read only access.

We need to add right access (schrijven = true)

Now the user can work with acces. There is still one problem. The user (myself) can also delete files from the access map.
We need to limit this security because we want of course no information to be accidentially deleted. This goes via security advanced settings.

select deny delete access

And ok,

Now the user cannot delete any files from the map, which is good.

The last thing is to create a shortcut to your access application and email it or place it in a easy to find map.

That is basically is.