Category Archives: MS Access

A begging letter

The web is a massive thing. You’ll find all sorts on here, good and bad, in relative measure of course. There are those what would consider blog on Access bad.

I’ve heard about inbound marketing. What it is exactly I am not sure. It sure does fit for a introverted person like myself, I can even speak with my parents for all that long!

But does it not all sound like inbound marketing is a new way of writing a work begging letter. It is a way to attract people who are looking for someone to find you. I thought if I blog on Access topics people will find me. So far it hasn’t worked out that way. Maybe they are coming here – though from stats they aren’t – and taking the knowledge.

Any inbound marketing tips and sites are welcme. I’ve used up my book budget this year so won’t be able to buy any of those.

New hotfix solves Access 2010 performance issue

Thanks to Tony Toews and his blog for bringing this to our attention.

Access 2010 hotfix package (Ace-x-none.msp): August 30, 2011  which can download from here by request.

This solves the Access 2010 nd Windows 7 performance issue.

As with all hotfixes, only apply if you really has having an issue described and you can’t live with it until the next Service Pack is released.

Using roles and security with Oracle

Joining Access and SQL Server when it comes to security it really simple. Your user’s AD accounts are used by the SQL instance to set the appropriate permissions and job done. How does one achieve the same group (or role) based security when using Oracle.

Oracle doesn’t fit in with AD very well. There may be strategies that could allow Oracle to recognise these, or a synchronisation mechanism. But this isn’t a OID blog so I won’t get into that. I’ll assume that you have Oracle as a separate entity on your network.

Oracle has Roles which can be assigned to schemas and used in permissions. So if you have a schema which is used by hundreds of users you would assign each user into a role in their user profile. When that user signs in they only see what that role has been given permissions to. Users can have access to multiple roles but in general it will be only one.

As this is not an Oracle blog, I won’t discuss how to set the roles up and assign them to the objects. I’ll leave that as a lesson for the reader. But lets assume we have two roles, aptly named ROLE1 and ROLE2. No idea which has more privileges but it hardly matters.

The Access ODBC connection will be using an Oracle user to login to the database and how can you tie the Windows names and assign the proper roles when logging in. Oracle has the USERENV context information and the SESSION_USER attribute. This attribute identifies the user name on the Windows machine which has connected.

Then you’ll need a table to map those user names to the role they are to be assigned along with a little bit of code.



+------------+---------+
|USERID | ROLE_ID |
+------------+---------+
|sam | 1 |
|todd | 2 |
|harvey |-1 |
|gill | 1 |
+------------+---------+

The -1 would indicate a user that previously had permissions but those have been revoked.

To do the mapping you need this bit of PL/SQL code



create or replace function authRole() return number(1)
as
s_user varchar2(20);
i_role_id number(1);
begin
select upper(sys_context('USERENV', 'SESSION_USER')) into s_user from dual;
select role_id into i_role_id from Users where userid=s_user and role_id>0;

case i_role_id
when 1 then execute immediate 'set role ROLE1';
when 2 then execute immediate 'set role ROLE2';
end case;
return i_role_id;
exception
when NO_DATA_FOUND then raise_application_error(-20011,'User unauthorized');
when others then raise_application_error(-20011,'Unknown Exception in authRole Function');
end;

you could put this into a login trigger on the schema but you’ll want to remove the raise_application_error as they don’t come through when ODBC is connecting. (in my experience, anyway)

That’s the Oracle side, whew, now for the Access part.

I create a function which assigns the role – by calling the code above and then checking if there is access.



Function OraAssignRole()
On Error GoTo ERR_ORAASSIGNROLE
dim rs as DAO.Recordset
dim oQry as querydef

'Initiate the login by running a passthrough query to connect to oracle
set oQry = CurrentDb.QueryDef!qryPassthrough
oQry.sql = "{call SetTheRole}"
oQry.ReturnsRecords = True
oQry.Execute dbFailOnError
oQry.Close

