Oracle To Access

[Arturo]

What are the possibilities of getting Oracle to
Access 2003. The IT guy set up a link but it
requires a password everytime the database
opens. What can Oracle output that Access
can simply link to and keep moving?

Thank you.

 

[Arvin Meyer [MVP]]

While it is possible to have the IT guy set up an ODBC link that passes the
password, that is somewhat insecure. You can protect the data in Oracle much
better by entering the password each time you connect.

 

[Arturo]

I know security is an issue. That is why I am seeking a better solution for
them and us. Here is the situation.

Every time an order prints it appends to the view created by the IT person.
(These are the linked tables that require the password when opening our
database.) We have skilled and unskilled users that use the database on our
side and entering a password every time they open the database could create a
work stoppage. We don’t want them to do anymore than open the program and use
it. This is not a good scenario, but it is what it is and that part is not
going to change.

I believe that if they create something, preferably with Access, they could
just keep dumping the information in the table, or whatever they create, and
be done with it. That is all I am trying to accomplish. All we need is the
data they pull out and they don't need to be involved with that data anymore.
That would eliminate the security issue.

The problem is they are not familiar with Access and therefore don’t usually
consider it as a solution anyway. If I can present something reasonable to
them, they will probably use it.

Do you have a solution for that? It would be greatly appreciated.

 

[Arvin Meyer [MVP]]

I've built several solutions which require data be obtained from the
accounting system. To alleviate IT concerns we created very narrow views
(queries) which dumped the data into a safe database file every night at 2
in the morning. The first user of the day, deletes his local data, and
imports the data from the nightly created file.

This will only work if the data is not particularly sensitive so that the IT
department feels comfortable with creating the "throw-away" file.

 

[Arturo]

Thank you again for your response. Let me explain the situation a little more.

The common system, Peoplesoft, prints orders for us. These orders print at
various intervals, seconds, minutes, etc. Sometimes they will print 20 to 30
orders at the same time. We currently enter these into our Access system
manually. The IT contact has set up a view in Oracle that we can connect to.
We can append these records to our database, which would eliminate our input
process. However, the contact said this is a live view and is updated by some
extraneous activities that we perform, like confirming that we have fulfilled
the order. When this happens, the order is removed from the view.

This can create problems because we need absolute control over the data.
That is the purpose of the program. We are trying to do a time study in a
very complicated system. If an employee takes an order before we assign it to
someone in our system, which they do periodically (that’s one issue we are
trying to resolve), and fulfill the order, we have no record of it.

So, what we need is for the IT person to create something that will update
in a “live†mode, and be done with it, giving us complete control over the
exported data. That is, create the object, a table, a view, or whatever, and
program the Oracle system to add any new records to that object. They don't
need to be involved after that.

Can it be done?

 

[Arvin Meyer [MVP]]

Yes, it can be done. What I think you want is a copy of the data that you
can manipulate as you wish. If you then need to update the PeopleSoft data,
you'll need to create a separate query, reconnecting your updated data to
either the existing view, or to a new one created by your IT folks, and do
an Update Query to return the changes to the Oracle database.

Initially, your query needs to just append the data to an Access table from
the View that has been created for you. All further data manipulation takes
place in Access, on Access data. Only the results are returned to Oracle,
and only if and when that is desired.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com