CODEWITHSUNDEEP
sqloracle-databasems-accessjoinoracle-sqldeveloper
Mike
Mike

Reputation: 4405

Join MS Access Table to Oracle table

I'm playing around with a table in an MS Access database. The table has a primary key of CLIENT_NUMBER. My corporation maintains an Oracle database that has a table which contains clients contact information (address, phone numbers, emails, etc). It also has the CLIENT_NUMBER field. I got to thinking that maybe I can join the 2 tables from the different databases and run some queries. I dug around on the net and I couldn't really find any reference, so I think this is a long shot and a silly question, but is that possible? Maybe through a DB link or something? For reference, I use SQL Developer 3.2.xx for sql developing.

Upvotes: 0

Views: 777

Answers (1)

O. Gungor
O. Gungor

Reputation: 768

I would copy the table in oracle to Access using what's called a sqlpassthrough query in Access. linked tables to oracle in my experience, perform very poorly, and if you are also thinking about joining to a local table in Access, probably much worse.

Passthrough queries are very quick since Access simply just sends the query for execution to the target server/database based on the connection you identify for the passthrough query, hence the name "pass-through".

The driver in the connect string may not work for you, and it may need more info depending on how things are setup in your environment, so you will have to work that out.

  'creates the passthrough query to oracle
  With CurrentDb.CreateQueryDef("qOracleConn")
     .Connect = "ODBC;Driver={Microsoft ODBC for Oracle};Server=oracleservername;Uid=oracledbusername;Pwd=oracledbpassword;"
     .sql = "SELECT * FROM tableinoracle"
  End With

'creates the local table in access
CurrentDb.Execute "SELECT * INTO OracleClients FROM qOracleConn"

Upvotes: 1

Related Questions