“In the cloud” ~ Using Microsoft Access to query and update Web sites

By Garry Robinson
Hello Microsoft Access developers and power users,

Garry Robinson and in his first article for Database Journal. In case you haven't read his articles before, he used to write for a magazine called Smart Access with Danny Lesandrini, your Database Journal writer since 2004. He has also written Microsoft Access articles for MSDN and He wrote a book on protecting your Access database in 2003. Anyway the main thing you can be assured about is that He is passionate about Access and he hopes he can introduce you to some successful software over the next few months.
In this article, He is going to describe how you can use an Access Database and VBA to manipulate a Web 2 application called Highrise, a customer relationship management (CRM) tool from the highly successful Web2 company, 37Signals. Why pick this online product? The main reason is that I love using it, it has a well-written Application Programming Interface (API) and it is free for your first two hundred contacts. Why might this be relevant and interesting to you, the reader? Because you will be performing these tasks on a database that is hidden behind the security of a website, something that has always been beyond the abilities of Access.
The reason he love Highrise, is that it orders my people specific tasks (e-mailing/calling) into a wonderful list of fuzzy dates comprising today, tomorrow, this week, next week, and later and fuses that with specific dates and times like 12-june-08 4pm (see the sample task list in figure 1). Highrise also keeps track of names, addresses, correspondence relating to people and companies in quite a versatile environment. Anyway, the functionality of the website is not important, what is important is that you can interact with the data in the Highrise website through the API by posting and retrieving XML. This allows you to extend the feature list of the online application and it allows you to merge information on a website with other applications on your desktop. More than likely this is going to be a Microsoft Access database if you are reading this article.

Note: Other prominent websites that have online API’s include Amazon, FaceBook, SalesForce and Microsoft has a beta release of SQL Server aimed fair and square at this market.
Highrise Website And Download Samples
The sample that He set up (shown in figure 2) will get a list of people, will add a person and delete a person. You will find VBA code under the buttons in the download Access database.

To make the sample work, you need to set up your own Highrise website. Do this by heading to http://highrisehq.com/ and clicking on the signup for free link. Work through the signup process until you have a Highrise sub-domain URL of your own plus a username and password. Once you're up and running, add a few people into Highrise as you will need this for your sample.
To use the technology in this sample, you need a copy of a Microsoft library called MSXML. To download MSXML, head to http://www.microsoft.com/downloads/ and search for MSXML6. Now you should be ready to run the samples.
Get a List of People from the Highrise Website into Your Access Application
Let's look at the code that you need to retrieve a list of people. I will do this without including any of the necessary error checking because I want you to see that the code is not all that complex.
Listing 1: Code to retrieve a list of people (without error checking) Private Sub cmdGetPeople_Click()
' Get a list of people from your Highrise account
'Head to (List All) at the following address for full XML
'http://developer.37signals.com/highrise/people.shtml

Dim objSvrHTTP As ServerXMLHTTP
Dim objXML As DOMDocument


Set objSvrHTTP = New ServerXMLHTTP
objSvrHTTP.Open "GET", txtURL & "/people.xml", False, _
CStr(txtUserName), CStr(txtPassword)
objSvrHTTP.setRequestHeader "Accept", "application/xml"
objSvrHTTP.setRequestHeader "Content-Type", "application/xml"
objSvrHTTP.send


'Load Highrise response into an XML document object and
'save it to the harddrive
Set objXML = New DOMDocument
objXML.LoadXML objSvrHTTP.responseText
objXML.Save "c:\highrisePeople.xml"
'If you want better structured XML, use this code
txtXML = objXML.xml
'even though this code is quicker
'txtXML = objSvrHTTP.responseText
Exit_Procedure:
On Error Resume Next
Set objSvrHTTP = Nothing
Set objXML = Nothing
Set objSvrHTTP = Nothing
Exit Sub

