?
Solved

reading from an Oracle recordset

Posted on 2001-08-13
13
Medium Priority
?
237 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 280 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…
Suggested Courses
Course of the Month14 days, 20 hours left to enroll

839 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