i haven't gone through your code but i think you're misusing excel and
vba for something like that.
why don't you use plain VB, C#, Java, perl, python, or anything else?
e.g. you could do this easily with perl.
if you do not know perl i would recommend you downloading VB.Net express
edition
http://msdn.microsoft.com/vstudio/express/vb/
cheers.
Ritesh wrote:
> 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
>
Similar Threads
