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

dqmq

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#

OP_Zaharin

- 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.

shilpi2

ASKER

can i also write a custom sql query to transform my data between oracle and access tables?

OP_Zaharin

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

shilpi2

ASKER

sorry which link?

OP_Zaharin

- 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.

shilpi2

ASKER

ok thx

ASKER CERTIFIED SOLUTION

dqmq

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer