Category Archives: Tech_Friday

Simulate Access 2007 runtime

Clint Covington has a hint to allow us to get a preview of what a project will look like when running under the Access 2007 runtime:

I know, the runtime hasn’t shipped yet. If you are looking for a simple way to share a database with co-workers and you don’t want them messing around with things… Try renaming the file to ACCDR. This is the equivalent of running the database with the /runtime switch. Basically the ribbon and nav pane get turned off.

I found a comment on a German blog quoting somebody who was quoting somebody that the runtime would ship “sometime in the March timeframe”. Let’s hope so.

SQL Command Box for Microsoft Access

Among the many things I miss in Access from Foxpro is the ability to enter SQL statements on-the-fly into the command box. So, as a first cut, I created a form with a textbox and a button. The textbox holds the SQL code, and a button that calls a subroutine to stuff the code into a scratch query defined in the .MDB Queries collection.

There are lots of possible refinements, error checking, parsing of other commands, etc, but already I’m taken with this as it eliminates half the clicking around when doing SQL queries. Thanks to Martin Green’s Office tips for most of the code. Here is the VBA code for the command button.

Private Sub cmdExecute_Click()
' The following code processes an on-the-fly SQL command
' entered in the text box. The command requires a "scratch"
' query be included in the database query collection. The code
' takes the SQL string, passes it to the scratch query, and
' then executes it.
' Code cribbed from Martin Green's Office Tips at
' http://www.fontstuff.com

Dim strSQL As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef

strSQL = Trim(Me.txtQuery.value)

'Here is the code which will apply the SQL statement to the query:

Set db = CurrentDb
Set qdf = db.QueryDefs("qryScratch")
qdf.SQL = strSQL
Set qdf = Nothing
Set db = Nothing

DoCmd.OpenQuery "qryScratch"
End Sub

Debugging SIP in Asterisk/Trixbox

Here’s the scenario:

Significant Other calls Mother. Gets a “ring no-answer”…which means of course, that it just rings and rings and rings. The assumption, then, is that Mother is not at home. Or she might be downstairs doing in the laundry room, or something, but she definitely is someplace else.

However, what is happening in reality, is that Mother is on the phone with Sister. So we should be getting a busy signal, but we’re not.

My theory was that since my PSTN connection provider, Voicepulse, couldn’t complete the call, that it just kept things ringing. However, they said that they actually send back a SIP message 486 [Busy here] to Asterisk…and Asterisk should then be dealing with that by changing the ring tone to a busy signal.

So, there is a SIP debug mode within Asterisk, and I’ll set this up by going into the Asterisk Command Line interface. I’ll log into this remotely using Putty using SSH (the secure shell). I’ll also set this up to log everything that appears in the terminal to a file.

So, I place a call to a known busy PSTN line.

But, nowhere in the transcript is there any evidence of a SIP message 486 Busy. So, I placed an outbound call via my ZAP hardware line to the PSTN number for that number. This is the same as calling your own phone number from a conventional phone. In this case, I get an immediate busy signal, as expected. But, looking at the transcript of that call, there is no evidence of a SIP message 486 there either. So now I’m wondering about the phone. In the transcript it shows the following entry:

<-- SIP read from 192.168.0.161:5060:
BYE sip:98639587@192.168.0.180 SIP/2.0
Via: SIP/2.0/UDP 192.168.0.161
From: "Larry Keyes" ;tag=8d48abf7-9a54-5966-3784-6ae80bce9d87
To: ;tag=as5fa85f66
Contact:
Proxy-Authorization: DIGEST username="200", realm="asterisk", algorithm=MD5, uri="sip:98639587@192.168.0.180", nonce="1c3041c5", response="759f615b48878ff3d617936450ae3c8e"
Call-ID: fc430bce-606a-00c1-18aa-c326d5af2e1b@192.168.0.161
CSeq: 45540 BYE
User-Agent: Grandstream SIP UA 1.0.4.17
Max-Forwards: 70
Allow: INVITE, ACK, CANCEL, BYE, NOTIFY, REFER, OPTIONS, INFO, SUBSCRIBE
Content-Length: 0

I’m wondering about the Allow:. Does this mean that the phone doesn’t accept any SIP message except those that are offered by the phone?

