Use MsAccess as MySql frontend


Horses for courses, where MySql, PHP and HTML are native to the web, PHP/HTML suck as table GUI,no offense, I just got extremely frustrated with it today. Another way to get the data off the web onto my desktop is using FTP/ODBC and enclosing MySql tables in my MsAccess desktop database. It has it perks but it works. If you want to try it check with your host (if you are not self hosting) if your host allows external access to your mysql server. For local servers it works flawless.

Download the odbc connector

Over at Dev.MySql they muist have heard my screams of agony an programmed an ODBC (open database connectivity) driver. Windows supports the ODBC standards hence in Office you can use external databases if you install an odbc driver for the database type.

Create the system datasource

Once the driver is installed you can add the MySql database as open database to Windows (odbc works via the operating system) and link the tables in MsAccess. Start by opening the Windows configuration screen and open System manager, where you can access the ODBC Datasources and open the ODBC Administrator screen :

Pick the tab System DSN and choose Add… to select a driver : the new MySql ODBC Driver.

Then comes the magic bit : when you click Finish, the MySql driver pops up its own configuration screen :

The data you fill in is the same login data used in WordPress wp-config.php : the mysql database host, login and password, and what database to select. When you are done, you can make more than one ODBC Sources so you can add every WordPress install you have on the web to it, provided you have access to the mysql server. A lot of hosts do not actually allow it, but mine do.

So far so good, the Datasource is now known in the Windows operating system, now we go to MsAccess.

Enclose the tables in MsAccess


Under External Data pick ODBC Database :

In the screen that opens, pick the Link data option :

And in the next screen pick the tab with machine data sources : this picture shows both my new localhost database and the database so I will pick that one (it is in Rotterdam somewhere on a server)

When I pick that one the driver kicks in and connects to the remote database, retrieving a list with tables for me to link into the database, for the occasion I will link the Ak_Twitter table of the WordPress Twitter Tools plugin that has my most recent Tweets :

And click okay and Voila :

The tables are linked

Note: if you get an error “server has gone away, ( ask your host to) set the connection timeout to 300 or more





Publish Microsoft Word 2010 documents to your WordPress blog

Hands on test, Microsoft Word 2010 supports publishing a document as WordPress blog post, excellent. I have been sailing for a while, so I haven’t played around with it yet. I love what Microsoft made. They did not just add an XML-RPC post routine, they added a small editor in Microsoft Word itself, especially for blog posts. It is everything you ever wanted but the WordPress editor just never was. You can manage content you already posted, publish directly or as draft, you can cut and paste screenshots, pictures, excel sheets and access tables, it is such a total improvement.

How to set Word up as WordPress editor ?

Make a document and pick from the File menu “Save and Send”, and you get an extra option to publish as blog post.




Once you pick that it opens an editor for blog posts, that pn the background uses the XML-RPC interface. Word starts a wizard the first time, that you can also access by “manage accounts” in the editor menu :

But let’s start by clicking Save and Send : Word will ask you to register an account, iow your wordpress blog website.

When registering you can pick from a whole list of blog types, of course I picked WordPress

In the dialog ‘new wordpress account’ you enter your XML-RPC end point, the directory of your blog with /xmlrpc.php, that is where you can post to your blog. Username and Password can be your normal username and password, you can also register separately at the blog. That would be my choice.

Of course these days blog posts all have pictures, and in the account dialog in the lower left corner there is a button “picture options” where you can specify how you want uploaded. You can use an FTP upload but in my case picking the blog provider (iow let WordPress handle it itself) works out of the box.

Once you click OK you also get a warning about the visibility of your login and password, XML-RPC posts are text files and your account user name and password are not encrypted when sent to the blog XML-RPC endpoint. There is little risk in it but it is a point that has to be made.

When you agree to continue you can edit your post. And when done, publish.

…and out came this post. In the WordPress editor you can add some tags and categories if you publish as draft, but you can also specify a category in the  Word editor..

Word offers a lot of picture effects yet it outputs the result as  .png images so it works without css3 dependencies, and outputs very clean html markup. Just for the picture effects and total ease of editing it gets a 5 out of 5.

how to get bing api search results in MsOffice VBA

For the loyal VBA fans a quick snippet to grab search results from Bing in Access or Excel. There are few articles on the web about that, and that is a shame. So lets add some content on it to the web : first in MsAccess VBA and then in Excel, with a sample workbook added.

