Remind me to never use access again....

I came back from Maternity Leave, all enthusiasm. It lasted of course only a few weeks, and then I was asked to help with a "Training Record System"; as an in-house developer, if they ask for something to record the training of the employees in the company, I said yes. It's called a "can do" attitude and people really appreciate it. And because I felt a little bit rusty, I decided to use Access as front end and SQL as the back end. I can do that with my eyes closed, right?
And then I was easily reminded of the joys of working with Access.
A basic form with two sub forms, and a combo box on top which is linked to them. The first sub form contains the details of the course, the second sub form contains the details of the participants. So when I first added details to the course sub form (the top one) (On new record) I would get the very descriptive

You can't assign a value to this object

with all sort of non relevat reasons.

And then I pressed "ok" and continued to edit the report.

It slightly help to add the following code to the sub form

And that resolved it temporarily.

So I managed to put all the course details without getting any annoying error messages, and then when I moved from the "course" part details to the "participants" part details, just to get the following error:

ODBC -- call failed

[Microsoft][ODBC SQL Server Driver]Optional feature not implemented (#0)

So I looked this problem up and found myself on #2 in Google! Well, that only shows how rusty I am! I only needed to refresh the underlying table this time in order to sort out the problem (I knew it's not the ODBC driver as yesterday it worked...).



One more thing (basic but good to know):
If you get the
"The expression you entered has a function name that Microsoft Office Access can't find"
error. 
Sometime it is just due to the fact that a Macro must refer a Public function (not a Sub!) in a module. And the function's name should be different than the Module Name. And of course, make sure you've spelled the word correctly. For a Function that could be written as a sub just do this

Public Function Foo () AS Integer
'Do Something
      Foo = 1
End Function

I Also downloaded this
http://www.microsoft.com/en-us/download/confirmation.aspx?id=6627
in order to set up the Menu item. Ho, the good old MS Access 2003 days! You can find a code example of how to add a side menu to a report here. As for manipulating the Ribbon for better functionality; sorry, it requires messing with the Registry, an absolute no-no in my view. I really don't think it is even slightly necessary.



Another Example:

Working on an older database, running a trusted code and Receiving
Error: Record is deleted, (3167) encountered.
Apparently I needed to do Compact and Repair

Another Example:

I've placed a copy of an Access MDB file on the Network so everyone can share the joy.
So I run it and it doesn't let you run the product from the Network, you need to add it to the Trust Center.
So I go File -> Options -> Trust Center -> Trust Center Settings ->Trusted Locations
And I add a new location.

It tells me that I'm not allowed ("The remote or network path you have entered is not allowed by your current security settings.")
And I need to check the "check "Allow trusted locations on my network (not recommended)" 

So:
If you want to run Access from the Network is not recommended.
Which means that Access isn't a corporate product! Ask MS if you don't believe me!

The issues is this, but really, really, please:
How come that if I'm working on a Trusted product, Like SQL or C#, I rarely rarely google my problems, and I hardly ever get "weird" error messages, while 10 minutes into using MS-Access I Google like crazy?

Getting empty values with Sum, Left Join and Inner Join

I'm now working on a simple Training Record database. It's a bit of a downgrade from BI. It's a database with basically 3 tables:
Learners (=the employees in the company)
Courses
Participation (which learner took which course when).

My architecture was based on SQL server as the back end and MS-access as the front end. It makes the database robust and the front end design quick (though you have to pay a price for that, and about that in another post). Another tip is if you don't get the result set that you want running a query in MS-Access it's always easier to investigate the situation on the SQL itself.

The Participation requires the following fields: Learner, Course AND CPD, which is the number of points you get from this course. It could be less than the number of points that the Course acquires.

Sounds simple enough, doesn't it? Well, you'd be surprised how many problems can one encounter with a simple database like that.

Business asked for a simple request: when they see all the Learners, they want to see those without any courses as well. The problem is outlined below:



If you run the following query:

SELECT LearnerList.FullName, Sum(dbo_Participation.CPD) AS SumOfCPD, LearnerList.id
FROM LearnerList LEFT JOIN dbo_Participation ON LearnerList.[id] = dbo_Participation.[Student]
WHERE (( LearnerList.id)=5 Or ( LearnerList.id)=6 Or ( LearnerList.id)=7 Or ( LearnerList.id)=34)
GROUP BY LearnerList.FullName, dbo_Participation.Student, LearnerList.id;


That gives me all the required participants, including those who has NULL as sumOfCPD

FullName            SumOfCPD  id
Amanda Smith         5.75        5
Amanda Jones         24.00       6
Catherine Smith NULL     7
John Smith          NULL      34



BUT, if I try to manipulate it to a certain time range, those didn't take any courses (or in SQL: they're SumOfCPD is NULL) disappears:

