SQL查询Excel表出现问题,以及两个SQL表如何连接Excel表查询
SQl 查询语句:SELECT * FROM OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0 ', 'Data Source= "d:/Example.xls ";Extended Properties=Excel 8.0 ')...APC DC$
报错:
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0 ' does not contain table 'APCDC$ '. The table either does not exist or the current user does not have permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName= 'Microsoft.Jet.OLEDB.4.0 ', TableName= 'APCDC$ '].
我用的是Excel2003,SQL2000
另外,SQL表如何连接Excel表查询,他们有一个共同的字段
------解决方案--------------------SELECT * FROM OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0 ', 'Data Source= "d:\Example.xls ";Extended Properties=Excel 8.0 ')...[APC DC$]
斜线画反了
------解决方案--------------------回复人:raulvim() ( 二级(初级)) 信誉:100 2007-9-24 13:48:08 得分:0
寒泉兄弟,斜线改正之后用的Excel2000可以执行成功(家里的电脑).办公室的Excel2003,SQL2000下仍然报错:
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0 ' does not contain table 'APCDC$ '. The table either does not exist or the current user does not have permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName= 'Microsoft.Jet.OLEDB.4.0 ', TableName= 'APCDC$ '].
另外3表连接查询仍然需要请教你
===========================================================
Data Source= "d:\Example.xls "原因是由於這一句中的D盤是指服務器的D盤,而不是你當前電腦的D盤
------解决方案--------------------SQl 查询语句:SELECT * FROM OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0 ', 'Data Source= "d:/Example.xls ";Extended Properties=Excel 8.0 ')...APC DC$
------------------------
用OPENDATASOURCE 要加上用户名和密码,User ID=Admin;Password=;上面的错误里面已经告诉你了。
OPENDATASOURCE( '驱动名称 ', '连接字符串 '),以你上面的为例:
SELECT * FROM OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0 ', 'Data Source= "d:\Example.xls ";User ID=Admin;Password=;Extended Properties=Excel 8.0 ')...APC DC$
使用OPENROWSET则不需要
SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0 ', 'Excel 8.0;Database= "d:\Example.xls ',[APC DC$])