Solved

reading from an Oracle recordset

Posted on 2001-08-13
13
225 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

911 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now