Avatar of shilpi2
shilpi2

asked on 

Get data from oracle to access

Hello Experts,

I want to connect some access tables to an oracle view and define a query to do some small conversions on data for transfer to access. How can I do this ? Can this be automated or I need to write some code in C sharp for this?

Thanks

Shilpi
Microsoft AccessDB2

Avatar of undefined
Last Comment
dqmq
Avatar of dqmq
dqmq
Flag of United States of America image

In the External Data menu, you import the view into an Access table or you can link to a view that acts like a table. Then you can run queries and/or VBA code to manipulate the data as you see fit.   It can be completely automated.  No C#  
Avatar of OP_Zaharin
OP_Zaharin
Flag of Malaysia image

- you can do so by using Access-Import via ODBC feature: 
: assuming you have oracle client with oracle odbc driver installed in your pc. setup system DSN to Oracle database in the ODBC Data Source (Control Panel > Administrative Tool > ODBC Data Source Administrator > System DSN tab > add Oracle driver). test the connection.
: in Access, click External Data menu > More > ODBC database > select "Import the data source data into a new table.." and click OK button
: a Select Data Source dialog windows will pop up. click on Machine Data Source tab and select the Oracle DSN name you created earlier. ket-in the pasword when prompted.
: an Import Object dialog will be pop-up, select the requierd table/view. click Ok. the table/view will then imported to Access.
Avatar of shilpi2
shilpi2

ASKER

can i also write a custom sql query to transform my data between oracle and access tables?
Avatar of OP_Zaharin
OP_Zaharin
Flag of Malaysia image

- based on the sample in the link i gave you, i believe you can.
Avatar of shilpi2
shilpi2

ASKER

sorry which link?
Avatar of OP_Zaharin
OP_Zaharin
Flag of Malaysia image

- oh i'm so sorry. got confuse with another question (its 2am here).
- no with this approach you can't. i would suggest you to create a view in oracle using the query that you have.  then based on my approach you can import that view.
Avatar of shilpi2
shilpi2

ASKER

ok thx
ASKER CERTIFIED SOLUTION
Avatar of dqmq
dqmq
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Microsoft Access
Microsoft Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo