Tony's Microsoft Access Blog

How to get at Jet warnings that are not errors

How to get at Jet warnings that are not errors by Michael Kaplan.  Note the discussion about "There is the Jet 4.0 feature of record level locking that people love ...".

Microsoft Access makes Inc Magazine

Ok, ok, so it's only mentioned once and in the last paragraph on page one. See How Hard Could It Be?: Unfocused and Unabashed.   Which is linked from Joel Spolsky's article Unfocused and Unabashed.  His original article on this topic was How to Ship Anything

Divide and Conquer: Debugging SQL statements created in VBA code

How do you debug the appropriate use of ' (single quote) or & (and) in the following SQL strings being built in VBA code?

A simple example from a newsgroup posting is as follows.

SQL = "INSERT INTO TABLENAME VALUES('" & Forms!Test!name & "', '" &  var "')"

I debug these kinds of problems by throwing a ' (single quote) in there and seeing if I still get an error.   Then I move the ' over to the right until I get the error message.

But what about multi line SQL code such as the following.

' Append records to the ServiceRecordsTech table
strSql = "INSERT INTO ServiceRecordTechs ( srtServiceTechID, srtServiceID, srtHours ) " & _
    " IN '" & strDatabasePathandName & "' " & _
    "SELECT ServiceTech.stID, ServiceRecords.srID, ServiceRecords.srLabourHours " & _
    "FROM ServiceTech " & _
    "INNER JOIN ServiceRecords ON ServiceTech.stServiceTech = ServiceRecords.srServicePerson " & _
    "IN '" & strDatabasePathandName & "';"

First I always have the Edit toolbar displayed in the VBA IDE.  (I don't recall if this is a standard toolbar so to add the Edit Toolbar right click anywhere on the ToolBar and select the Edit Toolbar. )  

Then I use the Comment Block/Uncomment Block buttons on the last few lines of code.  I also have to remove the "& _".

strSql = "INSERT INTO ServiceRecordTechs ( srtServiceTechID, srtServiceID, srtHours ) " & _
    " IN '" & strDatabasePathandName & "' " & _
    "SELECT ServiceTech.stID, ServiceRecords.srID, ServiceRecords.srLabourHours "
'    "FROM ServiceTech " & _
'    "INNER JOIN ServiceRecords ON ServiceTech.stServiceTech = ServiceRecords.srServicePerson " & _
'    "IN '" & strDatabasePathandName & "';"
CurrentDb.Execute strSql, dbFailOnError

If I don't get an error message then I uncomment another line or two and add in the missing "& _" and keep on going.

Learning Word 2007 if you're visually impaired

A number of techniques in the blog entry Learning Word 2007 if you're visually impaired should also apply to Access, Excel and PowerPoint.

Using bar codes within an application

I've added Henry Habermacher's excellent links on UPC bar codes to the Using bar codes within an application page.  I also removed some dead links.

Office SP3 is available for downloading

See Office 2003 sp3 for some details.  Or you can download it directly here.  Only 117.7 Mb.  I'd suggest waiting a week or so just in case there are some surprises.

A conversation ... about the reinvention of air travel

A conversation with Ed Iacobucci about the reinvention of air travel  A very interesting story about what goes on behind a totally new system of air travel in the US. 

The Risks of Letting Students Use The Network

The Risks of Letting Students Use The Network A very interesting blog entry with some links to a teacher using a chat room during her class.  Chat rooms have been around for years but here is a very interesting use.

Mars Rovers Resume Driving

Mars Exploration Rover Status Report: Rovers Resume Driving  Very impressive to see they're still working.

I Didn’t Know You Could Do That!

From Worse Than Failure I Didn’t Know You Could Do That!

I put a humour tag on this blog entry but I should really add the tag pathetic.

Microsoft jet database engine could not find object 'xyz.dbf'...

The following was added to the Some Microsoft Access MDB Corruption Symptoms page

4b) 3011 - Microsoft jet database engine could not find object 'xyz.dbf'...  Notice the dbf file extension.  A dbf file name needs to be in 8.3 DOS name format.   It is quite possible that some machines can open the file with a longer file name and others it must be in 8.3 DOS name format.  Thanks to Marc L. Allen for this one.

Open your new laptop box before you leave the store

The clerks and such at stores look at me like I'm an idiot.  But every time I've purchased a box containing something expensive, such as a laptop, I always open it right there at the clerks station and verify that yes indeed it looks like a laptop.   After all, how could I prove that there was just some bricks in the laptop once I've gone home.

One clerk said to me: "But that's the original factory sealed tape."  My reply "And how do I know that.  I don't care. This is several grand so everyone in the lineup is going to have to wait another twenty seconds."

Also Robert Scoble's interesting story Reminder to always open your boxes… about the empty boxes in the store.

Programmatically defaulting item(s) in a list box

I don't do this very often so I always keep forgetting how simple this really is.

