Access 2k3 Front End to Oracle 10g Back End design thoughts

[Corey-g]

Hi All,

I have been asked to develop an app that will use Access as the FE,
connecting to Oracle BE. I will need to install this at a location where
users will not have Access installed, and are unable to install anything
(extremely locked down environment - can write to local C;\ drive, but can't
touch the registry).

I was reading through all of the posts regarding connecting Access to Oracle,
but I didn't see any posts similar to what I am working on. I have put some
thought into how I can make this work, and was hoping some-one might be able
to qualify my thoughts or point me in the right direction...

First off, as I can't install the app (touch the registry), or create a DSN,
I had thought I could do all connecting via VBA. (This is where I started to
have questions - can I create a connection without linking tables? And if so,
can I just use pass-through queries and recordsets - rather than linking
tables directly?) Should also add that I am using ADO rather than DAO (Does
this matter - is it possible?).

So, If I can create a link to the Oracle BE without linking the tables, am I
able to link all the forms / reports etc... to recordsets returned from
Oracle? Will I be able to make updates to the data in the BE?

I had figured that if I create an Access runtime MDE, and was able to make
the DB connection via code, I could overcome the infrastructure issues. Does
that sound right?

Here is how I thought it might flow:
1. User launches MDE
2. Prompt for UID & PWD
3. Store these for duration of application session
4. Connect to Oracle BE (using supplied UID & PWD - should every user have
valid Oracle account?) & validate UID & PWD - return User Type (for privledge
level)
5. Based on type returned, show appropriate menu / items
6. When user clicks on link to data-entry, reports, etc..., get a recordset
from Oracle using VBA and link to the form / report.

Is this how I should be doing this? Also, in #4 the connection to the BE
should not have 1 UID / PWD for all connections to the DB as this is bad
security - correct?

Thanks in advance for any help / thoughts / comments.

Corey

 

[Jerry Whittle]

In the sage advice of Monty Python "Run away! Run away!". This project has
more minefields in it than Iraq. First of all you must install something to
get the MDE to work and this will require registry changes. Linking Access to
Oracle is difficult, but do-able; however, how are you going to test the
connections without setting up a mirror of the Oracle database?

IMHO as both an Oracle DBA and long time Access developer, this project
should be done using Oracle forms and reports or maybe some other web-based
solution. If they are trying to get off cheap, it's going to be costly. I bet
the fingers point at you.

 

[Corey-g]

Thanks Jerry, I appreciate your opinion. I have been feeling that this isn't
the best solution, but as they already had the software...

I think I may be better off using APEX (formerly HTML DB), that is now
provided with Oracle. This way I don't have to install anything, the user
just needs a web browser.

Corey

 

[Corey-g via AccessMonster.com]

Hi again,

To overcome the installation issues, it has been suggested to use Citrix. I
have talked with a couple of Citrix people, and they have seen runtime apps
run well this way. So I am going to continue developing this using Access as
the FE, and Oracle as the BE.

So now that I have been told about all of the "Mine Fields", can anybody
offer any suggestions to help avoid these?

I have been thinking about the connections to the BE, and what is the best
way to handle connecting securely (and reliably). As I posted earlier, any
hints / advice before I begin would be greatly appreciated.

I thought that I should use a small hidden form to be opened during startup,
so that I had a code module open to work from. Does this make sense? What
is the best way to implement (I think using the DSN-less connection & ADO is
the route I was going to take) this type of app?

Thanks again,

Corey

 

[Corey-g via AccessMonster.com]

Bump...

Your thoughts would still be greatly appreciated. How do you handle client
server systems?

Corey