Monday, March 15, 2010

Updating Cache from Excel

Excel (2007) is great as a quick database front end tool – reasonably versatile and easy to set up. This ease of use might make many want to be able to update a database from Excel, and that is not so easy.

It has to be done in code. One way would be through linking the Excel file to the database server (there are different mechanisms for doing this, e.g. OPENROWSET in SQL Server or the SQL Gateway in Cache). However, if you're running Office in 64-bit mode you soon discover that there is no 64-bit compliant Excel ODBC driver.

A quick and more natural solution is to embed the database update code in Excel itself; in the case of a Cache backend, there are two ways to write this code:

  • Using ADO/ODBC

    Dim cm As Command

    Dim cn As Connection

    Dim cns As String

    cns = "DSN=CacheWeb User"

    Set cn = New Connection

    cn.Open cns

    Set cm = New Command

    cm.ActiveConnection = cn

    cm.CommandType = adCmdText

    cm.CommandText = "INSERT INTO Income(UserId, Income, Country) VALUES('@u', @i, '@c')"


  • Using Cache ActiveX

A bit more interesting since it gets into some Cache internals:

Dim Factory As CacheObject.Factory

Set Factory = New CacheObject.Factory

If Factory.Connect("cn_iptcp:[1972]:TESTDBNS") = False Then

MsgBox "Failed to connect"

Exit Function

End If

Dim Income As Object

Set Income = Factory.New("Income")

Income.UserId = "2"

Income.Income = 120 = "PAK"



Set Income = Nothing