Microsoft Access error: Compile error . in query expression

Sometimes you just can’t win. I went through writing this super duper module in Visual Basic, which does all sorts of fancy stuff. I made sure that it can handle any error, and keep right on going. I am worried that if Microsoft sees this module, they will try to buy me, and copyright my brain. Ok, it isn’t quite that good, but you get my point.

Now when I open any of my queries, I receive the error:
“Compile error . in query expression ‘val(nz([proj_name],0))’”

It seems that the error is with any of the built in functions. If I remove the val statement, I receive it for the nz statement.

Of course, being a modern information worker, I used half of Google’s bandwidth to search for an answer, and I found some really great ones, just none that helped me. Generally, this type of error deals with a missing reference in the visual basic editor. Allen Browne has a terrific page about Solving Problems with Library References (thanks for that page Allen).

He also points to another incredible resource, Doug Steele’s Access Reference Problems

In my case, none of these steps worked for me. I did manage to narrow my problem down to one single statement. It seems that when I use the call statement to run a sub procedure, I get that error. The really neat thing is that I don’t even have to touch the module, all I have to do is have that call statement in there, and magically, none of my queries work.

I even tried converting the sub into a function, and I still get the errors if I have any code in my module that invokes the function.
I have made a post to the very helpful people that read the Microsoft Newsgroup Server. Unfortunately, I posted to Microsoft.public.access.modulescoding, and it doesn’t look like Google carries it.


Update: My post on the newsgroup was answered by Allen Browne. What a helpful guy. He had me choose compile in my vb module, and fix all of the errors, after that, it worked like a charm.

Strange SharePoint Team Services 2 Restore Issue

Like a good administrator, I was testing my SharePoint site backups, and was having a devil of a time getting it to work. Before I go into too much detail, if you are not familiar with backing up and restoring SharePoint using the stsadm command line tool, then you probably don’t want to read any farther.

If you are reading this, than you probably already know how to use it, or have looked at this Command Line Operations guide to stsadm.

To continue, I was testing the restore of my backup, and kept running into an error "Exception from HRESULT: 0x80040E2F”. I tried the usual, creating a site, and then deleting it, and verifying the versions, all to no avail. Of course I have gobs of free time, watch out for the puddle of sarcasm over there, so it wasn’t too big of a deal.

I had tested the backup and restore very thoroughly when I set up my neat little script, which backs up the entire SharePoint site every night, with the convenient file name of year-month-day-sharepointbackup.dat. As it turns out, what I was doing was renaming the backup file to sprestore.dat so that I didn’t have to do all that typing when I tried the restore. Once I stopped renaming the backup file, it worked like a charm.

Oh well, we live and we learn.

PS: If you have any interest in the script, reply to this post, and I’ll be sure to clean it up a little, and post it.

You may also like:
- Tools to work with SharePoint Offline – Review of Revelation and Colligo for SharePoint
- Script to Back up SharePoint Team Services 2

WSUS Update

I have had WSUS installed and running since Monday. On Tuesday, I had finished playing around with my test group, and I decided to go ahead and use it on the whole company. Let me tell you, I really like it.

Reporting Capabilities:

WSUS has some really nice reporting capabilities. You can pull reports on all of the computers that need updates, and then drill down to see what updates. One useful option is to pull the reports on all computers that have updates with a failed installation. This report even lists the error code for each of the failed updates.

The error I have run into is 0x80070643, and KB Article 836937 lists some methods to fix it.