I have a client who has a huge Excel file. They absolutely want to continue to work with this file. They asked us if we can update data in the file from a PocketPC.
I created a linked server to the spreadsheet:
EXEC master.dbo.sp_addlinkedserver
@server = N'ExcelFile',
@srvproduct=N'Excel',
@provider=N'Microsoft.ACE.OLEDB.12.0',
@datasrc=N'Filename.xls',
@provstr=N'Excel 12.0;IMEX=1;HDR=YES;'
I can successfully query the file with the following:
SELECT *
FROM ExcelFile...[Feuil1$]
If the file is already open, I get an error. I guess the file MUST be closed?
Anyway, is there a way to update cells in the Excel file with something like:
UPDATE ExcelFile...[Feuil1$]
SET [BIP] = 123456
WHERE [BIP] = '966985'
I get this error:
An error occurred while preparing the query "UPDATE Feuil1$ set
BIP
= (1.234560000000000e+005) WHEREBIP
=(9.669850000000000e+005)" for execution against OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "ExcelFile".
Thanks for your time and help