I used ODBC to create a sheet in Excel and add a row to it.
Literally the commands were just:
create table 'update5' ('age' NUMBER);
insert into 'update5'.'age' values (1);
This works and I can see the rows in the sheet and via DBVisualiser and my ODBC query results.
Later, I wrote more SQL to add another row like so:
insert into 'update5' ('age') values (2);
but I get the error:
[Microsoft][ODBC Excel Driver] Cannot expand named range.
I do not know why named ranges are being used, is there a way I can set ODBC to not use them?
Found it.
When you create an table in Excel via ODBC you create a named range of the same name within that table.
When you try to insert like this:
insert into 'update5'.'age' values (2);
It is interpreted as you trying to add to the the named range called update5 which is inside the table update5.
You need to use the syntax:
insert into [update5$].'age' values (2);
to add values to the table called update5.