Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

reading from an Oracle recordset

Posted on 2001-08-13
13
227 Views
Last Modified: 2012-06-27
Hi!,
I have a ADOBD recordset that i have populated with some data and would like to read or copy its contents into an Accesstable.What is the best way of doing this. I ma using Access 2000.
Thanks in advance,
0
Comment
Question by:kitso
  • 6
  • 2
  • 2
  • +3
13 Comments
 

Expert Comment

by:RJOHNS621
ID: 6379737
The easiest way to get the data into Access is to link the table via ODBC.  First create a new ODBC source for the table.  Next go to Access and open a new table.  On the file menu choose Get External Data.  Choose "link table" and select the ODBC source that you have just created.  Access will display the table data
0
 

Author Comment

by:kitso
ID: 6379797

Hi!,
Thanks but the reason why i am doing it this way is because i tried the ODBC way and linked to the Orcale table straight away but it was not reliable sometimes it worked others it did not.And i was always getting an ODBC call fail error.

Thanks
0
 
LVL 5

Accepted Solution

by:
KMAN earned 70 total points
ID: 6379839
Use INSERT INTO SQL statement:
Syntax: INSERT INTO tablename(fieldname1, fieldname2) VALUES(value1, value2)

1). Create a recordset on Oracle table
2). Use a Do Loop or For Each or... to iterate the Oracle records
3). Execute an Insert SQL for the records
4). Repeat til no more records
...

Example:
rst.MoveFirst
Do until rst.EOF = True
   strSQL = "INSERT INTO tablename(fieldname1, fieldname2) VALUES('" & rst!value1 & "','" & rst!value2 & "')"
   CurrentDb.Execute strSQL
   rst.MoveNext
Loop

HTH, K
0
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 5

Expert Comment

by:KMAN
ID: 6379857
Amended:

Use INSERT INTO SQL statement:
Syntax: INSERT INTO tablename(fieldname1, fieldname2) VALUES(value1, value2)

1). Create a recordset on Oracle table
2). Create a connection or database collection for Access DB.
3). Use a Do Loop or For Each or... to iterate the Oracle records
4). Execute an Insert SQL for the records
5). Repeat til no more records
...

Example:
...
'ADO connection to access ODBC Datasource example
conn.ConnectionString = "DSN=accessDB"
conn.open
...
rst.MoveFirst
Do until rst.EOF = True
   strSQL = "INSERT INTO tablename(fieldname1, fieldname2) VALUES('" & rst!value1 & "','" & rst!value2 & "')"
   conn.Execute strSQL
   rst.MoveNext
Loop
...
conn.close
set conn = nothing
...

HTH, K
0
 

Author Comment

by:kitso
ID: 6379879

Hi!,
Thanks but the reason why i am doing it this way is because i tried the ODBC way and linked to the Orcale table straight away but it was not reliable sometimes it worked others it did not.And i was always getting an ODBC call fail error.

Thanks
0
 

Author Comment

by:kitso
ID: 6379961

Hi!,
Thanks but the reason why i am doing it this way is because i tried the ODBC way and linked to the Orcale table straight away but it was not reliable sometimes it worked others it did not.And i was always getting an ODBC call fail error.

Thanks
0
 

Author Comment

by:kitso
ID: 6379962

Hi!,
Thanks but the reason why i am doing it this way is because i tried the ODBC way and linked to the Orcale table straight away but it was not reliable sometimes it worked others it did not.And i was always getting an ODBC call fail error.

Thanks
0
 

Author Comment

by:kitso
ID: 6379964

Hi!,
Thanks but the reason why i am doing it this way is because i tried the ODBC way and linked to the Orcale table straight away but it was not reliable sometimes it worked others it did not.And i was always getting an ODBC call fail error.

Thanks
0
 

Author Comment

by:kitso
ID: 6383180
Thanks  alot,
Sorry for my earlier comment appearing more than once.There was an error when i tried to post the comment.

Thanks
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6385172
Your question posted in duplicate, typically the result of hitting resubmit or F5/refresh.

Moondancer
Community Support Moderator @ Experts Exchange
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7095078
Recommended disposition:

    Accept KMAN's comment(s) as an answer.

DanRollins -- EE database cleanup volunteer
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 7095564
Here is the other one you created, and is also still open today.
http://www.experts-exchange.com/visualbasic/Q_20167509.html

They were duplicate questions, but experts apparently didn't know it and helped you in both of them.

Please advise.
Moondancer - EE Moderator
0
 
LVL 5

Expert Comment

by:Netminder
ID: 7213360
Per recommendation, force-accepted.

Netminder
CS Moderator
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question