End Sub
Let's look at the code in some detail. To start the routine of, you need to declare Dim objSvrHTTP As ServerXMLHTTP
Dim objXML As DOMDocument
ServerXMLHTTP allows you to communicate with the API and DOMDocument gives you a number of code tools to process the XML that is transmitted from the website. Both of these objects come from a well supported library from Microsoft called MSXML (see notes on installing MSXML).
First you need to set up the request that you send to the website to retrieve a list of people. This is managed through the GET command and is wrapped with the website address, user name and password. To make it it more obvious, here is what the GET command might look like for your Highrise subdomain.objSvrHTTP.Open "GET", "http://joessample.highrisehq.com/people.xml ", False, _
"JoeBloogs", "joesmum"
Send Command and Response text
You retrieve data from a website using the “GET” which returns an XML string in the response text of the ServerXMLHTTP object. Now have a look at the following code snippet to see how we populate a text control with the API's XML data. objSvrHTTP.send
txtXML.value = objSvrHTTP.responseText
Working with the good XML
If all is working well, you will now be in a position where you need to do something with the XML that has been returned using the ServerXMLHTTP Send method. Have a look at the XML displayed in the text box on the right hand side of Figure 2. As you can see it is orderly but unlike a table or a query. To do something with the XML (which is text with tags), you can
Use traditional text manipulation functions such as InStr, Left, Mid and Right
Save the XML to a text file and then (try) load that into an Access database using XML import commands that can be found in the Access menus, macros or VBA.
You can manipulate the XML using the DOMDocument object, a tool that will allow you to do almost anything with an XML file if you can work out how to use it.
In this code snippet, I show you how you can save the XML to a text file and display the XML in a text box. Set objXML = New DOMDocument
objXML.LoadXML objSvrHTTP.responseText
objXML.Save "c:\highrisePeople.xml"

'If you want better structured XML, use this code
txtXML = objXML.xml
Tip: if you want to have easy to read XML, use the load XML method of the DOMDocument.
Error handling
There are two types of errors that you can expect when dealing with a Web2 API, status codes from the API and VBA errors. In the download sample you will find the code to handle the website status codes. Whilst the status codes and what they mean will vary from site to site, generally they will work something like this. objSvrHTTP.send

If objSvrHTTP.status = 200 Then

MsgBox "List of first 50 people has been retrieved "
ElseIf objSvrHTTP.status = 401 Then

MsgBox "Listing failed with error# " & objSvrHTTP.status & _
" Check your username and password"

Else
MsgBox "Listing failed with error# " & objSvrHTTP.status & _
" " & objSvrHTTP.statusText
End If
The second type of error that you need to handle is the VBA errors that are generated in this environment. In the following code listing that you will find at the bottom of the download sample, you'll see how a trap an Internet connect error.Error_Handler:
Const NOINTERNETAVAILABLE = -2147012889

Select Case Err.Number

Case NOINTERNETAVAILABLE

MsgBox "Connection to the internet cannot be made or " & _
"highrise website address is wrong", vbCritical, _
"Listing of People cancelled"

Case Else
MsgBox "Error: " & Err.Number & "; Description: " _
& Err.Description, _
vbCritical, "Problem in subroutine called cmdGetPeople"
Resume Exit_Procedure
Resume
End Select
Tip: If you look carefully at the code, you will see that there are two Resume commands together. If you are in Debug mode, you can drag the yellow arrow to the line with only Resume on it. Now press the F8 key and Debug will return you to the line where the error occurred.
Summary ~ it's time to get your head in The Clouds
A lot of the websites are calling this Web2 technology “In the Cloud” software; whilst the name is not important, getting data from “in the cloud” and into your local Access application database is. At the moment, the main stumbling block for Access programmers is that most of the samples online are written in .NET, Ruby, PHP and other Web languages. But rest assured that this is likely to change pretty fast over the next couple of years as more writers start posting samples for VBA on the web. If you read next months article, I will show you a few more tricks. Thanks for reading my first Database Journal article.
MSXML6 is included as part of the Vista operating system so you can be sure you're hooking up with important technology that won't require any setup work in the future.
This in the cloud stuff is really important. At the moment you need to tackle it through MSXML (which is installed as part of Windows Vista) and easy to install for WinXP (if its not there already).
Conclusion ~ XML and MSXML
You are probably going to throw up your hands and say what does XML have to do with Access. To this I say, with XML and MSXML, you can talk directly through port 80 of your computer to the World Wide Web, thereby bringing online Web2 applications and their data to the richest tool on the planet, Microsoft Access.
So find the Web2application that you like, check that it has an API and start talking to the website using MSXML and Access VBA.
This allows you to have just few tables on the web in an online table, a share point list or a Web to website to allow your customers to enter data from somewhere outside of your local network. This can be grabbed from your all singing and dancing Rich Access databases on the local network, alternatively the Rich db can post to the cloud table. What this doesn’t do is make a your whole Access system net ready because coding is a lot trickier with the cloud systems as they bring data down 50 records at a time in XML packets. Not heaps of record like wonderful Access forms.