SELECT LearnerList.FullName, Sum(dbo_Participation.CPD) AS SumOfCPD, LearnerList.id
FROM LearnerList LEFT JOIN (dbo.Participation
INNER JOIN dbo.course ON dbo.Participation.course = dbo.course.id)
ON  LearnerList.[id] = dbo.Participation.[Student]
WHERE
(dbo.course.Date)<='20131115'
And (dbo.course.Date)>='20130701'
AND
(( LearnerList.id)=5 Or ( LearnerList.id)=6 Or ( LearnerList.id)=7 Or ( LearnerList.id)=34)
GROUP BY LearnerList.FullName, dbo_Participation.Student, LearnerList.id;


FullName            SumOfCPD  id
Amanda Smith         5.75         5
Amanda Jones         24.00        6


If you move the condition of the "Where" clause back to the "Join" part, that sort out the problem:

SELECT LearnerList.FullName, Sum(dbo_Participation.CPD) AS SumOfCPD
FROM LearnerList LEFT JOIN dbo.Participation
INNER JOIN dbo.course ON dbo.Participation.course = dbo.course.id
AND  (dbo.course.Date)<='20131115' And (dbo.course.Date)>='20130701')
ON dbo.student.[id] = dbo.Participation.[Student]
WHERE
(( LearnerList.id)=5 Or ( LearnerList.id)=6 Or ( LearnerList.id)=7 Or ( LearnerList.id)=34)
GROUP BY LearnerList.FullName, dbo_Participation.Student;


Results:
FullName            SumOfCPD  id
Amanda Smith         5.75         5
Amanda Jones         24.00       6
Catherine Smith NULL     7
John Smith          NULL     34

Running the above script in MS-Access would give me the "Error in the FROM clause". The above script run directly from the SQL.
Another option it to use the "NOT EXIST", which exist in MS-Access.
In this example, as I created the SQL as a string in the VBA engine, I didn't want to have the Date as a parameter of the JOIN, as I didn't know how Access would handle it (I wouldn't take a wild guess saying it wouldn't, would I?)


SELECT T1.FullName, T1.id
FROM LearnerList  T1
WHERE  NOT EXISTS
(SELECT T2.FullName,
Sum(dbo.Participation.CPD) AS SumOfCPD
FROM dbo.LearnerList  T2 LEFT JOIN (dbo.Participation
INNER JOIN dbo.course ON dbo.Participation.course = dbo.course.id)
ON T2.[id] = dbo.Participation.[Student]
WHERE
(dbo.course.Date)<='20131115'
And (dbo.course.Date)>='20130701'
AND ((T2.id)=5 Or (T2.id)=6 Or (T2.id)=7 Or (T2.id)=34)
AND T1.id = T2.id

GROUP BY T2.LearnerList , dbo.Participation.Student)
AND
(T1.id = 5 OR T1.id = 6 OR t1.id = 7 OR t1.id = 34)


It's important to note that correctly, if run in SQL Server, you'd get the warning of:
Warning: Null value is eliminated by an aggregate or other SET operation.

For all of the above queries except for the "not exist" one.

In MS-Access, on the other hand, even creating a sub-query wouldn't help. You'll get and "ODBC - Call Error" just from the LEFT JOIN. In INNER JOIN you wouldn't get any error.

And last but not least: as this had to be read as a report, and when you do the following

Dim RS As New ADODB.Recordset
'Execute the code to set the RS
Set Reports("rpt_participation_summary").Recordset = RS