Working with the BING API requires an Application Id you can get at the Bing website.

Accessing the Bing Api in MsAccess VBA

In the the VBA Ide, through the menu Tools References I add a reference to the XML object library that contains the XMLHTTPRequest object, enabling me to make HTTP requests from VBA in the MsAccess database.

The simple part is the http request string :

        Dim MyKeyword As String
        MyKeyword = "seo"
        Dim requestString As String
        requestString = ""

        Dim AppId As String
        AppId = "(get one at the bing website)"

       ' Common request fields (required)
        requestString = requestString & _
        "AppId=" & AppId & "&Query=" & MyKeyword & "&Sources=Web"

        ' Common request fields (optional)
        requestString = requestString & _
              "&Version=2.0" _
            & "&Market=en-us" _
            & "&Adult=Moderate" _
            & "&Options="

        ' Web-specific request fields (optional)
        requestString = requestString & _
              "&Web.Count=10" _
            & "&Web.Offset=0" _
            & "&Web.Options=DisableHostCollapsing+DisableQueryAlterations"

Then comes the working part, sending the actual request and receiving the XML response (objSvrHTTP.responseText) containing the search data. I use a DomDocument object and XPath to get at the XML data.

First sending the request

    objSvrHTTP.Open "GET", requestString, False
    objSvrHTTP.send requestString

then creating a domdocument and receiving the response in it

    'Create the DomDocument Object
    Dim oDoc As MSXML2.DOMDocument
    Set oDoc = CreateObject("MSXML2.DOMDocument")
    oDoc.async = False
    oDoc.validateOnParse = False
    'Load the response in the DomDocument Object
    Dim fSuccess As Boolean
    fSuccess = oDoc.loadXML(objSvrHTTP.responseText)
    If Not fSuccess Then
        MsgBox "failed"
        Exit Sub
    End If

The response uses two namespaces and the data we actually want is in the “web” namespace, so before we start selecting nodes in the xml-tree we first indicate what namespace we want to access :

    oDoc.SetProperty "SelectionNamespaces", "xmlns:web=''"

The total search result pages is a top level node in the namespace :

    Set xmlnode = oDoc.selectSingleNode("//web:Total")
    Debug.Print xmlnode.Text

…but the good stuff is in the “WebResult” nodes in the xml tree, I can use an XPath reference //web:Webresult to access that node-collection and with a simple for-next iterate through the collection, selecting the single nodes containing the actual data.

    Set oChildren = oDoc.selectNodes("//web:WebResult")
    For Each oResult In oChildren
        Debug.Print oResult.selectSingleNode("./web:Title").Text
        Debug.Print oResult.selectSingleNode("./web:Description").Text
        Debug.Print oResult.selectSingleNode("./web:Url").Text
        Debug.Print oResult.selectSingleNode("./web:DisplayUrl").Text
        Debug.Print vbCrLf

I output to the Immediate window aka Debug (under View, Immediate Window or Ctrl+G)

What can we do with it ? Let’s make a table for date, keyword, url, title, description, position

and add some code to store the results we retrieve

    Dim table1 As DAO.Recordset
    Set table1 = CurrentDb.OpenRecordset("Table1", dbOpenTable)
    'counter for position    
    i = 0
    Set oChildren = oDoc.selectNodes("//web:WebResult")
    For Each oStruct In oChildren

       i = i + 1
       With table1
            'data from bing
            !TITLE = oStruct.selectSingleNode("./web:Title").Text
            !Description = oStruct.selectSingleNode("./web:Description").Text
            !url = oStruct.selectSingleNode("./web:Url").Text
            'and some additional data
            !MYDATE = Now()
            !POSITION = i
            !KEYWORD = MyKeyword
            'store the record
        End With



There now that scratches the seo itch :

MsAccess stuff is a bit hard to put on the web as sample, so I’ll make a quick one in Excel and put it up for download.

Accessing the Bing Api in Excel VBA

there you go : on sheet 1 you can fill in your AppID and keyword, click the butt(on…

…and on sheet 2 you get the top 10 search results

It uses the cells B1 and B2 as named ranges for MyKeyword and AppID, and has the code in the enclosed module. It also contains one of my AppId’s so no querying nasty shit, please.