Solved

reading from an Oracle recordset

Posted on 2001-08-13
13
229 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
[X]
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
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
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…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

691 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