You get "You can do this type of action only in an ADP" I just used "SELECT...INTO "a table which is "temporary" on the SQL server. Ah, the joys of using Access! remind me NEVER, but NEVER to do it again!

Optional Feature not implemented -- ODBC call error

The problem:
Features: an access application with one form linked correctly to a table.
The form loads fine, the data in the table is viewed correctly.
The person in need to make a change (in this case: the business analyst) can't make a change. He enters the new data to the field, and once he leaves he gets the following error message:
ODBC -- call failed
[Microsoft][ODBC SQL Server Driver]Optional feature not implemented (#0)
Possible solutions:
The results I've found googling the problem usually relate to the case when there's some code. The problem is, that there's absolutely no code, so I can't tell "which line is it". It cannot be "parameter passing" as it was proved to some people.
Does he have the right permissions on the database?
Apparently he does, as
I've given the business analyst the permissions required on the database (namely: db_datareader, db_datawriter),and the following works as it should:




Also, when trying to use the following code straight from the SQL server, it runs perfectly fine:

EXECUTE AS LOGIN = 'BusinessAnalyst';
SELECT SUSER_NAME(), USER_NAME();
--gives 'BusinessAnalyst', 'BusinessAnalyst'
EXECUTE AS LOGIN = 'BusinessAnalyst';
SELECT *
from dbo.dimTable
--produces the results, as expected from someone with db_ddlreader permissions
EXECUTE AS LOGIN = 'BusinessAnalyst';
update DBO.dimTable
set column= 'value'
where ID = 1
--produces the results, as expected from someone with db_ddlwriter permissions



Needless to say, when I try to make the change from my instance of Ms-Access on my computer it run perfectly fine. And no, giving him higher permissions, like "db_ddladmin" didn't really help.
So obviously the problem is in the ODBC driver. But where?
As it works on my machine and doesn't work on his, we've decided to log in from my machine.
Then I had to re configure the ODBC connection, and found out where the error was:
Instead of choosing "SQL Server" as a client, you need to choose "SQL Server Native Client 10.0". 

True, the BA didn't have the driver installed on his machine, but he's a smart guy, so he  googled and got here:

http://www.microsoft.com/en-us/download/details.aspx?id=16978

choose "native client" and sent me the following beautiful email:

DONE,

WORKING J

Excellent! now I'm back to work. There's a new Fact Table coming right up...

Extra comment on October 2013: The problem results from the SQL back end being on an x64 machine. Please note that for Access front end you need your SQL server placed on X86 Machine.

No id, please

When I was 16 I got my first id card, that’s obviously a milestone in every person’s life. Not even using it for much (maybe for seeing M rated movies) it means that I have “id”. The second time I got an id was after I immigrated to Australia; as an immigrant to show Australian id is a real milestone.
In the SQL world - databases vs. data warehouses - we have two process. Learning databases you learn that normalization involves that each table has a unique key, and that every row in each table can be identified by a unique key. The first normal form requires every row to have a unique key. This unique key can be an “id” - identifier, or a composite key.
For many years I’ve been working with MS-Access. In Ms-Access, the best practice would be to have the unique key as an Identifier and not as a composite key; that’s due to the fact that if you want to link from one database to another it links the tables better. It’s usually best practice to split the access application to back end (data) and front end (user interface)  in two different mdb files, so when you try to change network location (or migrate the date to sql server) and you need to change “in bulk” the location of the referring database, if you have a field that’s defined as an “id” it would be done quicker and more accurately than it would if you have a composite key.
In data warehouses’ design things are different. While designing a dimension every row must have a unique key which is  a foreign key in the fact table. This unique key better be truly unique: not only it should be different than the “name” or the natural key of the external system, it should also be acting as a surrogate key, in case we’re going to change the “external system” that we’re working with. But apparently, a fact table doesn’t have to have a key! A unique composite key is “good enough”, and that's because we're dealing mainly with measures, and some people claim that even that isn't really necessary. Think "Sum" and then "group by"; no, we don't need Id as identity over there, do we? Moreover, I’ve found out that using a composite key gives me the ability to minimize the data and to warn me whenever I’m doing an error, like inserting the data with the wrong date. (I don’t disable the indices while inserting; I’d rather lose half a minute waiting for data to be inserted, than lose a day’s work thinking what went wrong with the job I’ve done. For my size of database it is “a good enough practice”).
So far I’ve found out that’s personally, this is the most difficult part of “denormalization”. Another thing which I needed to get my head around is that actually, when we’re discussing Hierarchies in SSAS, we don’t really need the “id” there, especially not for Slowly Changing Dimensions. Let’s take “employee” as an example: I’d group by the key of “employee id” and add a line for a change in the name, for example. As I group by the “employee id” I would like to see all that’s relating to a certain employee no matter if they got married/divorced in October and therefore changed their name... Keeping the “id” as an attribute, but outside of the “Hierarchy”, enables me to browse the Hierarchy correctly without seeing repeating lines of Employee Id where I shouldn’t.

Update on a linked table failed

The problem:
"Update or insert of view or function 'function_name' failed because it contains a derived or constant field (#4406)"
in my access database which is linked to SQL server

The reason:
The underlying view is of type "union"

The solution:
change to a single source (not a union select)

Where did all my values go to?

Or: Mess with values that you enter in a datasheet view.
This is the scenario:
There is a form which contains a sub form which is linked to values in a text box on the form (or to values in a different sub form). The form is displayed as datasheet.
You enter the values you need, all of them are from different linked tables,
and when you go the the next record your values had disappeared and instead you get totally different values!
For example:
the value should be "style" in the outside form
so for the style "myStyle" on the Parent Form the values in the sub form should be:

Bold 5%
Italic 10%
Normal 30%

But when you insert them they're seems to be taking from an existing style somewhere else. When you look in the table in the database, the values had entered correctly, it's just that the display is wrong.


The solution:
Add the id of the linked value to the subform, just hide it. That sort the mess out!

Environment:
Ms-Access 2003, Linked SQL tables.

The order of columns in a Access Query

If you write a query in Access using the design view, and then move to "datasheet view" and change the order of the columns, the columns' order stays the way you've closed it.
But if you try to "Export to Excel", the order of the columns will be the same as it was defined in the design view. You need to change it in the design view in order to get it in the order you want.

summing up decimals

I've just created a view that summs up percent value, that is saved in my SQL back-end as Decimal (5,4).
It resulted in the datatype (DECIMAL (38, 4)). Those 37 non-necessary digits were read by the Access database as Text, not number. If the number is treated as Text, then that causes ceveral problems to the access database: for example, it cannot format it as percent.
Since I don't really need those 37 digits, I've changed it to Decimal (6,4) (with an extra digit just to be on the safe side ; the sum shouldn't anyway exceed the 100%) and re linked the table. That sort this problem out.

Enter Parameter Value when exporting to Excel

The following problem is quite common, I believe, but without a simple solution.

I've create a query which depends on a parameter from the end user (being entered via a form). It runs beautifully.

But on "Export to Excel" (a command that does DoCmd.TransferSpreadsheet) it asks for the parameter value again.

The solution:

dictate the parameter to the query.

That means that instead of placing the parameter in the query, and placing the parameter there (Let's say, SELECT quantity, ..., FROM orders WHERE quantity > Forms!msg_quantity! txt_quantity)

You would place it in the VBA code like that:

Private Sub CreateQuery()

Dim qdf As DAO.QueryDef

Dim sSQL As String

sSQL = "SELECT quantity, ..., FROM orders WHERE quantity > " & Forms!msg_quantity!txt_quantity

Set qdf = CurrentDb.QueryDefs(Me.Form.Caption)

qdf.SQL = sSQL 'This saves the query

qdf.Close

End Sub

It is slower, of course, but if the client really needs exporting to Excel then the client can get it without the unusable "enter parameter value" text box. Trying to hide the text box didn't work in this case.

This way the parameter value is known to the query on export time.

Save changes to the following objects?

Aboout a year and a half ago we changed from using Access (full version) to Access 2007 runtime. It is cheaper (no need to install new Access on each user machine) and it is safer (I can control which objects do the users see) but it is more sensitive, so errors that I didn't have in Access 2003 full version appear all of a sudden in the runtime version. Access 2003 is simply more forgiving, especially if you tell it to load without openeing the database window. It is kind of horrible: you do something, test it, and after uploading the changes the users complains. The following error was in particular weird since I know it was tested in the Access 2007 runtime.
Another pain is to test it & develop on the same machine: each time you change version a prompt comes up with "installing...." which takes at least one minute of my precious time.
So today, after uploading the new version, my users complaint of the following error while hitting the "close" button of a form:
A form appears (see picture) asks you to save the forms. We are talking about users, not developers, and since when can you save changes in Access 2007 runtime?
I thought it was due to the multi user environment, but running a test version from my machine (for which I am obviously the only user) replicated the same problem.
The problem resulted from the following piece of code:

Private Sub Form_Current()

If get_se_listed(Me.pl_security) <> 0 Then
Me.pl_trade.DecimalPlaces = 0
Else
Me.pl_trade.DecimalPlaces = 2
End If

End Sub

Changing the decimal places (an option which is usually done in design time only, and not at run time) resulted in the definition of the form changing as well, and that prompts for the "save" problem. Goggling the issue had left me with only one (!) post about it in the forums, right here.
So first I eliminated the new changes, otherwise the users, non violent people in general, might kill me by the end of the day.
I decided to try and test for myself wheather prompting it not to save on close would actually work:
I added the code for the event "form_close" asking kindly to close without saving
docmd.close acForm, "sub_form_name", acSaveNo

It worked in Access 2003 but it didn't work in Access 2007 runtime version. At lease when I pressed the "no" it prompt me for saving (Again!) but didn't end up with a nasty "Execution for this application has stopped due to a run-time error" which I'm all to familiar with.
Adding the code to the form and not only to the sub_form had not helped. This time it did crash eventually.
In order to avoid the crash you can press 'Yes' and you can press 'No' but do not press 'Cancel'!
Also don't put the line
docmd.close acForm, "form_name", acSaveNo
or acSaveYes in the parent form name.

Trying to play with the Form_current event led to the conclusion that only for an empty function
Private Sub Form_Current()
End Sub
I wouldn't get the prompt. Please note that the form default view is in Datasheet. But I do need the design!

This Round Thing

I work on Ms-Access, VBA and

I have a legacy code and inside it there is a self-written ROUND function, as follows:

Public Function Round(v1 As Variant, v2 As Byte) As Double

If Not IsNumeric(v1) Then

Round = 0

Else

Round = INT(v1 * 10 ^ v2 + 0.5) / 10 ^ v2

End If

End Function

I never knew why it was there but as the old saying goes: if it's not broken, don't fix it!

Today I found out that it is broken, i.e. it simply doesn't round to the required precision.

Thanks to Allen Browne, and more specifically to the MVPs of Access, I found out that

a. Access rounding may not existed when the database I am working on was created

b. Even if it did, it uses "banker's rounding" which may not be adequate

c. I shouldn't be using cast as INT, but instead, a different type of CAST.

That's because INT truncates the decimals, which is not my intention.

Like this:

Public Function Round(v1 As Variant, v2 As Byte) As Double

If Not IsNumeric(v1) Then

Round = 0

Else

Round = CLng(v1 * 10 ^ v2 + 0.5) / 10 ^ v2

End If

End Function

And that makes the rounding beautifully done!

The beauty of the function supplied by the MVP's of access is that it can deal with very large numbers, which is a bit problematic in my version.

There isn't enough memory to perform this operation

I usually use MS-Access to program, and I got the following message:

There isn't enough memory to perform this operation. Close unneeded programs and try the operation later.

When I tried to open a certain form (in design view or in form view).

Needless to say, I closed all the programs available (including my messenger) and it didn't help much.

The reason was: too much IIF() in the text boxes of the form. Access had problems loading it.

The solution: Copy the form from the backup.

Move all the IIF required to the back end (in my case: the underlying SQL view).

Now the form loads up quickly and correctly!

Moral of the story: don't use too much IIF().