[AccessD] slightly OT: Oracle and ADO

---- London Borough of Bromley E-Mail Disclaimer ---- 
"For information about Bromley Council visit our web site
www.bromley.gov.uk" 

"The information contained in this message (including any attachments) is
confidential in that it is intended solely for the use of the recipient to
which it is addressed. If you are not the intended recipient, the use of the
information by disclosure, copying or distribution is prohibited and may be
unlawful." 
---- End of Disclaimer ---- 

Thanks Seth...
That'll be a great help!
RyanS

 -----Original Message-----
From: 	Seth Galitzer [mailto:sgsax at ksu.edu] 
Sent:	25 April 2003 14:39
To:	accessd
Subject:	RE: [AccessD] slightly OT: Oracle and ADO

---- London Borough of Bromley Security Notice ---- 
"Please treat all e-mail with caution. Only open attachments from trusted
business sources whose material you are sure is safe. Refer to the Council's
e-mail and internet users Code of Conduct for further guidance on the
correct use of the e-mail system."

---- End of Notice ----

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

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030425/c614a2c1/attachment-0001.html>