Link to Oracle without Using ODBC

[JesseH]

I would like to access an Oracle instance from MS Access without using ODBC. Our company's security team does not allow ODBC to be installed and/or used. Is there a way to do this? Read only access is all that is required. I would like to launch a sql statement that would create a table that I could read from MS Access. We have TOAD, MS Access and Excel available to us. Perhaps, I could launch a TOAD sql command from MS Access.

Has anybody does this? Examples would be greatly appreciated. MS Access does not pose a problem. I am a newbie to Oracle.

Thanks in advance for any help you can provide.

 

[CJ_London]

you need to use a connection string - see this link for some examples

https://www.connectionstrings.com/oracle/

I can't quite remember how the sql is constructed but it is something like

SELECT * FROM myTable IN ConnectionString

 

[Galaxiom]

I don't think there is any way around the policy.

Even using the connection string as suggested by CJ, the driver for Oracle databases must still be installed on the client.

 

[JesseH]

Thanks for trying.

 

[mienmobk]

this is topic i am studying in my project

 

[The_Doc_Man]

Your problem becomes political. The question is, who is asking for this project? THAT person has the onus of getting past security policy. If this idea is yours, I'd say you are stuck with no immediate hope. But if someone high enough in the company wants it, they have the ability to prod the IT team into being more amenable.

Sometimes you have to work with politicians in a company. Leave them to do the political haranguing and you stay with the techie stuff. That way, everyone (perhaps except the IT security folks) will be happy. But... having worked with U.S. Dept. of Defense IT security, they aren't usually that happy anyway so it's no biggie.

 

[jdraw]

mienmobk, DocMan

I deleted a response when I saw that the base post was about 18 months old. However, I'll repeat my idea here since the thread is active.
It wasn't clear (to me anyway) that online access was necessary since the requirement was for read only data. And it's been years since I worked with Oracle, but I know there were utilities to import/export tables/views etc from Oracle. So if read only was required, such a file(s) could be made available for reading even though it would have some time delay from the online system.

I agree with DocMan that IT database/security can seem to be a world unto itself --but there are often good reasons. And, as he said, if the requirement to access online data originated with management, the powers to be will evaluate and make it happen.

From experience, I also worked in a federal government organization. After a multi department merger we developed online, integrated corporate database and applications. The subject matter of relevance here is the application and approval of grants and loans to various people/orgs distributed across the country. Previously, there were individual applications typically batch mode where officers could get reports on actual vs projected spending; and using their experience could revise projections based on take up. However, with the online and integrated systems, the actual spend numbers were available instantly - and changing by the minute or second from across the country. The data was or could change at anytime; there was no longer a wait time whereby officers could "adjust projections".
Some areas, based on their previous work practices, needed online access to the data.

This need was reviewed. The solution was to extract data from the online system at the month-end, quarter-end, year-end and fiscal year-end and make it available Read Only. The data in each of the "files" was consistent (not changing instantly as the online system did). Officers could run multiple reports, queries, what-if scenarios etc repeatedly and get the info needed for their work. This extracted data and the major associated processing was known as the Management Reporting Database.

All this to say that for read only access to data, there may be some extract options.

Good luck.