<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 5.5.2652.35">
<TITLE>RE: [AccessD] slightly OT: Oracle and ADO</TITLE>
</HEAD>
<BODY>
<P><B><I><FONT SIZE=2>---- London Borough of Bromley E-Mail Disclaimer ----</FONT></I></B><I></I>
<BR><FONT SIZE=2>"For information about Bromley Council visit our web site www.bromley.gov.uk"</FONT>
</P>
<P><FONT SIZE=2>"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." </FONT></P>
<P><B><I><FONT SIZE=2>---- End of Disclaimer ---- </FONT></I></B>
</P>
<P><FONT SIZE=2>Seth,</FONT>
</P>
<P><FONT SIZE=2>Having looked at the solution below and the situation I'm in...</FONT>
</P>
<P><FONT SIZE=2>I have about 15 tables which need to be updated in this way, some of which have dozens of fields. Is there a quicker way to batch insert from a recordset into a table, or must it be done field by field and record by record as you have below?</FONT></P>
<P><FONT SIZE=2>Ryan</FONT>
</P>
<P><FONT SIZE=2> -----Original Message-----</FONT>
<BR><FONT SIZE=2>From: Seth Galitzer [<A HREF="mailto:sgsax@ksu.edu">mailto:sgsax@ksu.edu</A>] </FONT>
<BR><FONT SIZE=2>Sent: 25 April 2003 14:39</FONT>
<BR><FONT SIZE=2>To: accessd</FONT>
<BR><FONT SIZE=2>Subject: RE: [AccessD] slightly OT: Oracle and ADO</FONT>
</P>
<P><FONT SIZE=2>---- London Borough of Bromley Security Notice ---- </FONT>
<BR><FONT SIZE=2>"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."</FONT></P>
<P><FONT SIZE=2>---- End of Notice ----</FONT>
</P>
<P><FONT SIZE=2>Ryan,</FONT>
</P>
<P><FONT SIZE=2>I know this can be done by manually stepping through the recordset and</FONT>
<BR><FONT SIZE=2>adding the records to the internal table. I don't know if there's a way</FONT>
<BR><FONT SIZE=2>to do a mass dump from a recordset to a table, but doesn't mean that</FONT>
<BR><FONT SIZE=2>there isn't. Here's how you'd do it manually (inserted after the</FONT>
<BR><FONT SIZE=2>connection code I posted earlier):</FONT>
</P>
<P><FONT SIZE=2>'begin</FONT>
<BR><FONT SIZE=2>Dim rst as ADODB.Recordset</FONT>
</P>
<P><FONT SIZE=2>rst.Open "SELECT * FROM my_table;", conn</FONT>
<BR><FONT SIZE=2>rst.MoveFirst</FONT>
<BR><FONT SIZE=2>While Not rst.EOF</FONT>
<BR><FONT SIZE=2> ' build comma-seperated list of values using rst(fieldname)</FONT>
<BR><FONT SIZE=2> CurrentDb.Execute "INSERT INTO tblMyAccessTable " & _</FONT>
<BR><FONT SIZE=2> "(<list of field names>) VALUES (<list of values from above>);"</FONT>
<BR><FONT SIZE=2> rst.MoveNext</FONT>
<BR><FONT SIZE=2>Wend</FONT>
<BR><FONT SIZE=2>'end</FONT>
</P>
<P><FONT SIZE=2>Since you already need to have the Oracle ODBC drivers installed to make</FONT>
<BR><FONT SIZE=2>this connection in the first place, why not just link to the ODBC source</FONT>
<BR><FONT SIZE=2>directly? If you did this, then you could just run an append query from</FONT>
<BR><FONT SIZE=2>one to the other.</FONT>
</P>
<P><FONT SIZE=2>Seth</FONT>
</P>
<BR>
<P><FONT SIZE=2>On Fri, 2003-04-25 at 04:15, Smethurst, Ryan wrote:</FONT>
<BR><FONT SIZE=2>> </FONT>
<BR><FONT SIZE=2>> Seth/Jack,</FONT>
<BR><FONT SIZE=2>> </FONT>
<BR><FONT SIZE=2>> This is slightly OT, but reading below it looks like you two have done</FONT>
<BR><FONT SIZE=2>> something similar to what I am attempting. </FONT>
<BR><FONT SIZE=2>> </FONT>
<BR><FONT SIZE=2>> Once the connection has been established in code, is it possible to create a</FONT>
<BR><FONT SIZE=2>> recordset from that Oracle connection and then create a table in Access</FONT>
<BR><FONT SIZE=2>> based on those records?</FONT>
<BR><FONT SIZE=2>> </FONT>
<BR><FONT SIZE=2>> TIA</FONT>
<BR><FONT SIZE=2>> RyanS</FONT>
<BR><FONT SIZE=2>> </FONT>
<BR><FONT SIZE=2>> -----Original Message-----</FONT>
<BR><FONT SIZE=2>> From: Seth Galitzer [<A HREF="mailto:sgsax@ksu.edu">mailto:sgsax@ksu.edu</A>] </FONT>
<BR><FONT SIZE=2>> Sent: 24 April 2003 22:48</FONT>
<BR><FONT SIZE=2>> To: accessd</FONT>
<BR><FONT SIZE=2>> Subject: RE: [AccessD] slightly OT: Oracle and ADO</FONT>
<BR><FONT SIZE=2>> </FONT>
<BR><FONT SIZE=2>> Jack,</FONT>
<BR><FONT SIZE=2>> </FONT>
<BR><FONT SIZE=2>> Here's a snippet of what I ended up using:</FONT>
<BR><FONT SIZE=2>> </FONT>
<BR><FONT SIZE=2>> 'begin</FONT>
<BR><FONT SIZE=2>> Dim conn As ADODB.Connection</FONT>
<BR><FONT SIZE=2>> </FONT>
<BR><FONT SIZE=2>> Set conn = New ADODB.Connection</FONT>
<BR><FONT SIZE=2>> conn.ConnectionString = "Provider=OraOLEDB.Oracle;" & _</FONT>
<BR><FONT SIZE=2>> "Data Source=my.server.address.com;" & _</FONT>
<BR><FONT SIZE=2>> "User ID=username;Password=password;"</FONT>
<BR><FONT SIZE=2>> conn.Open</FONT>
<BR><FONT SIZE=2>> 'end</FONT>
<BR><FONT SIZE=2>> </FONT>
<BR><FONT SIZE=2>> Once you have that connection open, you can use it for any other action</FONT>
<BR><FONT SIZE=2>> until you close it. Note the "Data source" parameter can be an IP</FONT>
<BR><FONT SIZE=2>> address, and possibly even a NetBIOS name if you are confident in the</FONT>
<BR><FONT SIZE=2>> ability for your workstations to resolve those consistently.</FONT>
<BR><FONT SIZE=2>> </FONT>
<BR><FONT SIZE=2>> Seth</FONT>
<BR><FONT SIZE=2>> </FONT>
<BR><FONT SIZE=2>> On Thu, 2003-04-24 at 16:11, Drawbridge.Jack@ic.gc.ca wrote:</FONT>
<BR><FONT SIZE=2>> > Seth: Did you ever get this resolved? We are seeking info on </FONT>
<BR><FONT SIZE=2>> > ODBCDirect with DAO (access 2000) talking to Oracle 9i. We're trying to</FONT>
<BR><FONT SIZE=2>> get</FONT>
<BR><FONT SIZE=2>> > to Oracle without the ODBC prompt. Can we do this with code (hopefully</FONT>
<BR><FONT SIZE=2>> with</FONT>
<BR><FONT SIZE=2>> > some code and logic you have discovered)?</FONT>
<BR><FONT SIZE=2>> > </FONT>
<BR><FONT SIZE=2>> > Thanks.</FONT>
<BR><FONT SIZE=2>> > </FONT>
<BR><FONT SIZE=2>> > jack</FONT>
<BR><FONT SIZE=2>> > </FONT>
</P>
<P><FONT SIZE=2>-- </FONT>
<BR><FONT SIZE=2>Seth Galitzer sgsax@ksu.edu</FONT>
<BR><FONT SIZE=2>Computing Specialist <A HREF="http://puma.agron.ksu.edu/~sgsax" TARGET="_blank">http://puma.agron.ksu.edu/~sgsax</A></FONT>
<BR><FONT SIZE=2>Dept. of Plant Pathology</FONT>
<BR><FONT SIZE=2>Kansas State University</FONT>
</P>
<P><FONT SIZE=2>_______________________________________________</FONT>
<BR><FONT SIZE=2>AccessD mailing list</FONT>
<BR><FONT SIZE=2>AccessD@databaseadvisors.com</FONT>
<BR><FONT SIZE=2><A HREF="http://databaseadvisors.com/mailman/listinfo/accessd" TARGET="_blank">http://databaseadvisors.com/mailman/listinfo/accessd</A></FONT>
<BR><FONT SIZE=2>Website: <A HREF="http://www.databaseadvisors.com" TARGET="_blank">http://www.databaseadvisors.com</A></FONT>
</P>
</BODY>
</HTML>