May 2009 - Posts

DbUtilities - Transfer object permissions from a secured database to a new database container

The first item "is a fantastic re-write of DbUtilities which,  as an Add-in, makes it easier to transfer the object permissions from a secured database to a new database container. "  Author: Jeff Conrad (aka "Access Junkie"), former Access MVP and now a test for Microsoft.

Sandra Daigle's Microsoft Access Database Samples as well as other useful utilities.  Including "a way to open and manage multiple instances of a single form using a collection class"

Posted by Tony | with no comments
Filed under: ,

Redimming an array 1,000,000 times takes 4.63 seconds

The following question was asked at StackedOverflow.How do you redimension an array in VBA?.  One response was some detailed code showing how to redim an array efficiently by doubling the number of elements.   This took 0.41 seconds for a million records while redimming 1,000,000 took 4.63 records.

I don't think I've ever had over 100 elements in an array.   Maybe 1,000 once or twice. And now I'm not at all concerned about efficiency while redimming.

Note that I only occasionally visit that website to answer questions.  My biggest complaint is that you never know what comments or postings are new and haven't been answered.  Very much unlike using a NNTP newsgroup reader rich client.   But it's interesting to poke about to see what's new has been answered.

It's also interesting to read what bull**it has been spouted by folks who view Access with a lot of disdain.  David W. Fenton is doing a fine job of answering such comments. 

Posted by Tony | with no comments
Filed under: , ,

Latest Access 2003 hotfix - 970623

"When you try to import all objects from an Access 2003 database into a new one, you receive the following error message:   There isn't enough free memory to update the display. Close unneeded programs and try again."

Access 2003 hotfix package April 28, 2009 - 970623

Posted by Tony | with no comments
Filed under: ,

Outsourcing troubles

Health workers file lawsuit over **** payroll system
Staff urge school board, health region to dump gaffe-prone **** payroll system

These are in my home province.  The name of the company providing the troublesome service is irrelevant.  The troubles with outsourcing are quite relevant.

Posted by Tony | with no comments
Filed under:

Office 2010 The Movie

Office 2010 The Movie  Interesting video that.  But notice the link to the Office 2010 blog.

Posted by Tony | with no comments
Filed under: ,

Microsoft Office 2010 Technical Preview

The Microsoft Office 2010 Technical Preview is a limited, invitation only program which will provide you with the opportunity to experience early, pre-release versions of Office 2010 which will include the following applications:  Word 2010, Excel 2010, Outlook 2010, PowerPoint 2010, OneNote 2010, Access 2010, InfoPath 2010 and Publisher 2010.

Posted by Tony | with no comments
Filed under: ,

Exceeding 2 Gb MDB file size (and a bit of TGIF humour)

Exceeding the 2 Gb MDB file size gives you an exceedingly misleading error message:

In Jet 4.0 one message that is received when the 2 Gb file size is exceeded is invalid argument.    This is, of course, utterly confusing and can mean other things such as corruption.

In A2007/ACE (I was running in an ACCDB) I got the following message

3049 Cannot open database ''.  It may not be a database that your application recognizes, or the file may be corrupt.

running the following code.  (Watch for line wrap.)

Const FillText As String = "ASdlkjSLDJKADSLJADSLJ ASDLJK ALSDJ LASJKD
LJASLDJK ALSJKD LAJSD lJKA SDLJK ASDLj ASDLj ALSDJ LJ " & _
    "ASdlkjSLDJKADSLJADSLJ ASDLJK ALSDJ LASJKD LJASLDJK ALSJKD LAJSD
lJKA SDLJK ASDLj ASDLj ALSDJ LJ " & _
    " AS:LK:ASDK A:D :AKSD :ASDK:AKSD :AKS D adfasdf asd fasdf asdf"
Sub Filltable()

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("Table1")

tagTop:
    rs.AddNew
    rs!field1 = FillText
    rs.Update
    DoEvents
    GoTo tagTop

End Sub

Now this code runs for several hours so be patient.  I only ran it inside Access 2007 and ACCDBs.  I'll run it in Access 2003 just to double check that message.     (Out for steak, Caesar salad and mugs of red wine with friends.)

Now note that you don't want an autonumber key on the table.  Just the text field.  I suspect by the time you hit five million records the overflow areas of the index will get real, real slow.  But I don't feel like testing that.

Humour

Bill Mosca, fellow Access MVP, had the following to say about my FillText string:  Here's a site for greeking text...in case you need to fill a longer string without monkey-typing it yourself.
http://www.duckisland.com/GreekMachine.asp

Of course another alternative is Lorem Ipsum.    I used a paragraph from that in a reply to someone who stated "Of course, we're going to have to argue that one for a while."

Posted by Tony | 2 comment(s)
Filed under: ,

