ADO Recordset to SQLite

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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值