[AccessD] slightly OT: Oracle and ADO

Ryan,

I know this can be done by manually stepping through the recordset and
adding the records to the internal table.  I don't know if there's a way
to do a mass dump from a recordset to a table, but doesn't mean that
there isn't.  Here's how you'd do it manually (inserted after the
connection code I posted earlier):

'begin
Dim rst as ADODB.Recordset

rst.Open "SELECT * FROM my_table;", conn
rst.MoveFirst
While Not rst.EOF
  ' build comma-seperated list of values using rst(fieldname)
  CurrentDb.Execute "INSERT INTO tblMyAccessTable " & _
    "(<list of field names>) VALUES (<list of values from above>);"
  rst.MoveNext
Wend
'end

Since you already need to have the Oracle ODBC drivers installed to make
this connection in the first place, why not just link to the ODBC source
directly?  If you did this, then you could just run an append query from
one to the other.

Seth


On Fri, 2003-04-25 at 04:15, Smethurst, Ryan wrote:
> 
> Seth/Jack,
> 
> This is slightly OT, but reading below it looks like you two have done
> something similar to what I am attempting.  
> 
> Once the connection has been established in code, is it possible to create a
> recordset from that Oracle connection and then create a table in Access
> based on those records?
> 
> TIA
> RyanS
> 
>  -----Original Message-----
> From: 	Seth Galitzer [mailto:sgsax at ksu.edu] 
> Sent:	24 April 2003 22:48
> To:	accessd
> Subject:	RE: [AccessD] slightly OT: Oracle and ADO
> 
> Jack,
> 
> Here's a snippet of what I ended up using:
> 
> 'begin
> Dim conn As ADODB.Connection
> 
> Set conn = New ADODB.Connection
> conn.ConnectionString = "Provider=OraOLEDB.Oracle;" & _
>                         "Data Source=my.server.address.com;" & _
> 			"User ID=username;Password=password;"
> conn.Open
> 'end
> 
> Once you have that connection open, you can use it for any other action
> until you close it.  Note the "Data source" parameter can be an IP
> address, and possibly even a NetBIOS name if you are confident in the
> ability for your workstations to resolve those consistently.
> 
> Seth
> 
> On Thu, 2003-04-24 at 16:11, Drawbridge.Jack at ic.gc.ca wrote:
> > Seth: Did you ever get this resolved? We are seeking info on 
> > ODBCDirect with DAO (access 2000) talking to Oracle 9i. We're trying to
> get
> > to Oracle without the ODBC prompt. Can we do this with code (hopefully
> with
> > some code and logic you have discovered)?
> > 
> > Thanks.
> > 
> > jack
> > 

-- 
Seth Galitzer			sgsax at ksu.edu
Computing Specialist		http://puma.agron.ksu.edu/~sgsax
Dept. of Plant Pathology
Kansas State University