The following code, in the reports open event, defaults the very first item in the Status list box to being selected.
Me.lstStatus.Selected(0) = True

The following code defaults the location in the Branch Office list box which corresponds to the users default permanent location.
If Not IsNull(Forms!GlobalOptionsHidden!suDefaultPermLocnID) Then
    For i = 0 To Me.lstPermLocn.ListCount - 1
        If Me.lstPermLocn.Column(0, i) = CStr(Forms!GlobalOptionsHidden!suDefaultPermLocnID) Then
            Me.lstPermLocn.Selected(i) = True
        End If
    Next i
End If

Note that the form GlobalOptionsHidden stores all the fields in my Global Options table and a few other selected tables for use in my app as required.  In this app I also am storing the fields from the System User table which include the default permanent location.  

Also note that almost all occurrences of the words temporary location and permanent location in form and report field captions have been replaced with the user terms defined on the Global Options table such as "Job Site zzzzzz" (Hey, I'm testing ok.) and 'Branch Office'.

The question mark buttons opens a form bound to a table of help text which explains the permanent and temporary locations in one record as well as other topics in other records.  I've chosen to roll my own help as most of the system is easy to use.  No, really it is.

So now when the form is opened (and I selected a report) you can see the effect on the middle and right hand list boxes.

Why do I sometimes need to set the Subdatasheet property to [None]?

I've noticed that occasionally, and for no apparent reason, Access will sometimes set a few tables SubDatasheet property to [Auto] after I had updated the table definitions.  I just shrugged and started running the code as mentioned at BUG: Slower performance on linked tables in Access 2000 every time I open the FE.  Of course I makes sure I run the code against the backend MDB.

Tom Wickerath gently pointed out to me that "That's only true if you have not first disabled Name Autocorrupt".  (Actually Name AutoCorrect.) 

Now I don't run it that way very much.  I mostly turn it on when I need to update some field definitions and want the queries to automatically change as well.  So I've never had the problems with it that others have had.

The Performance FAQ page has been updated accordingly.

Thanks Tom,  Tony

Setting Startup Options Programmatically in Microsoft Access

I saw a newsgroup request on this and I realized I had all the code in one of my MDBs somewhere.   Likely took my longer to find it than it did to create the web page.  So I just created Setting Startup Options Programmatically in Microsoft Access page. 

Issues ... Access ...Windows Vista (or Windows Server 2008)

See Issues that you may encounter when you open or are using a Microsoft Office Access database on a computer that is running Windows Vista - 935370.   Note that this is an SMB 2.0 issue and thus Windows Server 2008 could also have this problem.   Looks like this could be similar in some respects to the OpLocks problem back when Windows 2000 Server was introduced.

The Access Corruption Symptoms, Causes and Access Performance pages have been updated.

Edit: Changed Small Business Server 2003 to Windows Server 2008 as per Susan Bradley's query posting.  I got confused between the SMB signing problem and SMB 2.0. See New Networking Features in Windows Server 2008 and Windows Vista and do a search on SMB 2.0.  Added links to various Access pages.

Interested in the bowels of the MDB file layout?

MDB Tools is a set of open source libraries and utilities to facilitate exporting data from MS Access databases (mdb files) without using the Microsoft DLLs. Thus non Windows OSs can read the data.  Or, to put it another way, they are reverse engineering the layout of the MDB file.   Download the file and open the hacking file.  

We know that a number of outfits, such as Peter Miller's PK Solutions and other Access repair facilities do understand the layout to some or a great extent.  Peter has stated that having an older uncorrupted copy of the MDB, without the data but with the tables if I recall correctly, does help him with retrieving the tables.

Additional information on undocumented SysCmd items

I received the following email and thought it was worth sharing.  Note the international interest in this topic.

Hello Tony,

First I thank you for my pleasure to read your blog.

Digging the undocumented SysCmd commands are always have fun. I have searched once before few years ago and the followed thread was very helpful for me.

http://www.ms-office-forum.net/forum/showthread.php?t=99594

Some unknown functions will be known by this.
About the rest, I made my research results public on my site. http://x7net.com/~access/AcTipsUnDocumentedSysCmd.html

Sorry, the site is in Japanese because I am a Japanese. I barely read English but the writing is still so difficult for me,
now you knew that? ;-P

Few commands are still be unknown for me. But if you want to know about the things that I know, please point to the syscmd number. I will try to explain in English about it.

One thing, SysCmd #14 is officially known by the Access 2003 VBA Help. See the bottom section of followed MSDN online help. http://msdn2.microsoft.com/en-us/library/Aa221609(office.11).aspx

Thank you and Sayonara!

　// Yu Tang
  // Tokyo, Japan

Added Microsoft Office Access 2007 Developer Edition FAQ page

I've created a new Microsoft Office Access 2007 Developer Edition FAQ page.