Tony's Microsoft Access Blog

Google has screwed up yet again

So I go to do my favourite technical question search method which involves searching just the microsoft.public.access newsgroups and it totally fails.  Worked just fine a few days ago.

Start at groups.google.com and enter your search term.  For example "access books vba" but without the double quotes.  

Now if you just click on Search Groups you get many, many useless, duplicate hits.   The problem is that there are many web sites out there mirroring the NNTP Usenet and Microsoft newsgroups.   All so they can sell Google advertising.  

I should note that there are a few genuine online forums such as Utter Access and, think, the Access World forums.   However the vast majority are bottom feeding, scum suckers whose only interest is advertising revenue.

So as to limit the search just to the Microsoft newsgroups I would then click on Advanced Search go down to the Group name field and enter microsoft.public.access.*  

And that doesn't find any hits.  I *KNOW* a few days ago this worked just fine. 

So Google has screwed up the NNTP newsgroup search interface yet again.    Ahh, for the good old days of dejanews.com and deja.com.    They did only one thing and did it well.

Dilbert 2009-06-30

http://dilbert.com/strips/comic/2009-06-30/

Hey, I thought it was funny.

Key fobs for logging onto domains

A while back I read that Paypal were offering key fobs for logging onto their system.   Also a while back fellow MVP Susan Bradley mentioned the AuthAnvil Tokens in her SBS Diva blog entry The "Later" Decision.   This is looking like a very interesting solution for business environments.

What's a key fob? I'm not sure exactly what the official name, if any is.   This is a device which is about the size of your little finger and generates a 4 to 8 digit random number every 30 or 60 seconds.  You use it in addition to your userid and password to log into systems.   So this is an extra authentication measure.

Now I'm no IT admin so I don't understand all the nuances here.  But it would appear that you can, for example,  force users who are remote to use the key fob and yet if they are on your physical LAN they can log in without it.

Looks like a very useful idea.   And Dana Epp (good Mennonite last name there) is a fellow MVP.