Now, my own opinion is that we can indeed live with this, just like we can live without E911 calling and all that. Still, it is just one more damn thing that is different between my VoIP implementation and the “real” PSTN. But, there is a safety issue there…if we can’t reliably determine when the phone is off the via a VoIP call, then we may want to place a PSTN call to verify. Which seems to sort of defeat the purpose.

We could solve this by just getting her an answering machine.

Here is another viewpoint about the anomolies of VoIP. Men are from VoIP and Women from PSTN.

My wife has been using Vonage for the past 3 years with me and she complains at every little Vonage hiccup, every little “fast busy” when dialing, every little Internet outage that brings down the phone line. She used to complain about the sound quality on the VoIP connection all the time, but she has gotten better. Or perhaps she’s resigned to the fact that I’m never going back to PSTN.

Me? I’m like “Hey, sounds great to me. I never have any problems when I’m making a call using Vonage. Sure, when the power goes off or the Internet connection dies, we lose our phone, but hey, we’re saving a ton of money each month. And it’s a cool technology to boot. Plus I write about this stuff all day long, so I should practice what I preach.”

I don’t think she bought it.

TrixBox 2.0 updates

And while we’re on the subject of VoIP… TrixBox now has a 2.0 version available. I downloaded and installed this on another machine, before realizing that, like Dorthy, all I had to do was click the heels of my ruby slippers together three times, and I could upgrade my current Trixbox to version 2.0. And indeed that is all it takes; running the upgrade script, letting it chug for an hour or so, and then going in on the FreePBX interface and running the module upgrade procedure for FreePBX.

2.0 includes new versions of almost everything, including a release candidate 1 of FreePBX 2.2.0. So, now I have TrixBox 2.0 with FreePBX 2.2.0 which manages Asterisk 1.2.13.

Over at the Digium they are touting the Asterisk appliance, which is intended as a Trixbox competitor. This comes with Asterisk 1.4.0, beta a notch more recent than the version provided with TrixBox. I flirted with this; installed it actually, but then ran into some problems. I was intrigued however, to see that there was some additonal support for H.323 video…does this means that Asterisk will someday compete as a videoconferencing multi-point control unit (MCU)?

While the new interfaces are fine, the great news from my perspective was that the update blew away my IAX2 trunk configurations for VoicePulse. It left, however the VoicePulse SIP trunks, and I’ve been using them for two days without difficulty, not a single reboot or missed call. Call quality seems to be consistantly good. If this continues, I may actually get confident enough to apply for a phone number for the VoicePulse trunks and use them for inbound calls as well as outbound.

I’ve been using VoicePulse for calls now for a little over a month. Calls within the U.S. have a rate of up to two cents per minute. Calls to Germany were charged 2.7 cents per minute. The one outlier was a charge of 75 cents per minute to directory assistance in the U.S. What a rip!

Network Troubleshooting Tools


Wireshark is the new name for Ethereal. It is a GUI packet sniffing program which watches your network traffic and reports on what’s going on at the network level. Ethereal has been around for years, but it is an ongoing project that just seems to be getting better all the time. Among other things, it will discriminate and display packets that are typical VoIP packets, that is SIP (Session Initiation Protocol), and IAX2 (InterAsterisk Exchange).

Set up a Wireshark capture and you are bound to find a ton of stuff that may be irrelevant. You’ll see requests for web pages, OutLook going out and checking for new mail, DNS requests (where is mxdesign.net?) and ARP broadcasts (who is 192.168.0.9?). So, one of the first things to consider is filtering the captured packets as they are being examined. This is done by using a capture “language” to create filters that are compatible with a predecessor program called tcpdump. Mike Horn has written a tutorial on these, which includes a basic set of capture filters.

I was puzzled why I couldn’t see traffic from my Asterisk/Trixbox computer on my desktop workstation. It turns out they were connected with a network switch which isolates traffic from individual devices. This makes network sniffing more difficult than in the old days when most LAN segments were connected via hubs. I ended up rummaging in the garage and found an older NetGear 16 port hub which I used to connect both stations, and Voila now I could see everything, the SIP phone calling the Trixbox, and the Trixbox calling out on the internet to Voicepulse. The Voicepulse tech support people want to see a capture file of just IAX2 packets, which should show (or not show) why my Trixbox occasionally loses the registration to the Voicepulse server. We’ll see.

Windows Troubleshooting Tools

Readers may be familiar with Mark Russinovich of SysInternals. They make cool tools for troubleshooting Windows networking problems. Their company was recently bought by Microsoft, and the SysInternals Toolkit has been made available in a complete rolled-up zip file. This includes all the current tools, and the relevant help files. Several are these are Windows versions of Unix tools.

