How do I fix "The ability to edit data in a linked Excel spreadsheet has been disabled"?

[Breck_Carter]

When trying to INSERT to an Excel 2007 sheet from SQL Anywhere 11.0.1.2276, I get the following error on Windows XP SP3:

Could not execute statement.
Server 'EXCEL': [Microsoft][ODBC Excel Driver] You cannot edit this
field because it resides in a linked Excel spreadsheet. The ability to
edit data in a linked Excel spreadsheet has been disabled in this Access
release.
SQLCODE=-660, ODBC 3 State="HY000"
Line 50, column 1
You can continue executing or stop.

INSERT proxy_ttt VALUES ( 1, 'A' )

Here is the DSN and the code:

---------------------------------------------------------------------
-- V11 CREATE TABLE AT

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\\Software\\ODBC\\ODBC.INI\\excel_files]
"Driver"="C:\\\\PROGRA~1\\\\COMMON~1\\\\MICROS~1\\\\OFFICE12\\\\ACEODBC.DLL"
"DBQ"="C:\\\\TEMP\\\\dummy.xlsx"
"DefaultDir"="C:\\\\TEMP"
"DriverId"=dword:00000416
"FIL"="excel 12.0;"
"ReadOnly"=hex:01
"SafeTransactions"=dword:00000000
"UID"=""

[HKEY_CURRENT_USER\\Software\\ODBC\\ODBC.INI\\excel_files\\Engines]

[HKEY_CURRENT_USER\\Software\\ODBC\\ODBC.INI\\excel_files\\Engines\\Excel]
"ImplicitCommitSync"=""
"MaxScanRows"=dword:00000008
"Threads"=dword:00000003
"UserCommitSync"="Yes"
"FirstRowHasNames"=hex:01

BEGIN
   DROP TABLE ttt;
   EXCEPTION WHEN OTHERS THEN
END;

CREATE TABLE ttt (
   pkey                INTEGER NOT NULL,
   data                VARCHAR ( 10 ) NOT NULL,
   PRIMARY KEY ( pkey ) );

BEGIN
   DROP TABLE proxy_ttt;
   EXCEPTION WHEN OTHERS THEN
END;

BEGIN
   DROP SERVER EXCEL;
   EXCEPTION WHEN OTHERS THEN
END;

CREATE SERVER EXCEL 
   CLASS 'ODBC' USING 'dsn=excel_files';

-- Some suggestions for CREATE AT and CREATE EXISTING AT...
--
-- Both files dummy.xlsx and ttt.xlsx will be created
--    when the CREATE TABLE ... AT is run.
--
-- Character strings may be truncated to 64 bytes on 
--    SELECT from an Excel proxy table.
--    Even if that limit can be bypassed, there may be 
--    another limit of 255 bytes.
--    These limits may be imposed by the ODBC driver, not Excel.
--
-- Do NOT use spaces in column titles on row 1, otherwise you will
--    get an error message like "Too few parameters. Expected 8."
--    when you try to SELECT from the proxy table.
--
-- The magic trailing $ may or may not be required...
--    AT 'EXCEL;C:\\\\temp\\\\ttt.xlsx;;ttt$';

CREATE TABLE proxy_ttt ( 
   pkey   INTEGER,
   data   VARCHAR ( 64 ) )
   AT 'EXCEL;C:\\\\temp\\\\ttt.xlsx;;ttt';

INSERT proxy_ttt VALUES ( 1, 'A' );
INSERT proxy_ttt VALUES ( 2, 'B' );

INSERT ttt SELECT * FROM proxy_ttt;
COMMIT;

BEGIN
SELECT * FROM proxy_ttt;
SELECT * FROM ttt;
END;

[Breck_Carter]

And the answer is... use the old Excel driver, not the new one that comes with Excel 2007.

In particular, use this driver: Microsoft Excel Driver (*.xls) Version 4.00.6305.00 File ODBCJT32.DLL Date 13/04/2009

Do NOT use this one: Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb) Version 12.00.6421.1000 File ACEODBC.DLL Date 06/03/2009

The new DSN and working code follows; the end result is a brand new *.XLS file containing a named sheet created from nothing by a SQL Anywhere 11 CREATE TABLE ... AT statement.

Also note that Excel 2007 has no trouble opening this file, unlike the file created using the newer driver.

Here comes the code...

---------------------------------------------------------------------
-- V11 CREATE TABLE AT with Excel

