27 November 2014

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?