Update query

[AlexanderBB]

I find this works for me within Access
Code:

UPDATE Table1 SET Table1.d = "wow" WHERE (((Table1.Field1)="ThisRow"));

But I'm trying to send this from Access to Excel via ADO connection and can't figure out the sql for addressing the columns in Excel.
I have a working connection wand a recordset open so I'm close !

 

[Fazza]

well you have some choices (such as if you have a table name or are using a sheet name), additionally it will vary for whether you have headers or not.

if the Excel range has a normal (non-dynamic) name, then : UPDATE [rangename] SET fieldname_d ='wow' WHERE field1_name = 'ThisRowIfText'
or if numeric the last section is WHERE field1_name = 123

cheers

 

[AlexanderBB]

Could you elaborate please? I have no idea what a "non dynamic" name is. There's no range set, it's just a cell in Excel, e.g. column2 row 4
I do have a column Names and a Worksheet name. I didn't know there were table names in Excel ? But factoring these into the sql is the problem. You show "fieldname_" and "field1_" - are
these the Header names? Or the default where no header name? But if no header wouldn't they all be field1, field2 etc. not "fieldname"?

What is the significance of the underscore? Does it somehow 'set' whatever comes before it as the column to use ?
Thanks for any more info. Very hard to get a solution here which is kind of weird.. Googled for days and nothing usable found.

 

[Fazza]

worksheet names & header names are usable. I assume the headers are in row 1 starting from cell A1 and data is contiguous underneath. that is data from row 2 down

syntax for using a worksheet name is brackets and $ such as [YourSheetName$]
syntax for using a valid header name is just the name
(if there are no headers & data starts in row 1 then try referring to fields F1, F2, F3, F4, etc)

"fieldname_d" is what I created to refer to your field name d. if your field name is d then use d
"field1_name" is what I created to refer to your field name Field1. if your field name is Field1 then use Field1

so if your sheet is called "Bob" & your field names are per your question (and I have not checked if Field1 is a valid name), this should be OK,

UPDATE [Bob$] SET d = 'wow' WHERE Field1 = 'ThisRow'