The new thing in the old

Ms word 2007

Microsoft Office Word 2007 helps you produce professional-looking documents by providing a comprehensive set of tools for creating and formatting your document in the new Microsoft Office Fluent user interface. Rich review, commenting, and comparison capabilities help you quickly gather and manage feedback from colleagues. Advanced data integration ensures that documents stay connected to important sources of business information.

When should you use the Vlookup function?

When you have a table with data, and you wish to retrieve specific information from it.
For example:You have an Excel table with student names and their grades.You wish that you could somewhere in the sheet type a student name, and immediately retrieve his grade (based on the data in the table).To achieve this, you can use "Vlookup": the function will look for the student’s name in the first column in the table, and will retrieve the information that is next to his name in the second column (which is his grade).
Another example:You have a big table consisting thousands of bank accounts.You wish to retrieve in another worksheet information regarding some specific accounts.To achieve this, you can type these specific account numbers, and put a Vlookup function next each one of them. The function will look for the account numbers in the big table, and retrieve relevant information from it.
The difference between “Exact match” and “Closest match”:
When you use the Vlookup function to retrieve information based on a student name or a bank account number, you cannot allow it to find something close or similar to “Jake”, or close to the account number “3647463”, but rather it has to find them exactly.
But sometimes you have a table that defines ranges, for example:$5,000 – “Small deposit”$20,000 – “Medium deposit”$100,000 – “Big deposit”$500,000 – “Huge deposit”
If you want the Vlookup to find the description for a deposit of $23,000 (which should retrieve “Medium deposit”), you will ask it to find a close match, and it will find $20,000.
This is very useful when dealing with dates. Look at the following table:4/1/2008 – Payment on time.6/1/2008 – Late with payment (small fine).8/1/2008 – Very late with payment (big fine).
If you would like to find what happens with a payment made on 7/14/2008, the function will relate it to the date 6/1/2008 and retrieve us “Late with payment (small fine)”.
Please note – the function will always retrieve the smaller closest match (in case it doesn’t find an exact match).

Microsoft Outlook

About Microsoft Outlook 2002

Microsoft outlook 2002 is the upgrade to Microsoft Outlook 2000 and includes the below new features.
Outlook Hotmail support
Autocomplete addressing
Intuitive management of e-mail, contacts, and appointments
Smart tags in Wordmail
Overall, users who are utilizing Microsoft Outlook 2000 would most likely not find it necessary to upgrade to Microsoft Outlook 2002 unless they believe the above features will be useful.

About Outlook 2000

Microsoft Outlook 2000 is an advanced e-mail software program that allows users to send and receive e-mail. In addition to e-mail, Outlook has a personal calendar and group scheduling, personal contacts, personal tasks and the ability to collaborate and schedule with other users.

About Outlook Express
Outlook express is a slimmed down version of the Microsoft Outlook software family. Outlook express is included with Microsoft Internet Explorer and also with Microsoft Windows 98 and above.

Introduction to Data Fields

A database from scratch

create a database using one of the available templates. This approach provides you with readily made objects you can directly start using. Another technique consists of creating an empty database that has no built-in object. Starting a database from scratch allows you to create and add its different objects when necessary. The main advantage of this approach is that you will exercise as much control as possible on your database because you will be creating all of your objects. The disadvantage is that you will miss that primary layout that the templates offer. Creating a database from scratch simply means starting from a blank database and adding the different components. Of course, after learning how to perform some changes, you will be able to modify some aspects of a database, whether created from a template or started from scratch.

