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')"
cm.Execute
- 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:127.0.0.1[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
Income.country = "PAK"
Income.sys_Save
Income.sys_Close
Set Income = Nothing
