我已经在网上搜索了,但是结果还是不行,现在讲我的程序写在这里,请大家帮我解决一下。
已经用sql的dts试了,可以导入excel的数据。但我编的程序,会提示odbc调用失败。Private Sub Command1_Click() ’点击命令按钮后,开始执行导入
Dim cnSqlserver As ADODB.Connection
Dim cnExcel As ADODB.Connection
Dim rsExcel As ADODB.Recordset
Dim rsSqlserver As ADODB.Recordset Set cnExcel = New ADODB.Connection
cnExcel.CursorLocation = adUseClient
On Error GoTo err:
cnExcel.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\bm.xls;Extended Properties=Excel 8.0;Persist Security Info=true" ‘c:\bm.xls,要导入的excel,就是从sql里bmxx表导出的。
Dim cn As ADODB.Connection
Dim strSQL As String
Dim lngRecsAff As Long
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\bm.xls;" & _
"Extended Properties=Excel 8.0" 'Import by using Jet Provider.
strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _
"Server=w8105;Database=my_database;" & _
"UID=sa;PWD=sa].bmxx " & _ 'bmxx,sql里已经有的表
"FROM [sheet1$]" ’sheet1就是该表单
Debug.Print strSQL
cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
Debug.Print "Records affected: " & lngRecsAff cn.Close
Set cn = Nothingerr:
MsgBox err.Description, vbOKOnly + vbExclamation, "出错了"
End Sub请大家帮忙,非常紧急。谢谢!
已经用sql的dts试了,可以导入excel的数据。但我编的程序,会提示odbc调用失败。Private Sub Command1_Click() ’点击命令按钮后,开始执行导入
Dim cnSqlserver As ADODB.Connection
Dim cnExcel As ADODB.Connection
Dim rsExcel As ADODB.Recordset
Dim rsSqlserver As ADODB.Recordset Set cnExcel = New ADODB.Connection
cnExcel.CursorLocation = adUseClient
On Error GoTo err:
cnExcel.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\bm.xls;Extended Properties=Excel 8.0;Persist Security Info=true" ‘c:\bm.xls,要导入的excel,就是从sql里bmxx表导出的。
Dim cn As ADODB.Connection
Dim strSQL As String
Dim lngRecsAff As Long
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\bm.xls;" & _
"Extended Properties=Excel 8.0" 'Import by using Jet Provider.
strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _
"Server=w8105;Database=my_database;" & _
"UID=sa;PWD=sa].bmxx " & _ 'bmxx,sql里已经有的表
"FROM [sheet1$]" ’sheet1就是该表单
Debug.Print strSQL
cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
Debug.Print "Records affected: " & lngRecsAff cn.Close
Set cn = Nothingerr:
MsgBox err.Description, vbOKOnly + vbExclamation, "出错了"
End Sub请大家帮忙,非常紧急。谢谢!
Private Sub Command1_Click()
Dim cn As New ADODB.Connection
cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=123;Initial Catalog=dataBase001;Data Source=mySERVICE"
cn.CursorLocation = adUseClient
cn.Open
cn.Execute "select * into table4 from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;HDR=Yes;database=c:\Test.xls;','select * from [Sheet1$]')"
cn.Close
Set cn=Nothing
End Sub
所以我现在用insert into. 但是此时又会出现新的问题,excel中的数据都是ntext型,数据类型与目标树表中的不符合。需要用convert函数转换。
我所参加的课题要求可以由用户选择不同的目的表进行导入,如何能保证在导入时数据类型,主键不变?