| re: using Excel as a DB
I still get the same error...
##########################################
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Excel Driver] Operation must use an updateable query.
/excel/excel_display1.asp, line 34
line 34:
cn.execute "update hammerin_hank set Name='" & "MARK" &"' where ID=4"
Tim Williams wrote:
[color=blue]
> If you want to do an update then the best approach is to use SQL
>
> <%
> exceldb="betting.xls"
> Set cn = Server.CreateObject("ADODB.Connection")
> cn.Open "DBQ=" & Server.MapPath(exceldb) & ";" & _
> "DRIVER={Microsoft Excel Driver (*.xls)};"
>
> cn.execute "update hammerin_hank set updateFieldName='update value' where
> idFieldName='recIdToUpdate'"
>
>
> %>
>
> Tim.
>
>
>
> "Mark" <Mark@MARK.MArk.Mark.Com> wrote in message
> news:%23KsJC1d6EHA.1564@TK2MSFTNGP09.phx.gbl...
>[color=green]
>>I am reading from an excel spreadsheet as an ODBC Recordset.
>>But whenever i try to write to it I get an error
>>
>>
>>A)
>>Ideally i would like to write "Mark" to A5
>>like this :
>>RS("$A5") = "Mark"
>>RS.UpdateBatch
>>but it doenst like the RS("$A5") is there an easy way to do this?
>>
>>
>>B) if i cant access it like that i have done loops to find out if it is
>>at the right location then do a
>>rs.fields.item(counter).value = "Mark"
>>which works but when i do a
>>rs.updatebatch
>>i get :
>>Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
>>[Microsoft][ODBC Excel Driver] Operation must use an updateable query.
>>
>>on the line number of the updatebatch
>>
>>Code is as follows... I just dont understand what to change to make it
>>work. I believe all file permissions are ok and have checked twice.
>>
>>
>>
>>
>>
>>##############################################
>>CODE
>>##############################################
>><%
>>exceldb="betting.xls"
>>Set cn = Server.CreateObject("ADODB.Connection")
>>cn.Open "DBQ=" & Server.MapPath(exceldb) & ";" & _
>>"DRIVER={Microsoft Excel Driver (*.xls)};"
>>
>>
>>Set rs = Server.CreateObject("ADODB.Recordset")
>>
>>sql="select * from hammerin_hank;"
>>
>>rs.Open sql, cn, 3,4
>>dim MKGCount
>>MKGCount = 0
>>do while not rs.eof
>>' Loop through all of the fileds
>>MKGCount = MKGCount + 1
>>for counter = 0 to rs.fields.count - 1
>>response.write rs.fields.item(counter).value
>>if MKGCount = 4 then
>>if counter = 0 then
>>rs.fields.item(counter).value = "Mark"
>>rs.updatebatch
>>end if
>>end if
>>next
>>rs.movenext
>>loop
>>rs.Close
>>Set rs = Nothing
>>' Kill the connection
>>cn.Close
>>Set cn = Nothing
>>%>[/color]
>
>
>
> From - Sat[/color]
| 
