THANKS!!!!
I'm given a spreadsheet where any one of the columns can contain a
list of drug names. I have an Access database with a massive list of
drug names and the ailments those drugs treat. I'm to insert a new
column in the spreadsheet, lookup the drug and populate a new appended
column with the referenced ailment. That's why I have to 'modify' an
Excel SS. (That should answer Slugg's q)
However, if I can't get the code you supplied to work directly I'll
first import the SS data into an Access table (I can read the SS all I
want no problem) , do the lookups and the rest of the work within the
Access table and then write it out to a brand new .xls file. (I can
write new stuff all I want, just not modify)
Thank you very much for the input, any advice on this topic is
non-existent and has been frustrating to find. I'll try your example
and expand from there.
Tom
On 22 Nov 2004 01:45:41 -0800,
ja**********@xsmail.com (Jamie Collins)
wrote:
Tom wilson <ye*******@nospam.com> wrote ...
myDataRow = myDataSet.Tables(0).Rows(RowNum)
myDataRow(ColumnCount) = Ailment
Adapter.UpdateCommand = New OleDbCommand("UPDATE [" &
SheetName & "] SET F" & ColumnCount & " = '" & Ailment & "' where F1 =
" & RowNum & "", Conn)
Adapter.Update(myDataSet, "[" & SheetName & "]")
The query sent is:
"UPDATE [Drugs cleaned up$] SET F6 = 'Test Ailment' where F1 = 1"
In the spreadsheet, F1 = 1. It's a uniquely numbered column and I
assume, the primary key. F6 is also a valid column. The Sheet name
is also correct cause it's open. I figured, and am told from MS
documents, that a unique column is required to make it an updateable
query. That's what I've done but I can't get anything but:
"operation must use an updateable query"
You SQL works for me using this code:
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source='C:\Tempo\db.xls';Extended Properties='Excel 8.0;HDR=NO'"
Dim conn As New OleDbConnection(strConn)
Dim strSQL As String = "UPDATE [Drugs cleaned up$] SET F6 = 'Test
Ailment' where F1 = 1"
Dim cmd As New OleDbCommand(strSQL)
cmd.Connection = conn
conn.Open()
cmd.ExecuteNonQuery()
cmd.Connection.Close()
As regards the error you are getting, Jet doesn't like in an update
e.g.
UPDATE MyTable SET MyCol = (
SELECT Col1 FROM OneRowTable
);
The above fails with the same error as your even thought the table in
the subquery isn't itself being updated. I'm wondering if one of your
.NET objects is using additional SQL under the covers that is
upsetting the provider. My code, on the other hand, simply sends the
SQL unaltered to the provider for execution. Just a guess.
Jamie.
