Using parameter with Microsoft Query 'CONTAINS' criteria

Overview

Microsoft Query does provide the "CONTAINS" criteria to check whether a field contains a value, the SQL 'WHERE' clause for this is "WHERE criteria_field LIKE '%value_to_check%'". The problem is how to use parameter with "CONTAINS" criteria?

Steps

With the '&' operator we can easily concatenate the parameter in the "LIKE '%%'" clause. The SQL is:
WHERE criteria_field LIKE '%' & ? &'%'

Query Access DB from Excel 2007 by "Microsoft Query"

Overview

Let's consider this scenario: I have a Access DB and want to retrieve the data in Excel. In Excel 2007, the easiest way to do it (as far as I know) is to use built-in Access connect ("Data" tab => "Get External Data" group => "From Access").

However, in this way I cannot pass parameter into the query, how about if I want to use the value of a cell as the query parameter? In this case, you can use "Microsoft Query" in Excel 2007.

Seems that "Microsoft Query" can be used on other DB, but I haven't tried it out yet.

Reference

1. Connect to (Import) external data (Excel 2007)
2. Use Microsoft Query to retrieve external data
3. Customize a parameter query

Steps

Build a parameter query with "Microsoft Query"

1. In Excel 2007, select "Data" tab => "Get External Data" group => "From Other Sources" => "From Microsoft Query"
2. Select your Access database
3. In the "Query Wizard", setup your query and select "View data or edit query in Microsoft Query" to open "Microsoft Query"
4. In "Microsoft Query", click the "Show/Hide Criteria" button (the button with a pair of glasses on it)
5. Select the Criteria Field (Field to be compared in SQL 'WHERE' clause), and enter "[Parameter Name]" in "Value" to specify a parameter in the SQL query.
6. Switch to SQL view and you should see a 'WHERE' clause like this:
   WHERE customer_id = ?
   The question mark "?" represents a parameter.
7. Go ahead and modify the SQL as you like. When finished, select "File" => "Return Data to Microsoft Office Excel" to save and exit.

Use a cell's value as parameter (For more detail, go to Reference 3)

