write new table from recordset (query on DB2 data)

[ecompa]

Hi,

I've been working with a vba code to access dat from DB2 into Excel, but I need no to adapt this code to write data in an Access table.

Public Function GetData() As ADODB.Recordset

StrQuery = "SELECT * "
StrQuery = StrQuery  & "from Table "
StrQuery = StrQuery & " with UR ;"
Set GetData = ReadDB2()'this connects to DB2 and create a recordset with the query

Call DumpRecordset 'this writes in the Excel sheet
ObjCnn.Close
    
End Function

The procedure to write in Excel is a quite classic one:

    r = sr
        
    Do While Not RstMain.EOF 'until end of record set is reached
       r = r + 1
       c = sc
       For Each objField In RstMain.Fields
           c = c + 1
           If IsNull(objField) Then
               Cells(r, c) = "-"
           Else
              Cells(r, c) = Trim(objField.Value)
           End If
       Next
       RstMain.MoveNext
    Loop
    Exit Sub

Does someone has some code to do the same action in a new Access table ? I've been looking around a bit but could not find something quickly. I've seen someone advising the Addnew method combined with Update but how ?
Thanks
E.

 

[PHV]

Why not simply link an access table to the DB2 one ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ecompa]

HI PHV,

well to make a long story short, that is the way I was working until now. But for technical reasons it won't be possible in the future (due to change in the way IBM handles the ODBC link + question of privilieges I have on DB2).
So I really need to feed a table with the recordset created by connecting to DB2.
Any idea ?
E.