Solved

reading from an Oracle recordset

Posted on 2001-08-13
13
228 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
Technology Partners: 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
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 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…
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…

726 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