1. Click on any cell of your query result
2. Select "Data" tab => "Connections" group => "Properties" (If "Properties" button is grayed out, redo Step 1 )
3. Click "Connection properties" (The little button next to Connection Name)
4. Select "Definition" tab, click "Parameters" (If there is no "Parameters" button, it means that your query doesn't contain parameter, redo Step 1-7 in previous section)
5. Select the desired parameter, click "Get the value from the following cell:" and select the cell, you may also select "Refresh automatically when cell value changes"
6. Done. Change the value of the cell to test the query.
7. Happy building query! (Hopefully...)

Eliminate error messages with "IFERROR" in Excel 2007

Overview

In making dynamic Excel spreadsheet, sometimes we may have a function with result "#REF" or "#DIV/0". Some of these error messages are avoidable, but some of them aren't. In this case we have to conceal (hide) the error message. In Excel 2003 or before, we were using the "IF" function to check whether the function results in an error. However, this workaround is cumbersome and not efficient, because we have to put the function twice inside the "IF" statement.

Now in Excel 2007, we have a new function "IFERROR" designed to due with this particular situation. Let's see how we can benefit from it.

Reference

Eliminate those unfriendly error messages with Excel 2007’s IFERROR function

Step

The step is very simple, just replace your old "IF" function:

=IF(SOME_FUNCTION(),”Error Message”, SOME_FUNCTION())

with this one

=IFERROR(SOME_FUNCTION(),”Error Message”)

and you are good to go~

Set the Start Up display Form in Access 2007

Overview

In my previous blog showing How to convert Access 2007 App into Runtime Mode, I discuss how to convert Application to Runtime Mode. However, you need to specify a Start-Up Form so that the Runtime Application have something to show when started.

Reference

Change Access 2007 database options for your application

Steps

1. Fire up Access 2007 and open your application (NOT in Runtime Mode)
2. Click the "Office" button
3. Click the "Access Options" button, then choose "Current Database" in left pane
4. Click the "Display Form" drop-down list and set your start-up form
5. Finish!

Convert application to Runtime Mode in Access 2007

Overview

Access Forms let you create a user-friendly interface to end-user, it can also restrict what your users can view/modify. However user can still modify your table directly if your application is not run in Runtime Mode. Below shows the steps to convert your Access 2007 application to runtime before you put it to production.

Reference

Convert your Access 2007 application from design to runtime mode by changing its extension

Steps

1. Open the folder that contains your application (with .accdb extension)
2. Ensure that extension is shown in filename (this can be set in "Folder Options"),  rename the application to have .accdr extension (e.g. "myApp.accdb" rename to "myApp.accdr")
3. Finished!

MOSS hosted InfoPath Form with Web Services Data Connection

Overview

在上一篇文 Publish Browser Enabled InfoPath Form to MOSS 2007 中分享了製作 MOSS browser enabled InfoPath Form 的方法. 今次就再為InfoPath Form 加上 Web Services 既 Data Connection.

Reference

1. InfoPath and Web Service data connection
2. Data Connections in Browser Forms
3. Data Connection Libraries
4. Publish a form template to a server running InfoPath Forms Services
5. How to: Deploy Form Templates That Contain Form Code
6. MSDN - Visual Studio 2005 Tools for Applications

Steps

我的經驗告訴我, 所有與 SharePoint 扯上關係的 Development 都不會像表面所看那麼簡單... 一定有很多困難的地方. 這次都不例外, 看看上面列出的 reference link 數目之多就可知道...

在下面我只會例出簡單的流程, 並指出相對應的 reference.

1. 當然是先要建立起一個web service吧. 這個用 Visual Studio 2005 可以輕鬆做到.
2. 建立與web service溝通的 InfoPath Form Template. 在 InfoPath 2007 中的 "Design a Form Template" Wizard 可以讓你一步一步建立起以 web service 做為 data source 及 submit target 的 Form Template. 詳細請參考 Reference 1. 下面列出幾點要注意的地方︰
   2.1   在 "Design a Form Template" Wizard 中可以選 "Enable browser-compatible features
           only", 可以避免誤用與 browser 不相容的功能.
   2.2   在 "Tools" => "Form Options" => "Compatibility" 中選 "Design a form template that
           can ......", 再將要 deploy / test 的 MOSS 的 URL 打入. InfoPath 就會用動替你找出
           template中任何與 server 不相容的地方, 並顯示在 "Design Checker" 中看到.
3. 在 InfoPath 中, 將 Data Connection Convert 並存放在 MOSS 的 Data Connection Library 中, 等候 MOSS Administrator Approve (在 development 的時候, 應該你自己就是 administrator). 詳細請參考 Reference 3. 這步驟很重要, 不然之後在 browser submit form 的時候會出 Security Notice.
4. 好~ InfoPath 的 Form Template 做完. 跟著就要去 MOSS 做 configuration. 先去 "Central Administration" Page, 再 "Application Management" => "Configure InfoPath Form Services", 然後選 "Allow cross-domain data access .....".
5. 最後再去之前 Step 3 中 store Data Connection 的 MOSS Data Connection Library, 將所有要用的 Data Connection 都 Approve (當然要用 Administrator account Login). Step 4, 5 都請參考 Reference 2 & Reference 4.
6. 最後將 Form Template Publish 去 MOSS, new 一張 Form 試一下, 如果沒問題就完成了！

Reference 5, 6 是關於如何在 InfoPath 中寫 custom code, 這將需要 VSTA. 而且 depoly 去 MOSS 的時候也有多些 configure 要做, 就不在這裡多講了.

Publish Browser Enabled InfoPath Form to MOSS 2007

今日開始玩InfoPath, 想publish一張 InfoPath 既 form 入 SharePoint Server 做 WebPart, 但係0向 publish 既時候就見到呢個warning...

"This form template is browser-compatible, but it cannot be
browser-enabled on the selected site"

結果搞左一大輪先搞得掂... 方法請參考下面既link

Reference:

A checklist for enabling browser forms with Forms Services and SharePoint 2007

KeyPoint:

其實我跟足 Reference 既方法都唔得, 原因大概係因為0向 SharePoint 做 setting 既時候 server未可以成功改到setting. 咁就可以根據以下keypoint試試︰

1. 做完第 1, 2 步之後, 可以 restart 一次 IIS 先再做 step 3.
2. 如果去到 "Site collection features" 搵唔到 "InfoPath Forms Services support", 就去 "Office SharePoint Server Enterprise Site Collection features"
3. 如果所有步驟都無效, 就做以下動作, 手動 restart 個 feature︰
   3.1   去 SharePoint Server, 入 command prompt, 去 folder: "Program Files\Common
           Files\Microsoft Shared\web server extensions\12\BIN"
   3.2   輸入 "stsadm -o deactivatefeature -filename IPFSSiteFeatures\feature.xml -force -url
           http://YourMOSSurl"
   3.3   Operation Success 後, 再輸入 "stsadm -o activatefeature -filename
           IPFSSiteFeatures\feature.xml -force -url http://YourMOSSurl"