June 28, 2005
pc world
Experts Exchange Community News
You are receiving this because you are an Experts-Exchange.com member who has opted-in to receive newsletters.
What's New at Experts Exchange
Mapping to UPS Online Worldship

turn123 is the Page Editor for Applications, Productivity Applications, CRM and ERP. He loves to tinker with stuff.

This article assumes you know how to get the data out of QuickBooks. Archived copies of relevant newsletter articles can be found in the June 14 and May 31 newsletters.

Some rough Perl code for putting the information into this sample database (zipped) can be found in a special supplementary thread (Q_21155587) in order to keep this short.

The first step is to connect UPS Online WorldShip to the database. Open UPS Online WorldShip --> pull down the "UPS Online Connect" menu --> click "Connection Assistant" --> choose "Create a new map for Import" --> click "Next" --> Under "Import Data Types" choose "Shipment" --> click "Next" --> choose the "By File" radio button --> Under "Currently Selected File" Enter the full path to the database or browse to it (for me it's C:\Documents and Settings\user\Desktop\newsletter\shipping_stuff.mdb --> "Under ODBC Drivers" Choose "Microsoft Access Driver (*.mdb)" --> Under "Data Source Name (DSN):" enter "EE_Newsletter" --> click "Next" --> choose the "New Map" radio button --> under "New Map Name" put "EE_Newsleter" --> click Next --> click "Finish" --> click "OK".

Now that we have finished connecting we need to map some fields. On the left there is a list of all the tables and fields in our database. On the right are all the tables and fields we can import into for the UPS database.

Choose ID on the left then on the right click the dropdown box and choose "Ship To" then click on the field "Customer ID". Then click Connect followed by clicking the "Primary Table" and "Define Key" buttons. Follow the above procedure for each of the following to connect everything up for this simple sample.

Ship_Company    Company or Name
Ship_Attn       Attention
Ship_Addr1      Street Address
Ship_Addr2      Room/Floor/Address 2
Ship_City       City
Ship_State      State/Prov
Ship_Zipcode    Postal/ZIP Code
Ship_Country    Country

There are three very important fields that I didn't map yet so under "WorldShip Fields" choose "Package" and map:

ID              Reference 1
PO_Number       Reference 2

This makes sure that the invoice number and the customer's PO number print on the label. To set up the third very important field choose "Shipment Information" and map "VIA" to "Service Type". Now click "Edit" then click "Translations" and choose "Service". This is where you can force your terms to match up with the correct UPS term.

When you're done click "OK" "OK" "OK". It will ask if you want to do a batch import and I personally would choose no as I prefer the keyed import. To finish up this long article, let's pull down the "UPS Online Connect" menu and choose "Keyed Import" --> More --> Choose "EE_Newsletter" --> push the Import button --> and enter an invoice number followed by enter to test. It should be pulling the data from QuickBooks now. Schedule your program to pull the data at regular intervals and you should be good to go until folks start asking you to start customizing...

The "other guys" are better

MHenry, the Page Editor for the Macromedia applications topic areas, does web development for one of California's leading network security companies.

Okay, I'm a techie guy. I like to play with new things especially if they are shiny or make noises. So when I kept reading about Firefox, I had to take a look. I'm not normally an early adapter. I much prefer that software has all the kinks worked out before giving it a shot. But anytime I can get something free that (supposedly) works better than a Microsoft product, I'm all in. Hmmm, guess I've been watching too many of those celebrity Texas Hold 'em shows.

I've been using Firefox for several months now. I've sampled the exciting new features like tabbed browsing, extensions and mouse gestures. I've even installed two updates. And through it all, I've come to a conclusion: I'm not all that impressed. In fact, I'm totally under-whelmed.

I found Firefox's tabbed browsing to be a rather un-impressive benefit. So I get to click at a tab toward the top of a window instead of clicking on the task bar - big fat, hairy deal. After several weeks of becoming accustomed to using the tabs, I was only closing the browser thinking it was a tab three to four times a day. My efficiency was soaring!

And there were other frustrations: Mouse gestures may help improve workflow, but in my case it seemed that for every gain, there was a loss as well. I found a handy feature involving a double-click of the mouse that I was quite happy with until I tried double-clicking to select a word. Having a low threshold for pain and frustration I did what I usually do when faced with obstacles: I quit trying.

Believe it or not, it's not all negative. My experience with Firefox gave me enough courage to check out Opera again. I had used Opera's browser several years ago before taking a job with a company that only allowed Internet Explorer. Firefox had piqued my curiosity so I took the plunge and downloaded Opera.

Wow. And wow again.

Now this is tabbed browsing. I'm not quite sure what makes it feel so different; maybe my foray into Firefox had equipped me to use tabs but I don't think so. Everything about Opera is sexy, from the default skin, XP style graphics and the cool navigation icons, to the neat little arrow on the left to show/hide the bookmarks panel. I love this browser. My favorite feature is the option to have Opera open to where you were when you closed it. And I mean exactly where you left off. If you had three tabs open when you closed it, that's what you'll see the next time you open it. So if you do accidentally close this browser, you're right back where you wanted to be.

I'm convinced that Opera actually took the time to develop an interface that is intuitive enough that you don't get lost in the tabs. Heck, they may have even done a focus group or something! It's apparent that this is what Firefox was trying to do but missed by a mile.

But there are frustrations here as well. When downloading files, Opera pops up a tab that shows download progress which doesn't close automatically. It also occasionally leaves an orphan tab in addition to the Transfers tab. When that happens, you have to close two tabs instead of just one. I seem to recall the orphan window occurred in Firefox as well, but I couldn't get it to happen when I tested it for this article.

The main frustration with Opera lies in the handling of email. Opera is apparently really pushing its email service and has incorporated a goodly portion into their browser. If you haven't set up an email account in Opera, you can't even click on an email link and send a message. There's probably a work-around that I'm missing, but why wouldn't it be in the Email FAQ? Or in the preferences? This alone is enough to make me stop using Opera. But I haven't yet. I'm still liking it despite the aggravation and I'll probably keep using it. I may even post a question and let one of you Experts show me how to use Outlook instead of Opera.

Or, maybe I'll go back to Internet Explorer. Wow, is that thought depressing!

Tip from the Page Editors: Pointer questions

What do you do if you're not sure where to ask your question? Suppose you have an ASP page that has some javscript and you're trying to show a table of information that's contained in a database?

The proper thing to do is to pick one Topic Area to ask your question in. Then, if you want Experts from the other TAs to look at your question, use what are called "pointer" questions -- they point the Experts to your main question.

First, ask your question and assign a point value to it -- let's say 300 points -- and copy the URL. Then go to the second TA, and ask your pointer question. For the title, put something like

500 points - ASP and database

In the body of the question, post the URL, and make the question worth 20 points (the minimum). Repeat the process in two or three TAs, but do it only in those TAs that are related to your question.

Once you're received your answer, you can go back to the pointer questions. If there are no comments other than your own (or those of a Moderator, Administrator or Page Editor), you can delete them yourself. Just click on the Edit button to the right side of the browser window, and change the Type from Open to Deleted. Your 20 points will be automatically refunded to you.

The Wireless Revolution, Part 2

In our last issue, stone5150, the Page Editor for the Wireless topic areas, gives some tips on wireless networking. That prompted a response from Robert Chapin and his colleague, David Runyon.

Client-Side Concerns, by Robert Chapin

Robert Chapin is President of Chapin Information Services, a Michigan IT business recently involved in security defect detection at Yahoo Music and catastrophe recovery in Florida. He also holds a Master certificate in MS Access.

These are truly perilous times for technology consumers. Just today I helped a neighbor recover from acute malware infestation of his computer. I did this as a favor because he was helpless to fix it himself.

As threats from the public Internet increase and the cost of a new computer falls, end users are coming close to a "totaled" system every time security is breached. I see a point in the near future where data security and recovery alone cost more than replacing the failed hardware itself.

Proactive security is the only hedge against reactive security. A couple years ago, this meant: #1 don't trust software from strangers and #2 don't trust public networks. However, recent blunders by major technology companies have turned the security world on its head. Proactive security now means: #1 don't trust your own operating system and #2 don't trust your own network.

The biggest concern with wireless networking trust is this: Client computers do not authenticate the access point they are connected to. That is to say, nothing can prevent someone from impersonating your home wireless router, like cloning a cell phone. If you boot up a laptop in your bedroom and surf to your bank's website, how do you know you are connected to the Internet through your own wireless router? In fact, with current technologies, you don't know this for sure.

Wireless security must begin at the client. A firewall needs to be installed and enabled on every computer that uses a wireless networking adapter. This firewall protects the operating system from many threats on the public Internet, as well as many threats from the cloning attack on an access point.

End-to-end encryption is another valuable component of airwave security. Growth of hot spot Internet sites and the emergence of BPL is cause to rethink the treatment of unsecured access points as routine onramps. Think about this the next time you look at your checking account balance on your computer. If the bank's website uses SSL, then the networking equipment is not even involved in the security process. The network is subjugated to that process, and that is ideal because SSL is widely accepted for this purpose.

A solution even more robust would involve a secure "tunnel" between the client and a remote router, called a VPN server. This can be convenient for accessing a home network remotely, but it can also add headaches to the network configuration.

The wireless router setup is also important, and it is much more involved than stone5150 would have us believe. First, I must point out the default frequency channel selected by the router absolutely matters. Depending on how many access points your neighbors have set up, you could encounter a large amount of interference from their nearby signals, decreasing the speed at which you can transfer files. Since channels 6 and 7, for example, are not distinct frequencies like on a radio, they can interfere with each other. Each channel bleeds over to 4 channels above and below, so I recommended using this channel chart as a reference.

Second, the WEP security scheme selected for The Wireless Revolution was not a good choice for most scenarios. It offers little more than obfuscation of the raw data being transmitted. On that note, my colleague, David Runion, has more to say about the pros and cons of various wireless security schemes for access points.

Access Point Concerns, by David Runion

David Runion lectures at venues sponsored by CompuMaster, and specializes in OS and wireless security. His clients include public and government security firms. He also holds an MCSE on Windows 2000.

WEP is a fraud. The acronym stands for Wired Equivalent Privacy, which is an outright lie no matter how secure it is, but the technology is so flawed that its continued use constitutes a fraud upon the unsuspecting public. All traffic going across the wireless link is encrypted, but flaws have been discovered in its implementation. While the vulnerability started as a pinprick, time has widened it to what is now a sizeable hole.

If you use WEP, I can decrypt your access key in 10-20 minutes. After decrypting that key, I can capture the traffic generated by your computer. With that traffic, I can find passwords, credit card data, and personal information. I can see everything you send or receive, just the same as you can. But it gets worse.

Not only can I see what traffic is sent over that wireless link, I can affect the traffic in any way I desire. I can re-route your secure connections through my computer, secretly decrypting traffic between you and the computer you are supposedly communicating with securely. The time between finding a WEP-encrypted router and getting complete access to everything on your computer can be less than an hour. It's bad.

Some people suggest MAC filtering. Your router probably supports this. Essentially, your laptop's wireless network card has a unique ID. Your router can be configured to only accept connections from that unique ID. But this technique is also flawed. Anyone who can break your WEP key can also change their MAC address and clone your computer, just as they can clone your router.

Some suggest turning off the SSID broadcast, commonly known as the beacon. This makes your router "stealth". But while this may cut down on your likeliness of being casually detected, there are 3 other ways that your router can be discovered remotely.

There is a solution for router security, and its name is Wireless Protected Access (WPA). WPA uses a different kind of encryption key. It isn't perfect, but it is much better than WEP. WPA usually works by encrypting your communications with a "pre-shared key". This is a key that both the router and the wireless client know ahead of time, like a password. Anyone who knows this password can decipher the data between the client and the router.

It is important to choose a good password. There are only two things that determine whether the password is good or bad. First is the length of the password, which is recommended to be a minimum of 20 characters. Second, the password should not be a dictionary word. If your password is a sentence over 20 characters, and not one that would be easily guessed, then you can be reasonably assured that your key will be safe.

If you are a wireless network user, you must configure and use WPA. If your router doesn't support it, check the manufacturer's website for a firmware upgrade, or turn off the wireless link. Routers can almost always be upgraded to support WPA, and if the manufacturer is reputable they should provide it for their customers. If your router cannot be upgraded to support WPA, you should buy a new router. Many can be had for $40 or less these days. I suggest the Linksys WRT54G. Similarly, if your wireless adapter does not support WPA, then you should consider a new one of those as well.

The Apprentice and The Secret of the Access Barcode

SidFishes is one of EE's best Access Experts; like many of us, he says "Many times I don't know the -right- way to do things... but I usually find a way...". He has some tips, tricks, samples, and other entertaining stuff on his website.

"Master Sid, what control or code do I use in VBA to be able to store barcodes in my application?"

As always, I reply, "You don't."

"Huh? I've seen lots of Access applications that use barcodes. You must be wrong."

"Wrong you say? Come with me my Apprentice, and I'll show you the key to the dark and mysterious world of the barcode. And I might even let you in on the secret handshake."

Some History

The barcode is an example of an invention that was driven by need. In the 1960s, the small inventory corner store was being shoved to the sidelines to make room for the "supermarket", with its thousands of SKUs (Stock Keeping Units). Employees who knew every product in the store found themselves looking at hundreds of feet of shelves. Checkout staff had no way to remember what all those items were, let alone their prices. Looking up SKUs or product names in a book was simply too slow. Consumers were getting cranky.

In 1966, the National Association of Food Chains (NAFC) sent out an SOS to industry: "Give us a system to speed up the checkout progress and help us keep track of our inventory." RCA installed the first system in a Kroger's store in Cincinnati in 1967. The system worked but it required store employees to manually apply codes specific to that system to each product. What was really needed was an industry standard for manufacturers and retailers.

This standard was developed by George Laurer at IBM in 1973 and is known as the UPC or Uniform Product Code. You may also see it referred to as the Universal Product Code, but the proper designation is Uniform Product Code. George Laurer calls it that and he should know. If you're interested, the very first product scanned using a UPC symbol was a pack of Juicy Fruit gum. It's now in the Smithsonian. There are many other types of barcodes, but the UPC is the one that is most familiar and for our purposes, the form of the barcode is not important. They all "operate" in the same manner.

By the way, if you call it a UPC Code, you're really calling it a Uniform Product Code Code. And that would be silly. Call it a UPC Symbol. Thank you.

The Secret Handshake

The ubiquitous barcode can be a bit hard to use in an application especially if one doesn't understand exactly what is going on when we scan or print. You may ask, "Do I need an OCX or DLL to make Access play nice with barcodes?" The answer is no. The Secret is this: You don't need any special code at all to read or print barcodes. You don't need to store a bar code object in your database. There are only 2 things you need: A Barcode Font and a Barcode Scanner.

Wait! It can't be that simple can't it? Well, like any code development, it's not that simple. But the basic concept is.

Input and the Scanner

Understanding just what the scanner is doing is the key to this whole exercise. The great thing about the scanner is that it is doing most of your work for you. Most scanners are installed between the keyboard and the keyboard port. When you scan an item, the scanner reads the code, translates it into a series of numbers and passes them to the keyboard port. So a barcode like this

| || ||| || ||||

is actually passed to the computer "as if somebody was typing it" like 245631. If you have a scanner you can test this. Open Notepad and scan something. You'll see numbers appear as if by magic. Ooooh! This bit of information is crucial to development of an application. To scan a product and bring up its information or price, all we need to do is add an AfterUpdate event to a text field.

Private Sub ProductID_AfterUpdate()
MsgBox Me!ProductID
'add code to either run a query or use DAO/ADO to Insert/Update tables or whatever'
End sub

This is also important when we want to store the "barcode" in a database. Since we know that the scanner is returning numbers, all we need to do to store the barcode information into the database is to store the numbers. No messing around with BLOBS or OLE Objects. Just a simple number field will do it. Note: some barcode scanners may return control or delimiter characters like *245631* so you may need to handle this before inserting into a table. You could either strip the control character using REPLACE or store the information as a text field.

Output and the Font

Since we now know how to get information into a database, we now need to get that information out again. Again, this is a pretty simple process. The main ingredient is a barcode font. There are many out there, and the ones you need are determined by your application. If you are creating a product database, you will likely need to use UPC-A. If you are using barcodes to track paper or process you can probably use something like 3of9 (no, that's not Jeri Ryan's ugly sister). These fonts are available from different sources. Some printers, especially POS (Point of Sale) printers come with printer barcode fonts. There are also many types available for free or purchase on the Internet.

Once we've installed the font, all we need to do is create a report that uses the font on the field where we want to see the barcode. Again here, you may need to deal with control characters. Some scanners require them to properly scan a code. Your control source might need to look something like

= "*" & ProductID & "*"

Your scanner manual will tell you whether this is required.

So remember, Barcodes are basically nothing more than graphical representations of numbers. And since we've hardware (the scanner) and software (windows fonts) doing most of the work for us, all we really have to program for is dealing with a string of numbers. And that's not all that hard now, is it?

"My Apprentice, if you can scan the barcode in my hand, store it in a database and print it out to a report, it will be time for you to leave."

An afterthought

I had initially written some clunky code for this last bit (hey... it worked but pretty it wasn't) Some barcode formats require calculating a check digit to ensure their accuracy. Fortunately, EE has many great Experts and nico5038 was kind enough to modify it to be better-than-clunky code. This bit of code generates UPC-A check digits.

Function fncGenerateCheckDigit(strUPC As String) As Integer

Dim x As Integer
Dim y As Integer
Dim z As Integer
Dim intI As Integer

If Len(strUPC) < 11 Then
MsgBox "Error, UPC code not 11 characters"
Exit Function

End If

For intI = 1 To 11 Step 2
x = x + CInt(Mid(strUPC, intI, 1))

x = x * 3

For intI = 2 To 10 Step 2
y = y + CInt(Mid(strUPC, intI, 1))

z = x + y

fncGenerateCheckDigit = Right(10 - (z Mod 10), 1)

End Function

More News and Notes
Nata's Corner: And I thought it was just shorthand

woman in specticals If there's one thing I have a hard time with, it's understanding why people insist on using "ur" for "you're" or "your", and other text-messaging type shorthand when they're writing an email, or posting in a question at EE.

My mother-in-law sent me a note a couple of weeks about a study that concluded that constant text messaging reduces mental capacity by ten IQ points. And all this time, I thought it was just either laziness or bad spelling skills, but now it turns out that they might not be able to help themselves.

I think I'm going to go back to using my checkbook -- or maybe even cash. MasterCard reported that information on 40 million accounts had been stolen from a payment processing company. Since the processing company wasn't supposed to have kept the information but did, I think we're going to be seeing a lot more in the Legal Notices section of the newsletter pretty soon. Late reports suggest that the breach may have occured earlier than previously thought -- as far back as November 2004.

I got an email the other day from an old friend, who told me her Yahoo Instant Messenger account had been stolen. No reflection on her, but I wouldn't be surprised if she'd been caught in a phishing web. I know I've been harping on it for a while, but I can't believe that people get sucked into those scams. You might want to take this quiz and let us know how you did.

Click here to from the newsletter.
The Premium Services include a number of features not available to "limited" members. Among them:
> Unlimited question points
> VIP Search
> Bookmarks
> Quick Links
> Collapsible menu
> No ads
You can purchase Premium Services on a month-to-month, semi-annual or annual basis, and take full advantage of all that Experts Exchange has to offer!
Inside the numbers
ameba, one of EE's prominent Experts, provides us with a list of newly earned Certificates. His list of all of the Certified Experts is located at his site. The list below covers the period from June 12 through June 26.
Expert Certified in Topic Area
GRayL kirenievs pootle_flump TriggerHappy fanpages BevinManian Sudonim Preece rettiseert jitganguly hongjun flow79 bvinson rohanbairat3 ispaleny amit_g BlackTigerX pcsentinel spiderfix sramesh2k andymsmith18 Longbow Bartender_1 zzynx doronb caball88 rindi Jonybrv Sage Master Master Master Master Master Master Master Master Sage Sage Master Master Master Wizard Wizard Guru Master Master Genius Guru Master Master Genius Guru Master Master Master MS Access MS Access MS Access MS Access MS Access MS Access MS Access Visual Basic Visual Basic ASP ASP ASP ASP ASP Microsoft SQL JavaScript Delphi Delphi Windows 2000 Windows XP Windows XP Windows XP Windows XP Java Java .NET Hardware Hardware
Expert Certified in Topic Area
JohnnyCanuck mnasman jj819430 Rejojohny amit_g saleek Ramuncikas flyguybob sr_millar TheLearnedOne iboutchkine PaulHews AlexCode Torrwin schwertner slightwv DrSQL InteractiveMind Arthur_Wood TimCottee nicholassolutions NJComputerNetworks jmissild jaime_olivares sajuks elhy adamdrayer jrb1 Master Guru Master Wizard Guru Master Master Sage Master Sage Sage Master Master Master Genius Wizard Guru Guru Guru Master Master Guru Master Wizard Master Master Guru Guru Hardware C# C# ASP.NET ASP.NET ASP.NET ASP.NET Exchange_Server Exchange_Server VB.NET VB.NET VB.NET VB.NET VB.NET Oracle Oracle Oracle Programming Programming Programming PHP Win Server 2003 Win Server 2003 MFC Outlook Flash Microsoft Network Databases
Expert Certified in Topic Area
Lowfatspread rafrancisco shijusn ameba Mikal613 idmisk leew mcv22 bdreed35 GJParker Diablo84 akshah123 bloodredsun ryancys Daydreams Daydreams humeniuk Havin_it wesly_chen AlexFM sgstuart -Leo- whtrbt7 yukele dedy_djajapermana bochgoch Guru Master Master Master Master Master Master Master Sage Wizard Master Master Wizard Master Master Master Guru Master Guru Guru Master Master Master Master Master Master Databases Databases VB Controls VB Controls VB Controls Linux Linux Applications Crystal Reports Crystal Reports HTML PHP and Databases JSP VB DB CSS Lounge IIS DreamWeaver Linux Setup Visual C++.NET Macintosh Handhelds/Wireless Handhelds/Wireless MultiMedia Apps AS/400 Oracle iAS
1491 experts have 2374 certifications: Genius:45 Sage:117 Wizard:138 Guru:424 Master:1650
Copyright ? 2005. All rights reserved.