My coding doesn't seems to work??

Need some advice from all the vb gurus here...

Below are the coding

Option Explicit
Public siebeldb As ADODB.Connection
Public nicedb As ADODB.Connection
Public rs1 As ADODB.Recordset
Public rs2 As ADODB.Recordset
Dim sql_Siebel As String

Private Sub cmExit_Click()
End
End Sub

Private Sub Form_Load()
Dim intRecCount As Integer
Dim intCounter As Integer

Set siebeldb = New ADODB.Connection
Set nicedb = New ADODB.Connection
Set rs1 = New ADODB.Recordset
Set rs2 = New ADODB.Recordset

'siebeldb.open opens SQL Server Connection
siebeldb.Open "Provider=sqloledb;" & _
"Data Source=rightfax;" & _
"Initial Catalog=siebel_database;" & _
"User Id=sa;" & _
"Password="

sql_Siebel = "SELECT Call_Type FROM siebel_table"
rs1.Open sql_Siebel, siebeldb, 0, 1

'nicedb.open opens SQL Server Connection
nicedb.Open "Provider=sqloledb;" & _
"Data Source=IMD;" & _
"Initial Catalog=NICE;" & _
"User Id=sa;" & _
"Password="

Set rs1 = New ADODB.Recordset
rs1.CursorType = adOpenKeyset
rs1.LockType = adLockOptimistic
rs1.Open "siebel_table", siebeldb, , , adCmdTable

Set rs2 = New ADODB.Recordset
rs2.CursorType = adOpenKeyset
rs2.LockType = adLockOptimistic
rs2.Open "nice_table", nicedb, , , adCmdTable

'List out the call type column from siebel and nice database
'Note that using this method, every time the database moves to the next record it must make a check to see if it has reached the end of the file.
'This slows the looping down a great deal, but it works.

rs1.MoveFirst
Do While Not rs1.EOF
lsSiebel.AddItem rs1("Call_Type")
rs1.MoveNext
Loop

rs2.MoveFirst
Do While Not rs2.EOF
lsNice.AddItem rs2("Call_Type")
rs2.MoveNext
Loop

'Trying another method (see below), it suppose to list out the data faster but not sure why not working. There is no error but nothing list out at the lsSiebel list box

'rs1.MoveLast
'intRecCount = rs1.RecordCount
'rs1.MoveFirst

'For intCounter = 1 To intRecCount
'lsSiebel.AddItem rs1("Call_Type")
'rs1.MoveNext
'Next intCounter

End Sub


It doesn't seems to work...I am trying to list out the data using a for loop so that it can load the data out faster...please advice

VB Code:

Rs.CursorLocation = adUseClient
Rs.Open mSQL, Cn, adOpenKeyset, adLockOptimistic

where msql is the query... this will return the no of records
use the for .. next will be faster

If lsSiebel and lsNice are listboxes then the following will significantly speed up your process.

VB Code:

Public Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
                       (ByVal hwnd As Long, _
                        ByVal wMsg As Long, _
                        ByVal wParam As Long, _
                        lParam As Any) As Long
 
Public Const LB_ADDSTRING = &H180
 
rs1.MoveFirst
Do While Not rs1.EOF
Call SendMessage(lsSiebel.hwnd, LB_ADDSTRING, 0, ByVal rs1("Call_Type"))
rs1.MoveNext
Loop

If the SendMessage line gives you an error, try this

Call SendMessage(lsSiebel.hwnd, LB_ADDSTRING, 0, ByVal cstr(rs1("Call_Type")))

is there anything wrong with my coding??...the for loop part to list down the data....because when I used the do while loop, it works but take some time to load out the data.....

when u get the recordcount check it might be -1

if u set the recordset parameter as posted it will return the record count change it and try MartinLiss which will be fast using APi

API??....do you need to add any component using MartinLiss coding...?

You don't need to add anything. Just put this part in the Declarations section of a code module or change Public to Private and put it in the Declarations section of your form.

VB Code:

Public Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
                       (ByVal hwnd As Long, _
                        ByVal wMsg As Long, _
                        ByVal wParam As Long, _
                        lParam As Any) As Long
 
Public Const LB_ADDSTRING = &H180

Thanks again...it works...but just wondering why using the api will be faster?....

Need your help again..is there anyway to improve my copy and update code?...after listing out the database I would like to copy from siebel database to nice database...here are the following code.

'declare an API function for retrieving the data faster
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
(ByVal hwnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
lParam As Any) As Long

Private Const LB_ADDSTRING = &H180

Private Sub Form_Load()

'set database connection
Set siebeldb = New ADODB.Connection
Set nicedb = New ADODB.Connection

'siebeldb.open opens SQL Server Connection
siebeldb.Open "Provider=sqloledb;" & _
"Data Source=rightfax;" & _
"Initial Catalog=siebel_database;" & _
"User Id=sa;" & _
"Password="

'nicedb.open opens SQL Server Connection
nicedb.Open "Provider=sqloledb;" & _
"Data Source=IMD;" & _
"Initial Catalog=NICE;" & _
"User Id=sa;" & _
"Password="

'set database for recondset
Set rs1 = New ADODB.Recordset
Set rs2 = New ADODB.Recordset

'sql statement for retrieving both databases
sqlRetrieveS = "SELECT Call_Type FROM siebel_table"
rs1.Open sqlRetrieveS, siebeldb, 0, 1

sqlRetrieveN = "SELECT Call_Type FROM NICE_table"
rs2.Open sqlRetrieveN, nicedb, 0, 1

'list out the call_type data from siebel database
rs1.MoveFirst
Do While Not rs1.EOF
Call SendMessage(listdata2.hwnd, LB_ADDSTRING, 0, ByVal CStr(rs1("Call_Type")))
rs1.MoveNext
Loop

'list out the call_type data from nice database
rs2.MoveFirst
Do While Not rs2.EOF
Call SendMessage(listdata.hwnd, LB_ADDSTRING, 0, ByVal CStr(rs2("Call_Type")))
rs2.MoveNext
Loop

'Copy data from siebel and update to nice database - column
'Call_Type
rs1.MoveFirst
Do While Not rs1.EOF

rs2.AddNew
rs2!Call_Type = rs1("Call_Type")
rsNice.Update

rs1.MoveNext
Loop

End Sub

What I have done is copying the whole siebel database to nice database, this is going to be very slow if the database is big, how can I just copy only the latest ones?..please advice again..

so any help from the vb gurus here??.....

u can try this... if u r 2 database are on same server...

insert into nice.dbo.NICE_table select * from siebel.dbo.siebel_table where....

if on diffrent servers

then create a linked servers and


insert into servername.nice.dbo.NICE_table select * from servername.siebel.dbo.siebel_table where....