Access to Oracle

[tcb]

1) The client.mdbs are on desktops. The user's are networked. The
table.mdb is on a server. One user occasionally needs to query an
Oracle database to append data to the table.mdb. The other users don't
need or have rights to do this. On my testclient.mdb I have links to
the Oracle tables (MS ODBC for Oracle DSN and an Oracle client) which
are only open when the user needs to call upon them. He is prompted
for his username and password, an append query runs, the recordset is
closed.

Is there any performance hit or any other problems to be encountered by
the other users if all of the client.mdbs have that inactive link to
the Oracle tables? Or should I have two front ends?

2) The call to the Oracle database is done through DAO, which is what
I am familiar with. It seems to work just fine. Is there any
advantage to doing this in ADO?

 

[Arvin Meyer [MVP]]

1) The client.mdbs are on desktops. The user's are networked. The
table.mdb is on a server. One user occasionally needs to query an
Oracle database to append data to the table.mdb. The other users don't
need or have rights to do this. On my testclient.mdb I have links to
the Oracle tables (MS ODBC for Oracle DSN and an Oracle client) which
are only open when the user needs to call upon them. He is prompted
for his username and password, an append query runs, the recordset is
closed.

Is there any performance hit or any other problems to be encountered by
the other users if all of the client.mdbs have that inactive link to
the Oracle tables? Or should I have two front ends?

All linked databases affect performance somewhat. It may be a small amount,
but theu only way to tell is with a test. If you are bothering to make a
second db for the test, I'd just go ahead and make the second db. Keep your
master as the one with the links, and just delete the link for everyone
else.

2) The call to the Oracle database is done through DAO, which is what
I am familiar with. It seems to work just fine. Is there any
advantage to doing this in ADO?

I've never found any advantage except with some SQL-Server views. I use DAO
whenever I can.

 

[tcb]

Thanks, I'll follow your suggestions.

Another question. When running the query (runSQL that is) that
downloads from Oracle the user is prompted for user name, password,
DSN. After the user runs the query, a live link is no longer
necessary. How can I "disconnect", deactivate the link (or whatever it
might be called)?

 

[Arvin Meyer [MVP]]

Thanks, I'll follow your suggestions.

Another question. When running the query (runSQL that is) that
downloads from Oracle the user is prompted for user name, password,
DSN. After the user runs the query, a live link is no longer
necessary. How can I "disconnect", deactivate the link (or whatever it
might be called)?

Disconnect from the session. With an Access front-end, you simply close the
database. Most database applications will close in a few seconds and open in
a similar period of time. If you have compact on close set, it may add up to
4 or 5 seconds.