Creating New Tables With VB and Populating With Recordsets

[Dragenesis]

Here's the scenario.

I've queried data from an oracle database and want to save it locally. The local table needs to be wiped clean or recreated before the results of each query is appended to it. I also need to save the number of returns the query gives in yet another locally stored table that also needs to be wiped clean each time. I currently am able to connect to the database, do the query and push the query results into recordsets, and then display them with Debug.print. I know I can use the query string to put the results directly into a table, but I'm don't know how to tell which value goes to which column or how to wipe the destination table before appending the new results. Also, I need to know how many results were returned. So far my code is something like this:

Dim conn as NEW ADODB.connection
Dim cmd as NEW ADODB.command
Dim rs as NEW ADODB.recordset
Dim strconn, strsql as string
Dim i as integer

strconn = (connection string)
strsql = (query string)

with conn
     .connectionstring = strconn
     .open
     End with

with cmd
     .activeconnection = conn
     .commandtext = strsql
     End with

set rs = cmd.execute
rs.open

set i = 0

do while not rs.eof
     Debug.print (query results)
     i = i + 1
     loop

debug.print i

The code was written from memory, so it may be off a little. It does work so far.

 

[Guus2005]

The answer is something like this:

with rs
   .edit
   .fields("col1") = "something"
   .fields("col2")=True
   .update
end with

HTH