微软MSDN提供的格式如下:SELECT [id] INTO #tempExl
FROM OpenDataSource(
'Microsoft.Jet.OLEDB.4.0',
'Data Source=e:\temp.xls;Extended properties=Excel 5.0')...main$
WHERE [id] <> ''经查询分析器测试能在本机数据库运行正常导数据成功
但是如果在远端的数据库里就不行了出错信息
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' does not contain table 'main$'. 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='main$'].请问应该如何操作,谢谢
FROM OpenDataSource(
'Microsoft.Jet.OLEDB.4.0',
'Data Source=e:\temp.xls;Extended properties=Excel 5.0')...main$
WHERE [id] <> ''经查询分析器测试能在本机数据库运行正常导数据成功
但是如果在远端的数据库里就不行了出错信息
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' does not contain table 'main$'. 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='main$'].请问应该如何操作,谢谢
http://www.microsoft.com/china/community/Column/31.mspx
do while not 本地recordset.eof
远程recordset.addnew
远程recordset.fields(0)=本地recordset.fields(0)
远程recordset.update
本地recordset.movenext
loop
你给的那个地址用的是我上面的那个方法,我这边试验只能对本地数据库有效To:frankwong(黄梓钿--快乐ing新爸爸^_^)
可以这么做,但一条一条往里插不会很慢吗?
,'Excel 5.0;HDR=YES;DATABASE=e:\test.xls',sheet1$)
select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=e:\test.xls',sheet1$)
//但一条一条往里插不会很慢吗?
慢是慢点,但暂时想不出其他的方法,这个好像是唯一或者是最好的方法.
Dim cn As New Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\test.xls;Extended Properties=Excel 8.0"
’如果这张表不存在,你可以使用如下代码:
strSQL = "SELECT * INTO [odbc;Driver={SQL Server};Server=sha-kennyhao-01;Database=Northwind;UID=sa;PWD=;].Customers2 FROM [Sheet1$]"
cn.Execute strSQL, , adExecuteNoRecords
如果表已经存在,您需要添加进数据库,可以使用如下代码:
strSQL = "INSERT INTO [odbc;Driver={SQL Server};Server=sha-kennyhao-01;Database=Northwind;UID=sa;PWD=;].Customers2 SELECT * FROM [Sheet1$]"
cn.Execute strSQL, , adExecuteNoRecords