GIGO Almost Caused Plane to Crash part 2

in GIGO Almost Caused Plane to Crash I mentioned how I screwed up a Dun and Bradstreet database by putting two extra digits for the cents in the data sent to them when dollars were expected.  However I forgot to mention the reasonableness check.  D & B should've totaled up the annual sales on the tape that I sent them.   A reasonable number would've been total sales to all the customers of between $10 and $100 million dollars.   However $1 billion or more would be unreasonable for a company that is not a household name in Canada at that time.  $5 billion would be exceedingly unreasonable as a guesstimate.

Posted by Tony | with no comments

Dreaming of Rails as the Next Microsoft Access

An interesting blog posting titled Dreaming of Rails as the Next Microsoft Access  (Rails meaning Ruby on Rails of which I know nothing other than having seen the occasional reference it.)  Now I disagree with the authors characterization of us as "Office drones".  <smile>

Nevertheless interesting reading.

Note that I located this blog entry because I'm now following #access, #msaccess and #microsoftaccess items on Twitter.  I'm currently using the Tweetdeck client but I'm not overly impressed with the UI.  I also am Tweeting (I think that's the correct verb) my blog postings.

Posted by Tony | with no comments

GIGO Almost Caused Plane to Crash

A very interesting story:  GIGO Almost Caused Plane to Crash "Instead of entering the true weight of 362 tonnes on the laptop, the aircraft weight actually entered was 262 tonnes. "  The airline now has a second laptop for data entry.

But I wonder if a better approach would've been a reasonableness check.  That is factor in the empty weight of the aircraft.   Add the weight of the fuel which has to be entered somewhere somehow.   Then enter the number of crew and passengers and guesstimate the weight per person.  Which airlines already do.  Also enter in the weight of the freight.  Then compare that rough computation to the weight calculated by the airline dispatch which is entered.  If it's accurate with 5% or 10% then great. 

For example

Empty weight is 170,400 kg 170 tonnes
80% of Max fuel 214,810 L at 0.8075 kg/L 139 tonnes
275 Passengers with luggage @ 100 kg 28 tonnes
    Rough guesstimated total 337 tonnes
Entered weight which was in error 262 tonnes

Information sources - Wiki - A340-500, Wiki - Jet Fuel

The guesstimated weight is 30% over the entered so a severe reasonableness problem.  Unless I've screwed up a computation especially the weight of the fuel.  I chose to use 80% of the maximum allowed fuel as this was a long range flight.  But even 50% of the fuel would've failed a reasonableness check.

I also had no idea how much fuel is on a typical long range jet.  No wonder the landing was hard as they were unable to dump enough fuel.

Finally I'm glad for the sake of the lives of the crew and passengers that there was a control called "TOGA on the thrust levers" which allowed the flight crew to get maximum power.

Other situations

Every few years I read of a similar problem in the stock market.  A clerk enters either a stock quantity or price which has an extra decimal place or two in it.  And it costs the clerk's employer millions to unwind the stock trade.

My screwup

Your esteemed blogger once did the same 25 years ago.  The client, a major Canadian heating, ventilating and air conditioning wholesaler, wanted to send customer data to Dun and Bradstreet including the customers annual sales as well as current, 30, 60, 90 and 120 days overdue amounts.   I happily setup the 1200 bpi reel tape files with all the appropriate data.    The accountant then calls me in a week or two later and tells me that I included the amounts with the cents when I should've included just the dollar amounts.   So a given customer showed $10,000,000 in annual sales rather than $100,000.  And $200,000 in 30 days outstanding rather than $2,000.

I was told that Dun and Bradstreet never noticed for a week or so and then had to labouriously back out my clients specific numbers from the data.  

Oops.   

Posted by Tony | with no comments

Image control events on continuous form - Access 2007 SP2

"The main window of my app is a Continuous forms form where each row
has several small graphics (filepath bound image controls) and a few
text fields. When the user clicks on one of these, several events fire
based on which ROW the user clicked on. After installing the SP2, when
the user clicks on any of the image controls, the events fire as if
the user has clicked on the Top row, no matter what row he clicked on. "

MSA Runtime SP2 screwed up my App!! Options

This one has not yet been confirmed by others or Microsoft.  

Posted by Tony | with no comments
Filed under: ,

Navigation Pane Description missing - Access 2007 SP2

"Use MS ACCESS 2007 to manage large volume of accounting data, and have many
tables and queries.  I enter a description in these tables and queries to
help me remember what they all do.  MS OFFICE Service Pack 2 was released
into my machine last night, and now the descriptions have disappeared in the
navigation pane I use to run queries."

Navigation Pane Descriptions -2007 Office Service Pack 2

Confirmed by multiple people and Microsoft.

Posted by Tony | 1 comment(s)
Filed under: , ,