- 帖子
- 552
- 主題
- 3
- 精華
- 0
- 積分
- 578
- 點名
- 0
- 作業系統
- win7
- 軟體版本
- office 2010
- 閱讀權限
- 50
- 性別
- 男
- 註冊時間
- 2015-2-8
- 最後登錄
- 2024-7-9
  
|
本帖最後由 lpk187 於 2017-2-19 20:20 編輯
用這個例子吧！1萬筆資料約3秒。這個insert into 的用法
匯入資料，不一定得用SQL語句- Public Sub ex()
- sql = "SELECT * FROM TblStudent ;"
- strSqlInstance = "伺服器名稱"
- strSqlDB = "資料庫名稱"
- strSqlUser = "sa"
- strSqlPWD = "a123456"
- sConnect = "Provider=SQLOLEDB"
- sConnect = sConnect & ";Data Source=" & strSqlInstance & ";Initial Catalog=" & strSqlDB
- sConnect = sConnect & ";User ID=" & strSqlUser & ";Password=" & strSqlPWD & ";"
- Set myCon = CreateObject("ADODB.Connection")
- myCon.Open sConnect
- Set rng = Range("A2")
- With New ADODB.Recordset
- .Open sql, myCon, , adLockOptimistic
- Do Until rng = ""
- .AddNew
- For i = 1 To .Fields.Count - 1
- .Fields(i) = rng.Offset(, i - 1)
- Next
- Set rng = rng.Offset(1, 0)
- Loop
- .Update
- End With
- Set myRs = Nothing
- myCon.Close
- Set myCon = Nothing
- End Sub
|
|
發表於 2017-2-17 23:56
| 
