如何使用OpenDataSource方法, vb匯入xls到sql server2005資料庫中
Dim objConn As New ADODB.Connection
Const recordsaffected = 30
Const adCmdText = 1
Const adCmdTable = 2
Const adOpenKeyset = 1
Const adLockPessimistic = 2
'連接資料庫
Public Sub ConnectToServer()
On Error GoTo Errmsg
objConn.Open "driver={SQL Server};server=" & DBIP & ";uid=" & DBuid & ";pwd=" & DBpwd & ";database=" & DBName & ";"
Exit Sub
Errmsg:
MsgBox "資料庫連接出錯，請聯系管理員！" & Err.Description, vbInformation
End
End Sub
'下面是匯入程式
'先洗掉該日資料，支持反復匯入
CommandText = "delete from " & sTableName & " where pt_dt='" & ProDataDate & "'"
objConn.Execute CommandText, recordsaffected, adCmdText
'匯入資料
CommandText = "insert into " & sTableName & "(" & Replace(sFieldNames, "|", ",") & ") " & _
" SELECT " & Replace(sFieldNames, "|", ",") & _
" FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0'," & _
" 'Data Source=""" & CheckFilePath(App.Path) & sFileName & """;User ID=;Password=;Extended properties=Excel 8.0')" & _
" ...[" & sTableName & "_" & Format(DTPicker1.Value, "YYYYMMDD") & "$]"
objConn.Execute CommandText, recordsaffected, adCmdText
Print "新增匯入" & sTableName & "表" & I_n & "條記錄成功！"
'但就是報錯，VB 物件關閉時不允許操作，不知道為何?
'想一次性匯入，速度快，但以上問題解決不了，最后不得不使用Excel.Application，一條一條insert，比較慢
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/21755.html
標籤：VB基礎類