To create a database from scratch, if you are just launching Microsoft Access, in the left section, you can click Featuring. Then, in the main section in the middle, click Blank Database, give a name in the File Name text box, and click Create.

Introduction to Ms Access

Microsoft Access is a computer application used to create and manage computer-based databases on desktop computers and/or on connected computers (a network). Microsoft Access can be used for personal information management (PIM), in a small business to organize and manage data, or in an enterprise to communicate with servers.
Like any other computer application, in order to use Microsoft Access, you must first install it. After installing Microsoft Access, then you can open it. There are various ways you can open Microsoft Access. It gets launched like the usual products you have probably been using. As such, to start this program, you could click Start -> (All) Programs -> Microsoft Office -> Microsoft Office Access 2007. You can also launch it from Windows Explorer or My Computer. To do this, locate its shortcut in Windows Explorer or My Computer. By default, Microsoft Access 2007 is located in C:\Program Files\Microsoft Office\Office12 and its shortcut is called MSACCESS.EXE. Once you have located it, you can then double-click it.

Power Point 2002 XP

Inserting a Chart
PowerPoint allows you to insert charts into your slide presentation to display different types of information to your audience.
To Insert a Chart:
Insert a new slide with a title and a chart icon.
When the slide appears, click the Insert Chart icon

A chart appears with a data sheet and sample data.

replace the sample data in the data sheet with actual data that you want to present. The Y axis is for values or numbers. For example, number of hours worked or amount of money earned. The X axis is the label for the information. It now reads East, West, North.
You can delete some information in columns or rows of the sheet. Right click on the row or column and choose Cut, Delete or Clear Contents.
NOTE: You can expand the chart columns to fit your data or titles. Place your mouse pointer over the end of the column in the gray heading. A black cross with double arrows appears. Right click and drag the columns to the size you want.
To format column width, click on FormatColumn width.
Notice that as you enter the new data and titles etc., the chart on the slide changes to show this new information.

Have your DOC file got converted to DOCX ? solution is here!!

Today one of my co-ordinator request me to check a DOC file on my network which got some problem and thus unable to open in its original form. And that file had some important audit report of around 14 pages the audit officer was frustrated and asked me to retreive the data by any method and let him take cool breath. The moment I as usual got on to google and requested to find out the solution for this. So ultimately as usual i got it. So now its here for your reference and help. New Office & Word application from Microsoft has newer version of format called Docx, which replaces doc format as peviously used. Files with docx format does not open in older office application and only works fine with new version of Office. So I recommend of one the best methods to open your such DOCX documents successfully is by downloading Microsoft Compatibility Pack. Though there are few websites which provides you online conversion but for getting your converted file through e-mail frustrates for sure. Be happy with microsofts compatibility pack and enjoy!

Customize your work week

Does your work week start on Tuesday and end on Thursday? Lucky you! You'll be pleased to know that Outlook can display the work week for you that way. You would do this by modifying the settings in the Calendar Options dialog box, as we've shown in the picture. (To open this dialog box, you would click Options on the Tools menu, and then click Calendar Options).
After you modify the settings for the work week, you'll see the Work Week view when you click the Work Week button on the Standard toolbar (note that even if you change the work week to contain some number of days other than 5, the button will continue to display the number 5). In this view, Outlook shows only the days of your work week, and it displays the working hours in a slightly lighter color than the rest of the calendar background. You'll get a chance to try this on your own in the practice session that is coming up next.
If you're using Microsoft Exchange Server, one benefit of setting up your work week to reflect the schedule that you actually work is that when people try to schedule time with you, your Free/Busy time will reflect the appropriate hours and times of your work week. (For more information about Microsoft Exchange, see the article What is an Exchange Server e-mail account?)

6 tips for adding international characters and symbols to your document

from microsoft.com

