ADO Recordset to SQLite
来源:互联网 发布:cloudflare nginx 编辑:程序博客网 时间:2024/04/18 19:27
What is the fastest way to get data from an Interbase database or from an ADO recordset to a SQLite database?
I have this code now, which works well, but is a bit slow:
Sub IB2SQLite()
Dim i As Long
Dim c As Long
Dim x As Long
Dim strConn As String
Dim ADOConn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cn As SQLiteDb.Connection
Dim sqliteRS As SQLiteDb.Recordset
Dim PassWord As String
Dim UserName As String
Dim strSQL As String
Dim arr
Dim LR As Long
Dim LC As Long
10 On Error GoTo ERROROUT
20 Set ADOConn = New ADODB.Connection
30 UserName = "UN"
40 PassWord = "PW"
50 strConn = "DSN=System 6000;" & _
"UID=" & UserName & ";" & _
"PWD=" & PassWord
60 ADOConn.Open strConn
70 strSQL = "SELECT R.SUBJECT_TYPE, R.READ_CODE, R.TERM30, R.TERM60 FROM READCODE R" ' WHERE R.READ_CODE STARTING WITH 'G'"
80 Set rs = New ADODB.Recordset
90 rs.Open Source:=strSQL, _
ActiveConnection:=ADOConn, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockReadOnly, _
Options:=adCmdText
100 If rs.EOF Then
110 Set rs = Nothing
120 Exit Sub
130 End If
140 LR = rs.RecordCount
150 Set cn = New SQLiteDb.Connection
160 cn.ConnectionString = "Data Source=C:/SQLite/Terra/rc2.db"
170 cn.Open
180 cn.Execute "PRAGMA synchronous=off;", , slExecuteNoRecords ' Just to speed up things
190 cn.Execute "PRAGMA encoding='UTF-8';", , slExecuteNoRecords
200 cn.Execute "CREATE TABLE [READCODE] ([SUBJECT_TYPE], [READ_CODE], [TERM30], [TERM60])"
210 Set sqliteRS = cn.Execute("SELECT * FROM [READCODE] WHERE 1=0")
220 LC = rs.Fields.Count - 1
230 While Not rs.EOF
240 sqliteRS.AddNew
250 For i = 0 To LC
260 If rs(i).Type = adDate Then
270 sqliteRS.Value(i) = Format(rs(i).Value, "yyyy-MM-dd hh:mm:ss")
280 Else
'This is faster than sqliteRS(i) = rs(i).Value
290 sqliteRS.Value(i) = rs(i).Value
300 End If
310 Next
320 sqliteRS.Update
330 rs.MoveNext
340 x = x + 1
350 If x Mod 1000 = 0 Then
360 Application.StatusBar = " Rows done: " & x
370 DoEvents
380 End If
390 Wend
400 cn.Execute "CREATE INDEX S_IDX ON READCODE (SUBJECT_TYPE)"
410 cn.Execute "CREATE INDEX R_IDX ON READCODE (READ_CODE)"
420 cn.Execute "CREATE INDEX T_IDX ON READCODE (TERM30)"
430 cn.BeginTrans
440 cn.CommitTrans
450 cn.Close
460 Exit Sub
ERROROUT:
470 MsgBox Err.Description & vbCrLf & vbCrLf & _
"Error number: " & Err.Number & vbCrLf & _
"Error line: " & Erl
End Sub
I wonder if I could make a construction with INSERT INTO, so pushing data directly to SQLite, without making a
Recordset first.
Thanks for any advice.
RBS
- ADO Recordset to SQLite
- Binding a DataGrid to an ADO Recordset
- ado.RecordSet
- ADO.Recordset对象方法
- ADO Recordset 对象
- ADO Recordset Open
- Recordset 对象 (ADO)
- ADO Recordset addnew
- ADO Recordset 对象
- ADO Recordset 对象
- 补充些ADO Recordset知识
- ADO recordset open方法解析
- 在ado.net中使用ado recordset
- ADO如何锁定RecordSet的记录呢?
- ASP与ADO RECORDSET对象 的错误
- PRB: Catastrophic Error Occurs Referencing ADO Recordset.
- MSDN的文档 Open 方法 (ADO Recordset)
- C#调用ADO的RecordSet.AddNew方法.
- 改良程序的11技巧
- 优酷网网站架构分析
- Firefox和IE之间7个JavaScript的差异
- 旧博客转帖关于qtp的一些脚本
- COM接口聚合实现解惑(C++虚表)
- ADO Recordset to SQLite
- dos学习笔记一之简单dos命令
- strut2在ExtJs中使用复合类型
- icmp socket
- ubuntu NDK 的安装
- 正则表达式30分钟入门教程 1.本文目标
- Visual C++ 6.0环境配置
- 学习笔记之三dos命令进阶
- icmp socket 之二