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; |
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' 2
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. |