'Made it here so the user is ok, get the role id for use in the app
glbl_role_id = rs(0)

Exit Function
ERR_ORAASSIGNROLE:
MsgBox "Error on login, You may not have rights. Application will close now."
Application.Quit
End Function

And that’s the end of it. Place a call to this in the autoexec macro and your application will login to Oracle, set the role, and if the user is not found – close down the application.

I hope you found this post useful.

A simple way to improve performance

When you split a database and place it on a lan drive – the most common configuration – you can find performance begins to suffer. This is for two main reasons, one because a LAN is much slower than an disk, and the file opening and closing that happens. I can propose a solutions to the second problem.

The first problem is mainly a hardware issue. Put in a gigibit LAN and NICs and make sure the path to the database file on the server is as short as possible.

The second issue is, I believe, caused by the file open, locking and close process that occurs each time the access to the database file is required. Simply because the front-end is open does not mean that the file is also open, it most likely is not.

The technique I use is to hold a constant connection to the file open at all times. The only way to do this is to have a hidden form with some code on it.



Private Sub Form_Open(Cancel As Integer)
Dim ors As Recordset
Set ors = CurrentDb.OpenRecordset("select * from dummy")
Form.Visible = False
End Sub

Open this form when the database opens, say in the autoexec macro.

I find this technique works quite well when performce problems affect your application. I have even used on applications which connect to SQL Server and Oracle.

Flattening a hierarchy in an editor

In one of projects I had to import in Excel spreadsheets into an Access database. This database also had to maintain the structure of the files, as the on screen representation was to be identical. The file was sectioned and this structure was stored in a hierarchy in the database. I had to supply a form to the users so they could edit the file (and screen) layout.

One can show this in flattened screen but how do you edit this nicely? I hope to show in this post a nice technique to edit tables which refer to hierarchical information using a single form.

The Tables
First lets build a simple hierarchy. I’ll keep it simple and just stick to a two level hierarchy.
And now the table to store the referencing information which has fields to reference the top and middle values
The Form
I will build the from on the tblSections which is essentially the configuration table. I use the form wizard to create the intial form in the tabular style.

Now setup the rowsource for each combo appropriately:



select id,top from tblTop

and

select id,middle from tblMiddle

Now if you run this form as is you will notice that the sectionmid combo is not filtering based on the sectiontop combo. So lets sort that.

We will need to add the following event code to the form.



Private Sub SectionTop_Change()
Dim sSQL As String
sSQL = "select id,middle from tblmiddle where TOP_ID=" & Me.SectionTop
Me.Combo7.RowSource = sSQL
Me.SectionMid = Null
End Sub

Private Sub Combo7_GotFocus()
Dim sSQL As String
sSQL = "select id,middle from tblmiddle where TOP_ID=" & Me.SectionTop
Me.Combo7.RowSource = sSQL
End Sub

Private Sub Form_Current()
Dim sSQL As String
sSQL = "select id,middle from tblmiddle where TOP_ID=" & Me.SectionTop
Me.Combo7.RowSource = sSQL
End Sub

(you would probably do a better job by placing this into a single sub and invoking it from the events)

And now run the form again. What you will notice is that when you run the form all the sectionmid combos will change and not just the single one you are using. You can notice this when the combo blanks out when you change rows.
The trick to this is the place a textbox control on top of the combo box. And have this combo use a DLookup to display the proper value. Place the text box on top of the text area and ensure you move it to the top. Change the border to transparent.

Set the DLookup formula to



=IIf(IsNull([SectionMid]),"",DLookUp("[middle]","[tblMiddle]","ID=" & [SectionMid]))

The IIF ensures that and #error is not displayed when the comboboxes are null.

Conclusion
You can of course extend this by adding in more combos and textboxes. Just modify the code appropriately and add in events to enure changes are cascaded.

That’s all there is to it. I hopes this provides you some inspiration.