Append data to local table

[teel73]

Hi
I am trying to connect to oracle database and add data to a MS Access table via append query.

I was able to connect to oracle and create a recordset just fine. But now I need to add the data from the recordset to an existing local table. Here's what I have so far:

Public Const dvcon$ = "Driver={Microsoft ODBC for Oracle}; Server=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=prodora08.doleta.gov)(PORT=1921)) " & _
"(CONNECT_DATA=(SID=stbpdb)));User Id=RPT_GROUP;Password=PASSWORD1;"

 
Function createConn()
Dim rs As New ADODB.Recordset
Dim qdfPassThrough As QueryDef
Dim fld As ADODB.Field
Dim selstring As String
Dim dnn As New ADODB.Connection
  
dnn.Open dvcon

rs.Open mysql, dnn
If Not rs.BOF Then rs.MoveFirst

This runs fine but I don't know how to manipulate the data once the recordset is open.

 

[Guus2005]

once the recordset is open you can walk thru it and use the data to fill the local table.

you can either create an SQL insert statement or use a recordset from the local table and use the addnew method to create new records and copy data from the oracle database.

... or you can use a different approach and link the table and copy the data or import the table locally.

HTH