Look and Stuff in Microsoft Access


I think any database analyst or programmer has heard this common client request when doing mailing lists:

I want to be able to look up the organization data if the person is a member of an organization, and copy all of the relevant information into my mailing list. But I also want to be able to type in (or over) any existing organization information. And if the person isn’t a member of an organization, or if the organization doesn’t exist in the table, then I want to be able to add it right then…

So we stand in really odd positions to make this work, trying to relate the person table to an organization table and put together some kind of logic that works like QuickBooks (which no doubt cost $2.45 million to develop…)

This recently came up again, and because it turns out that the Organzation table gets replaced periodically, I gave up on the idea of relating the two tables, and just decided to duplicate the organization’s fields in the person table. So shoot me.

If you press the “Look and Stuff” button, it brings up a subform which contains a single control, a combo-box pick list which picks an organization from the organization table.

The code behind this takes the field contents for the chosen Organization record and copies them to equivalent fields in the person table. The code:


Private Sub cmdFill_Click()
On Error GoTo Err_cmdFill_Click
Dim dbs As DAO.Database
Dim strSQL As String
Dim rstTemp As Recordset
Set dbs = CurrentDb

'Only do the following if the user has chosen a company
If Me.cboCompany.Value > 0 Then
strSQL = _
"Select * FROM Company Where Company_id =" & Me.cboCompany.Value
Set rstTemp = _
CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

'Stuff the results into the Mailing List Form
Form_Maillist.txtOrganization = rstTemp![Organization]
Form_Maillist.txtAddress = rstTemp![Address]
Form_Maillist.txtCity = rstTemp![City]
Form_Maillist.txtZip = rstTemp![Zip]
Form_Maillist.txtState = rstTemp![State]

End If
'Close the chooser form
DoCmd.Close
Exit_cmdFill_Click:
Exit Sub
Err_cmdFill_Click:
MsgBox Err.Description
Resume Exit_cmdFill_Click
End Sub

Tech Friday: Asterisk/Trixbox quirks

A couple of interesting quirks have come up after about ten days of using Asterisk/Trixbox as our “home office” phone system.

1. The Grandstream Budgetone 101 and 102 SIP phones that we are using are not (apparently) FCC Type-B accepted. That means that they are not shielded to prevent television or other interference. So the phones make a hash of Channels 5 and 33 on our non-cable television….thereby rendering these phones essentially useless in a home environment. To be fair, they are a couple years old….maybe the new Grandstream phones are better shielded? So, the way we’re coping is to plug in the phone when we want to make a call. Saves power, that way, of course.

2. As described earlier, I’m using VoicePulse as our outbound call service, and it seems to work very well indeed. However, the IAX2 channels sometimes become unregistered with VP during the course of a day. When this happens, if you place a call via the VoicePulse trunk…you get a ring-no-answer…that sounds just like a conventional ring-no-answer..i.e. you can’t distinguish whether this is a problem with the VoicePulse registration, or if in fact the person really isn’t at home. As a precaution, in the morning, I’ve been making check calls to my cell phone….if the cell phone rings, then I know my outbound trunk is working. At the Asterisk command line, if I type IAX2 show channels, it should show two registered VoicePulse channels. Even if channel voicepulse1 is unregistered, I would have hoped that channel 2 would take the call, but there doesn’t appear any to be any logic that can deal with this issue. What I’d really like to see is some kind of real-time indication that the channels are not working, and/or some kind of fall-back or re-registering function.

3. I’ll be checking the power consumption of all the components, but I was pleased to see that the idle power consumption of the Dell server that I’m using goes at about 44 watts. This seems pretty reasonable for a server.

Online Diagnostic Tools

Time to mention some online, free diagnostic tools:

www.dnsstuff.com
Are people having trouble finding your web site, or is expected eMail not getting through? Use this site to verify your DNS mappings and Mail Server mappings.

www.broadbandreports.com
Are you getting all the bandwidth that you are paying for? Find out by running the speed tests from this site, and find out how your site is compared to others in your region, or from the same provider.
My current results:

Hmmmmm…

Shields Up!
Go to this site to see your current “public” IP address. The site will also check for open or closed ports on your firewall.

All three sites have tons of information explaining how the tests work, and the underlying internet plumbing.