VBA

VBA: Transforming XML Error Messages into VBA Errors (Raising or Throwing Errors)

This is trial code that I used to translate an error from a Yahoo web service into a COM ErrObject.

It's not real XML parsing, but good enough for this purpose. IF an error message is sent, we extract the message and then use Err.Raise to throw an error.

MS Access: Quoting Strings in SQL

I was having a real WTF moment with Access. I'd coded up an SQL query in access, and a string had a single quote in it, fouling up the query.

The SQL was something like this:

SELECT * FROM Places WHERE Name='Joe's Bar'

Obviously, I forgot to quote the string correctly. For some reason, web searches didn't really turn up much about quoting text strings in SQL statements in Access. There was a lot of code that looked like this:

sql = "SELECT * FROM Places WHERE NAME='" & name & "'"

MS Access: Geocoding and Distance Reporting

This is some code and controls that help you geocode addresses, and prepare a report of addresses sorted by distance from a point.

It's based on the Excel Geocoding Tool, but expands on it by adding a few features, including caching of calculated locations.

Addresses are stored in their own table, and are normalized a little bit, so that you don't end up geocoding the same address over. (For example, if you have 50 people at an office, that location should only be geocoded once.)

MS Access: Address Cleanup Macros

Here are some Excel macros that help to clean up data. Once cleaned, it's easier to remove duplicates. (I used these to de-dupe a list exported from Outlook.)

Included is a rough version of MS Access' Nz() function.


Public Sub SimplifyEmails()
    ' This subroutine scans a column, turning emails in this form:
    '   Joe Blow (joe@company.com)
    ' Into this form:
    '   joe@company.com

    Dim Rng As Range
    Set Rng = Application.Intersect(ActiveSheet.UsedRange, _
        ActiveSheet.Columns(ActiveCell.Column))
    
    Col = Rng.Column
    
    N = 0

MS Access: Inserting Records with Visual Basic and DAO

This example shows you how to add records with VBA and DAO instead of with SQL queries.

MS Access, Outlook: recording bounced email addresses

This is a subroutine that will scan your Outlook inbox or a subfolder of inbox named "Bounces", and copy bounced email addresses to a MS Access database.

It will then join the table of bad addresses to another table (of people, presumably) and null out the bad addresses, so you won't send to them again.

MS Outlook and Access: Recording Bounced Email Addresses

This is the start of a macro that will scan your Outlook Inbox or a subfolder named "Bounces" for bounce messages, and record such messages to an Access database.

Wacky VBA, Bitchen PHP

I was spending a night debugging some heinous VBA code. It was my code, but it sucked. It sucked because it's pretty hard to write glue code that integrates different parts of MS Office. All I wanted to do was produce some distribution lists from an Access database, but thanks to Office Outlook's totally complicated system of managing email lists, you can't just spit out something like the old .alias file:
listname: joe@blow.com, foo@bar.com, blarg@foo.com
That would be easy. No, Outlook requires you to make a DistributionListItem, and then AddMembers to it.

MS Access VBA: Error -2147217900 (80040e14)

Jawahar on Expertsforge says this is an SQL syntax error where a keyword is used as a field name.

In Access, the app finds these keywords and quotes them before running the query. It's all done behind the scenes, but you can expose this feature through the query design tool.

Create a new query in design view. Bring up the SQL view. Paste your SQL in there. (You are probably already be at this point, testing your SQL and knowing it works.)

MS Excel: Cleverer Table Importer

These are some functions that help you write a script to import Excel data into a SQL database. What makes this different from the Access import feature is that the data can be poorly formatted.

MS Access VBA: Generating Code for Handlers

I've been working with "unbound forms" and, ay ay ay, what a pain in the butt. Unbound forms are regular forms, except that the controls aren't associated with any data source. An unbound form is like a panel of controls, without wires behind them, and no information being shown or recorded. It's like a prop without a performance. It's weird to use an Access form that doesn't seem to work, because the data structure behind it doesn't exist -- without information, there's no animation.

MS Access: Inserting and Deleting Contact Items With VBA

Gripe: VBA syntax is difficult. The object system is a little confusing too. It's just very hard to use. To make things even more difficult, the sample code out there is kind of *weird*. Maybe there's some good reasons for doing things their way, but, it just seems verbose, error prone, and hard to write, to me.

Here's some code that is the start of a library to work with Outlook's folders. It's based on some code samples from the web, refactored into something resembling a library.

MS Access: Printing a "Dictionary Header" on a Report

I wanted to print a report that indicated the first and last item on each page, just like a dictionary has. You know: "Azeri - Babcock", "Milk - Minder". It makes it easier to flip through printouts.

This is how to do it. It will put the range in the footer. I haven't figured out how to do one in the header, which is what I originally wanted, but found too difficult to do. (There is probably a way.)

First, take your report, and add an unbound field to your report. Rename it to "Range". See the picture below.

MS Access: Log Messages to a Table and Know What's Happening When Your User is Stuck

Here's some code to help you log messages to a table. First, make a table called tblLog, with at least these columns: Timestamp, User, Computer, Message. (You don't need a primary key.)

Set the default value of Timestamp to NOW().

Copy the following code into a code module.

Also, add a reference to "Active DS Type something or other".

Syndicate content