由于目录下的excel文件是每隔一小时会生成一个,需要系统将每小时生成的excel文件中的数据导入到SQLServer,VB代码如下:Dim cn As ADODB.Connection
Set cn = New ADODB.Connection'Import by using Jet Provider.
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=D:\IMPSQLServer.xls;" & _
"Extended Properties=Excel 8.0"strSQL = "insert into [odbc;Driver={SQL Server};" & _
"Server=servername;Database=databasename;" & _
"UID=sa;PWD=admin].tablename (column1,column2) " & _
"select column1,column2 from [Sheet1$]"cn.Execute strSQL
报错说是 insert的语法错误。上述SQL语句是INSERT INTO...SELECT...FROM追加 形式,我把它换成SELECT...INTO...FROM 形式是不会报错的。
'strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _
' "Server=servername;Database=databasename;" & _
' "UID=sa;PWD=admin].XLImport10 " & _
' "FROM [Sheet1$]"
但是我就是需要追加的形式,谁来帮个忙,看一下。谢了先~
Set cn = New ADODB.Connection'Import by using Jet Provider.
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=D:\IMPSQLServer.xls;" & _
"Extended Properties=Excel 8.0"strSQL = "insert into [odbc;Driver={SQL Server};" & _
"Server=servername;Database=databasename;" & _
"UID=sa;PWD=admin].tablename (column1,column2) " & _
"select column1,column2 from [Sheet1$]"cn.Execute strSQL
报错说是 insert的语法错误。上述SQL语句是INSERT INTO...SELECT...FROM追加 形式,我把它换成SELECT...INTO...FROM 形式是不会报错的。
'strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _
' "Server=servername;Database=databasename;" & _
' "UID=sa;PWD=admin].XLImport10 " & _
' "FROM [Sheet1$]"
但是我就是需要追加的形式,谁来帮个忙,看一下。谢了先~
哎,看来我是准备回去卖红薯了~
不过你有什么建议的话,我虚心向你学习!再次up!