And a few years more.
I think the ACE drivers are buggier than a cockroach driving a VW Beetle. I'm pretty sure the legend that is Jeff Moden reported something to Microsoft and it just got dropped without being fixed. If anyone knows him maybe give him a nudge?!
If it helps, I think the IMEX setting overrides some things and causes issues. The following tests demonstrate this.
Make a spreadsheet called SQLtest with a tab called Test and save it as XLSB. In Row 1 put A in column A, B in column B and c in column c. I pit 3 rows of random numbers below these letters making 4 rows of data. Save it, in my case in D:\ where I know I
have full control and so does SQL.
--- Test SELECT
SELECT *
FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0',
'Excel 12.0;Database=D:\SQLtest.xlsb;HDR=YES;IMEX=0',
'SELECT * FROM [Test$]' -- OK
) as SLT
GO
--- Test update - Header and auto data type
UPDATE SLT SET C='100' -- Works even though column numeric
FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0',
'Excel 12.0;Database=D:\SQLtest.xlsb;HDR=YES;IMEX=0',
'SELECT * FROM [Test$]'
) as SLT
WHERE A='3' -- Works even though this is numeric
GO
/*
RESULT - OK
(1 row affected)
*/
--- Test update - Header and force data type to text
UPDATE SLT SET C='100' -- FAILS!
FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0',
'Excel 12.0;Database=D:\SQLtest.xlsb;HDR=YES;IMEX=1',
'SELECT * FROM [Test$]'
) as SLT
/*
RESULT - FAILURE
OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "(null)" returned message "Cannot update. Database or object is read-only.".
Msg 7399, Level 16, State 1, Line 24
The OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "(null)" reported an error. The provider indicates that the user did not have the permission to perform the operation.
Msg 7343, Level 16, State 4, Line 24
The OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "(null)" could not UPDATE table "[Microsoft.Ace.OLEDB.12.0]".
*/
--- Test update - no header and data type automatic
UPDATE SLT SET C='100' -- Works!
-- UPDATE SLT SET F3='100' -- Fails as F3 does not exist
FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0',
'Excel 12.0;Database=D:\SQLtest.xlsb;HDR=NO;IMEX=0', -- IMEX=0 automatically overrides HDR=NO
'SELECT * FROM [Test$]' -- Need single quotes and square brackets!
) as SLT
WHERE A='3' -- Works even though this is numeric and column should be F1
GO
/*
RESULT - OK with modification
(1 row affected)
*/
--- Test update - no header and data type automatic
UPDATE SLT SET F3='100' -- FAILS!
FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0',
'Excel 12.0;Database=D:\SQLtest.xlsb;HDR=NO;IMEX=1',
'SELECT * FROM [Test$]' -- Need single quotes and square brackets!
) as SLT
WHERE F1='2'
GO
/*
RESULT - FAILURE
OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "(null)" returned message "Cannot update. Database or object is read-only.".
Msg 7399, Level 16, State 1, Line 54
The OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "(null)" reported an error. The provider indicates that the user did not have the permission to perform the operation.
Msg 7343, Level 16, State 4, Line 54
The OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "(null)" could not UPDATE table "[Microsoft.Ace.OLEDB.12.0]".
*/
JCEH
Update Excel spreadsheet using ACE driver (Microsoft.Ace.OLEDB.12.0) in 64bit environment.
