Dear Forum,
I have to upload the content of chart CH01 (65000 records in size) to a SQL database.
I use the macro below for that task.
The macro works fine. However, when the macro reaches the end, the qlikview program wont close. In the lower left corner of QlikView there is a message: "Macro running".
The document is however closed but the application is not.
Another strange thing to note here is that the application will close if i limit the no of records in the loop to 100. (instead of 65000)
This annoying freeze/hang of the macro prevents my intention of planning the document into task planner. Any help would be appreciated.
Sub upload
Const adOpenStatic = 3
Const adLockOptimistic = 3
'Establish a connection
Set objConnection = CreateObject("ADODB.Connection")
objConnection.open "PROVIDER=SQLOLEDB;DATA SOURCE=xxxxxx;UID=xxxxxx;PWD=xxxxxx;DATABASE=xxxxxx "
Set objRecordSet = CreateObject("ADODB.Recordset")
'' First delete all records from the table.
objRecordSet.Open "DELETE FROM wm_customers",objConnection, adOpenStatic, adLockOptimistic
set TableObject = ActiveDocument.GetSheetObject( "CH01" )
For RowIter = 1 to TableObject.GetRowCount-1
set wmcustomer_no = TableObject.GetCell(RowIter,0)
set wmcustomer_name = TableObject.GetCell(RowIter,1)
set wmcustomer_address = TableObject.GetCell(RowIter,2)
set wmcustomer_place = TableObject.GetCell(RowIter,3)
set wmcustomer_participantid = TableObject.GetCell(RowIter,4)
set wmcustomer_id = TableObject.GetCell(RowIter,5)
'Set SQL
objRecordSet.Open _
"INSERT INTO wm_customers " & _
"(wmcustomer_no,wmcustomer_name,wmcustomer_address,wmcustomer_place,wmcustomer_participantid,wmcustomer_id ) " & _
"VALUES ('"&wmcustomer_no.text&"', '"&wmcustomer_name.text&"', '"&wmcustomer_address.text&"', '"&wmcustomer_place.text&"', '"&wmcustomer_participantid.text&"', '"&wmcustomer_id.text&"')", _
objConnection, adOpenStatic, _
adLockOptimistic
next
Set objRecordSet = Nothing
objConnection.Close
Set objConnection = Nothing
Application.Quit
ActiveDocument.GetApplication.Quit
end sub