Applies to
Microsoft Office Word 2003Microsoft Word 2000 and 2002
Addressing a letter to a customer with an umlaut in the name? Comparing U.S. dollars to yen, euros, and pounds sterling in a report? Need to include characters from the Greek alphabet in your science homework? It's amazing how often you need a symbol or international character when you're creating a document.
If you are having trouble finding the symbol or character you are looking for, tip 1 shows you how to locate characters in the Symbol dialog box, and tip 2 provides additional information about inserting international characters.
If you have already found the symbol or character you want, these four tricks make adding the symbols or characters you use often even faster. Tip 3 and tip 4 show you how to customize Word to open the Symbol dialog box with one click or keyboard shortcut. Tip 5 and tip 6 show you how to set up shortcuts for inserting characters or symbols that you use all the time.
Finding the characters and symbols you want
The Symbol dialog box is the place to go when you want to insert a symbol or special character. To open the Symbol dialog box, click Symbol on the Insert menu

With all the symbols and characters available, it can be a challenge to find the one you want. You can change the selection of characters by choosing a different font. You can even narrow your search by choosing a subset of the font.
Tip 1: Try different fonts to see different characters
The Font list in the Symbol dialog box shows the fonts that are available in Word, and each font offers different characters (sometimes very different). Some of the fonts are divided into subsets so that you can view the list of characters by groups. For example, in the Times New Roman font, you can click General Punctuation in the Subset list to quickly view the punctuation characters available in the selected font. Not every font has a Subset list, and the list is different depending on the selected font.
Note To display the Subset list in Word 2003 or 2002 for the fonts that support the list, click Unicode (hex) in the from list at the bottom of the Symbol dialog box.
To find a limited selection of international characters, click the Latin-1 or Latin Extended-A subset in a font that supports the Subset list, such as Arial.
Tip 2: International characters: Use shortcuts or a different keyboard
To insert international characters in your document, you can use a keyboard shortcut, or if you type extensively in another language, you may prefer to switch to a different keyboard.

Microsoft PowerPoint

Microsoft PowerPoint is a proprietary presentation program developed by Microsoft. It is part of the Microsoft Office system, and runs on Microsoft Windows and the Mac OS computer operating systems. The Windows version can run in Linux operating system, under the Wine compatibility layer.
PowerPoint is widely used by business people, educators, students, and trainers and is among the most prevalent forms of persuasive technology. Beginning with Microsoft Office 2003, Microsoft revised the branding to emphasize PowerPoint's place within the office suite, calling it Microsoft Office PowerPoint instead of just Microsoft PowerPoint. The current versions are Microsoft Office PowerPoint 2007 for Windows and 2008 for Mac. As a part of the popular Microsoft Office suite, PowerPoint could be considered the world's most widely used presentation program.

Using conditional formatting in excel

Conditional formating helps you make the excel cells behave in a different way according your requirements you desire. For example you can change the cell background to red if you enter text as red or the moment you enter text as red the font color changes to red. You can play with excel you can do lot of things in excel and more over conditional formating which help you create alerts also autmatic barcharts inside the cells instead of using bar charts. If you need help regarding such formats feel free to write to us @ w.xl.tech@gmail.com

-----------------------------

Excel's conditional formatting feature (available in Excel 97 or later) offers an easy way to apply special formatting to cells if a particular condition is met. This feature is even more useful when you understand how to use a formula in your conditional formatting specification.
The worksheet below shows student grades on two tests. Conditional formatting highlights students who scored higher on the second test. This formatting is dynamic; if you change the test scores, the formatting adjusts automatically.

To apply conditional formatting, select range A2:C15 and choose Format, Conditional Formatting. The Conditional Formatting dialog box will appear with two input boxes. In the first box, choose Formula Is, press Tab, and enter the following formula: =$C2>$B2
Click Format and choose a format to distinguish the cells (the example uses background shading). Click OK, and the formatting will be applied.
The conditional formatting formula is evaluated for each cell in the range. The trick here is to use mixed cell references (the column references are absolute, but the row references are relative). To see how this works, activate any cell within the range and choose Format, Conditional Formatting so you can examine the conditional formatting formula for that cell. You'll find that cell A7, for example, uses this formula:=$C7>$B7.

Thanks to www.j-walk.com

Dont call it excel because now it is excellent

Its been quite a few months that myofficepower remain idle, as other projects come across the way well they did good. So it's been always a passion to talk about excel. Please keep following the new things gonna happen only on myofficepower.blogspot.com.