hi all,
i wrote a macro for creating table and inserting selections in qv to a database.that s all right.it works without any problem.BUT there is a problem about its performance.if you write only 100 rows, it works well.it does this job in a few seconds.but if you want to write data like 100.000 rows it works too slowly.i didnt wait but i can say that it may last aprx 60-70 mins.
does anybody know how to increase writng speed? or can i do the same job with another application?
you can find the code works here:
sub dbCreateInsert
dim sConn, oRS, sSQL1,sSQL2,var1
set var1= ActiveDocument.Variables("VARIABLE")
var1 = ActiveDocument.Variables("VARIABLE").GetContent().string
set doc = ActiveDocument
Set sConn = CreateObject("ADODB.Connection")
sConn.Open "Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=USERNAME;Password=PASSWORD;Data Source=DBNAME;"
set oRS =CreateObject("ADODB.Recordset")
'creating table with name gettiing from variable
sSQL1="CREATE TABLE " & var1 & " (CUSTOMERID varchar(20))"
set oRS = sConn.execute(sSQL1)
'selecting all different rows
set cLoRecord=doc.Fields("CUSTOMERID _NO").GetPossibleValues()
for i=0 to cLoRecord.Count-1
vRecord = cLoRecord.Item(i).Text
doc.Fields("CUSTOMERID _NO").Select vRecord
set CM1 = doc.Fields("CUSTOMERID").GetPossibleValues()
vm1 = cM1.item(0).Text
' writing to db
sSQL2="INSERT INTO " & var1 & " (CUSTOMERID ) VALUES (" & "'" & vm1 & "')"
set oRS = sConn.execute(sSQL2)
next
'warning after writing
doc.ClearAll
msgbox("all your selectins has been written")
set oRS = Nothing
set sConn = Nothing
end sub
