Question

Write data to Excel Spreadsheet - ERROR [HY000] [Microsoft][ODBC Excel Driver] Operation must use an updateable query.

Workflow

Updated October 26, 2018

asked on July 10, 2018

I originally setup the connection as Direct Connection using ODBC and Data Source Driver as Microsoft Excel 2007/2010

I read the post about checking the ODBC and making sure it isn't set to Read-Only

https://answers.laserfiche.com/questions/61884/Trying-to-insert-data-into-Excel-and-get-operation-must-use-an-updateable-query

I found my ODBC was not:

But the error persisted.

Any thoughts on which modifications I should make to write to the Excel file? I know writing to Excel in general is bad practice. Their spreadsheet will be phased out eventually with custom SQL Tables I am building.

0 0

Answer

SELECTED ANSWER

replied on July 10, 2018

I actually just resolved this.

I did a Windows ODBC connection and created a System Driver for Excel.

Worked perfectly.

3 0

Replies

replied on October 26, 2018

Something we've found recently...

If using the ODBC Connection method, the solution as above is sufficient: ensure that the driver setup shows that it is not read-only. This is the third bullet point from the resolution in this Microsoft KB article for working through the issue, for "connecting through a DSN".

If using the Direct Connection method, this is the equivalent of "a DSN-less connection" in the same article, for which you'd need to apply the fourth bullet point of the resolution. To achieve this in Workflow, when specifying the Excel file for the connection, suffix the path with ";ReadOnly=0;", so that for example you specify the following file in the selection:

C:\Worksheets\path\to\my\worksheet.xlsx;ReadOnly=0;

You should then be able to insert data to the worksheet using the direct connection.

3 0

replied on October 26, 2018

I try to avoid using Excel sheets to read/write to for this very reason. Usually I will just dump out reports and store to the Repository.

I will take it a step further as well if I need to read/write to Excel and users need to use it. I make sure the document is in the Repository and then check if the document is locked and delay until it resolved not being locked. This works pretty well for me.

But my error was coming up from trying to connect the driver. The server was missing the driver completely so I created it again.

0 0

You are not allowed to follow up in this post.

Question

Question

Write data to Excel Spreadsheet - ERROR [HY000] [Microsoft][ODBC Excel Driver] Operation must use an updateable query.

Answer

Replies

Sign in to reply to this post.