Matching Record In 2 Record set

Matching Record In 2 Record set

Post by gyan200 » Fri, 04 Aug 2006 21:21:48


I am writing macro who will extract table name from ORACLE and chek is
this in DB2 or not.
I am using windows2003 server and ORACLE 8.2.1 and DB2 8.2. and office
2000
my code is folowing
********************** CODE *******************
Private ORACLECON As Connection
Private DB2CON As Connection
Private RSORACLE As Recordset
Private RSDB2 As Recordset

Set ORACLECON = New Connection
Set DB2CON = New Connection
ORACLECON.ConnectionString = "Provider=MSDAORA.1;Data Source=" &
LCase(Trim(server_name.Text)) & ";User ID=" & LCase(Trim(user_id.Text))
& ";Password=" & LCase(Trim(password.Text)) & ";Persist Security
Info=True"
DB2CON.ConnectionString = "Provider=IBMDADB2.1;Data Source=" &
LCase(Trim(SERVER_NAME1.Text)) & ";User ID=" &
LCase(Trim(USER_ID1.Text)) & ";Password=" & LCase(Trim(PASSWORD1.Text))
& ";Persist Security Info=True;Location=" & "" & ";Extended
Properties=" & "" & ""
ORACLECON.Open
DB2CON.Open

Set RSORACLE = New Recordset
Set RSDB2 = New Recordset
With RSORACLE
.Source = "select a.table_name,count(a.column_name) from
user_tab_columns a,user_tables b where a.table_name=b.table_name group
by a.table_name"
Set .ActiveConnection = ORACLECON
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.Open
End With
With RSDB2
.Source = "select NAME,COLCOUNT from sysibm.systables where creator='"
& UCase(Trim(user_id.Text)) & "'" & " AND TYPE='T'"
Set .ActiveConnection = DB2CON
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.Open
End With
RowCnt = 3
While Not RSORACLE.EOF
Sheet3.Cells(RowCnt, 1).Value = RowCnt - 2
Sheet3.Cells(RowCnt, 2).Value = CStr(RSORACLE.Fields(0).Value)
Sheet3.Cells(RowCnt, 3).Value = CStr(RSORACLE.Fields(1).Value)
RSDB2.MoveFirst
RSDB2.Find "NAME='" & Trim(Sheet3.Cells(RowCnt, 2).Value) & "'"
If Not RSDB2.EOF Then
Sheet3.Cells(RowCnt, 4).Value = CStr(RSDB2.Fields(0).Value)
Sheet3.Cells(RowCnt, 5).Value = CStr(RSDB2.Fields(1).Value)
Sheet3.Cells(RowCnt, 6).Value = "Found"
Else
Sheet3.Cells(RowCnt, 6).Value = "Not Found"
End If
RSORACLE.MoveNext
RowCnt = RowCnt + 1
Wend

rowfinalT = RowCnt
******************************** END CODE *************************

Here it is not displaying any thing from RSDB2 Record set while there
is one matching is there.

Thanks in advance.......................

Ritesh Kumar