(Jim, I have additional information if you're interested in it.)

Misleading error message - "Microsoft Access Was Unable to Create an MDE Database"

Someone got this message using Access 2003 but this could also apply to other versions of access.  Clicking help on the error message then displays

"This error is usually associated with compiling a large database into an MDE file.  Due to the method used to compile the database, a considerable number of TableID references are created for each table.  The Microsoft Jet database engine version 4.0 can only create a maximum of 2048 open TableIDs at one time.  Exporting a database as an MDE potentially can exceed this limit if the database has a large number of objects (table, macro, form, report, etc).

There is no accurate method to estimate the number of TableIDs the Jet database engine uses during the process of compiling a database as an MDE. However, each VBA module and each form uses one TableID, as a result, if the database has 500 forms, and each form's HasModule property is set to Yes, as many as 1,000 TableIDs are used."

(I included the entire message so that folks searching on this message could find this blog posting.)

This posting sounds ominous but unless you have more than 1000 objects in your MDB/ACCDB you're not even close to having this problem.  And that assumes that 1000 objects also have VBA code. So, for example, you'd have to have 100 tables, 300 queries and 700 forms and reports that also have VBA code.

The first thing to try is compiling the code to see if there are any errors.   Ctrl+G to get to the VBA editor >> Debug >> Compile

Death by Delete - Backups, backups and test your backups

"One day, a developer was optimizing the database and removing records that MegaPetCo no longer needed. All it took was a single, poorly formed delete query to wipe out each and every row in the database table."

"...  but that was the least of the company's worries. The status of its backups was bleaker still: None. Zip. Zilch. Nada."

Death by Delete

Who is reading your email?

E-Mail Surveillance Renews Concerns in Congress   What about your ISP or mail server hosting service?  Or even Google, Yahoo or Hotmail. 

For the record I've had discussions about privacy issues with employees of Microsoft.  Not only do I doubt corporations snoop through your email on a regular basis I'm sure access is tightly restricted and logged.  Therefore should an upset ex girl/boy friend/spouse/whatever start snooping around and you make a complaint then it's very likely their employer can go through the logs and fire them.

But then who knows what happens if your at the periphery of an investigation because of your brother-in-law who you've never liked.  Yes, one of my job titles is paranoid pessimist.

Security experts state that you should think of email as a post card rather than a letter in an envelope.

I'd like to see greatly increased use of encryption in emails.   Look up security, encryption, digital signatures, digital IDs or S/MIME in your email software help system.   And you can get a personal certificate for free using Thawte's Web of Trust.

While you're at it see if you can use SSL authentication with your ISP or web hosting service as another security layer.   I use OpenVPN between my laptop and my web/email server.

Steps to think about when doing a data MDB back up, compact or zip.

I have a button on my Granite Fleet Manager app which allows the user to do a backup of the backend data MDB. I also have two other buttons which zip and email the backend data MDB to either themselves for backup purposes or to me for support purposes.

However any open forms or reports will lock the back end MDB file so a rename and compact won't happen.

As I do not know which forms and reports the user might have left open when they click on any of the buttons I run through some code that closes all open forms and reports, then run the code doing the rename, compact back to the same file name and, if requested do the zip and email.

' close all forms and reports
For Each frm In Forms
    DoCmd.Close acForm, frm.Name
Next frm
DoEvents
For Each rpt In Reports
    DoCmd.Close acReport, rpt.Name
Next rpt
DoEvents

DoEvents seems to be necessary to give things time to close. But maybe not..

Also note that I do keep a status form open during the above operation. I also do not have any list boxes or combo boxes with the row source set to a query or table referencing the data MDB on the form. And I don't have any open recordset or database variables for performance purposes.

You can double check that this code works by stopping the code just after the above and seeing if the ldb file on the data backend mdb no longer exists

Of course if there are other users in the back end then the rename won't work of course. Also see my Backup, do you trust the users or sysadmins? tips page for more info.

An older version of MSCOMM32.OCX has had the "kill bit" flag set.

The latest Windows Update has set the kill bit flag for an out of date but commonly used version of mscom32.ocx.  Microsoft Security Advisory (969898)

Options

- roll back the update.  Not recommended however instructions are in the above web page.

- locate the newest version of mscomm32.ocx and distribute to your users/customers.  Not recommended as this will require the users to have admin privileges on their system to install the OCX or for the IT staff to do the update.  In the meantime your app's serial port logic isn't working

- replace the mscomm32.ocx with API code.  Perform Serial Port Communication - The sample code on this page consists of a VB module containing a collection of routines to perform serial port I/O without using the Microsoft Comm Control component. The module uses the Windows API to perform the overlapped I/O operations necessary for serial communications.  Recommended

Symptoms

Errors such as

"<your application> doesn't support this ActiveX control".

"no inserted control" (or something like that)

"Object doesn't support this property or method"

When you open the form in design view you will get the following message "There is no Object in this control" and the control will no longer show the telephone icon.

ioSafe Solo USE External Hard Drive with Disaster Protection

Now here's an interesting product. ioSafe Solo External Hard Drive, Fireproof Waterproof Rugged USB in 500 Gb, 1 Tb or 1.5 Tb capacities.

Note however that the fire rating is only 1/2 hour at 1500 F.    While this is probably quite adequate for most disasters if the fire is real bad this won't be sufficient. Or the proverbial tornado, etc.  You will still need offsite backup.  

That said I'd put this device in it's own closet with fire resistant drywall on all the walls and ceiling as well as a steel fire door.  And a sprinkler.  

My job description today is paranoid pessimist.

Microsoft Forefront is flagging the Auto FE Updater as a virus

I've just received an email from a user of the Auto FE Updater stating that Microsoft Forefront is flagging the StartMDB.exe as a virus.  

If this happens to you try a newer or older version of the utility.   Also please use the Microsoft Forefront Client Security Support Options page to inform them of the false positive.  

When I receive more details from the user such as the version number I'll update this blog entry.

Smart Access Magazine

Garry Robinson, fellow Access MVP, has purchased the Smart Access Magazine.  there was 300 articles/2000 pages written by over 100 of best Access professionals in the world under the guidance of editor Peter Vogel.

(Although I do recall emailing a few authors some corrections and never hearing back from them.)

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"

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. 

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

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.

Office 2010 The Movie

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

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.

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."

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.

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.