-- Some suggestions for CREATE AT and CREATE EXISTING AT...
--
-- Use the old Excel driver:
--    Microsoft Excel Driver (*.xls) Version 4.00.6305.00 
--       File ODBCJT32.DLL Date 13/04/2009
--
-- If you use the new Excel 2007 driver...
--    Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb) Version 12.00.6421.1000 
--       File ACEODBC.DLL Date 06/03/2009
-- you may get this error when you INSERT to the proxy table:
--    Could not execute statement.
--    Server 'EXCEL': [Microsoft][ODBC Excel Driver] You cannot edit this
--    field because it resides in a linked Excel spreadsheet. The ability to
--    edit data in a linked Excel spreadsheet has been disabled in this Access
--    release.
--    SQLCODE=-660, ODBC 3 State="HY000"
--    Line 50, column 1
--    You can continue executing or stop.
--
-- If the dummy.xls file named in the DSN does not exist,
--    it will be created when the CREATE TABLE ... AT runs.
--
-- If the ttt.xls file named in the AT clause does not exist,
--    it will be created when the CREATE TABLE ... AT runs.
--
-- Character strings may be truncated to 64 bytes on 
--    SELECT from an Excel proxy table.
--    Even if that limit can be bypassed, there may be 
--    another limit of 255 bytes.
--    These limits may be imposed by the ODBC driver, not Excel.
--
-- Do NOT use spaces in column titles on row 1, otherwise you will
--    get an error message like "Too few parameters. Expected 8."
--    when you try to SELECT from the proxy table.
--
-- The magic trailing $ may or may not be required...
--    AT 'EXCEL;C:\\\\temp\\\\ttt.xls;;ttt$';
--
-- The ReadOnly setting has no effect. If you get a "read only"
--    error message it is likely caused by some other mistake
--    such as using the .xlsx extension in the AT clause
--    instead of .xls.

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\\Software\\ODBC\\ODBC.INI\\excel_files_xls]
"Driver"="C:\\\\WINDOWS\\\\system32\\\\odbcjt32.dll"
"DBQ"="C:\\\\temp\\\\dummy.xls"
"DefaultDir"="C:\\\\temp"
"DriverId"=dword:00000316
"FIL"="excel 8.0;"
"ReadOnly"=hex:01
"SafeTransactions"=dword:00000000
"UID"=""

[HKEY_CURRENT_USER\\Software\\ODBC\\ODBC.INI\\excel_files_xls\\Engines]

[HKEY_CURRENT_USER\\Software\\ODBC\\ODBC.INI\\excel_files_xls\\Engines\\Excel]
"ImplicitCommitSync"=""
"MaxScanRows"=dword:00000008
"Threads"=dword:00000003
"UserCommitSync"="Yes"
"FirstRowHasNames"=hex:01

BEGIN
   DROP TABLE ttt;
   EXCEPTION WHEN OTHERS THEN
END;

CREATE TABLE ttt (
   pkey                INTEGER NOT NULL,
   data                VARCHAR ( 10 ) NOT NULL,
   PRIMARY KEY ( pkey ) );

BEGIN
   DROP TABLE proxy_ttt;
   EXCEPTION WHEN OTHERS THEN
END;

BEGIN
   DROP SERVER EXCEL;
   EXCEPTION WHEN OTHERS THEN
END;

CREATE SERVER EXCEL 
   CLASS 'ODBC' USING 'dsn=excel_files_xls';

CREATE TABLE proxy_ttt ( 
   pkey   INTEGER,
   data   VARCHAR ( 64 ) )
   AT 'EXCEL;C:\\\\temp\\\\ttt.xls;;ttt';

INSERT proxy_ttt VALUES ( 1, 'A' );
INSERT proxy_ttt VALUES ( 2, 'B' );

INSERT ttt SELECT * FROM proxy_ttt;
COMMIT;

BEGIN
SELECT * FROM proxy_ttt;
SELECT * FROM ttt;
END;

pkey,data
1,'A'
2,'B'

pkey,data
1,'A'
2,'B'

[Former Member]

I believe Microsoft disabled the ability to do updates for legal reasons: http://support.microsoft.com/?id=904953

Looking in the sqlanywhere.general newsgroup, I'd guess this issue has been bothering Breck since at least June 2009:

http://groups.google.com/group/sybase.public.sqlanywhere.general/msg/2a9fd095081fd4c3a

I think the only technical answer is to use an older driver that doesn't have the "fix" to prevent updates.

[VolkerBarth]

Just a wild guess (I don't use Excel 2007):

The "Readonly" flag seems to be the problem. Maybe it can be set to 0 in the Registry.

Excerpt from the readme for SA 11.0.1.2376, Engineering Case #580133

Also note that the Microsoft Excel ODBC driver treats the file as read-only by default. To write to the file, turn off the "Read only" flag in the DSN or, if not using a DSN, include "ReadOnly=0" as a connection parameter in the OUTPUT statement. For example:

SELECT * FROM Departments;
OUTPUT USING 'Driver=Microsoft Excel Driver (*.xls);
       dbq=c:\\test\\test.xls;ReadOnly=0'
INTO Departments CREATE TABLE ON

[Breck_Carter]

Didn't notice that setting, but it makes no difference... tried changing it in the USING clause and in the registry both. The error message is talking about something more fundamental than a read-only setting. Also, note that the ReadOnly setting did NOT prevent the CREATE TABLE from creating both the *.xslx file AND the sheet... only the INSERT fails, the subsequent SELECT * FROM proxy_ttt works. Even though it's empty, the ability to create both a file and a sheet would argue against "read only" status... and yes the sheet has the titles "pkey" and "data" in row 1 when viewed by Excel.

[Breck_Carter]

Another quirk, probably unrelated: Excel 2007 refuses to open the .xlsx file. When the file is renamed to .xls, Excel 2007 complains but will open it if you insist.

[Breck_Carter]

And the answer is... not "ReadOnly" but "use the older Excel driver"... this issue's been annoying me for HOW long? ...months

[Breck_Carter]

I cancelled my downvote, which had cancelled an earlier upvote, so this answer now has two upvotes. And the reason? Actually NOTICING the ReadOnly setting is worth points IMHO... I wonder what (if anything) ReadOnly actually does?

[VolkerBarth]

@Breck: Thanks for the last comment - I guess quick attempts to help (when noted as such) should not get downvotes IMHO. When not that helpful (as here), they won't get relevant votes, and will get clarifiying comments, and that is fine. Downvoting them might make folks afraid to post answers when they are not sure if they apply. Just my 2 cents:)