RE: Access 2k3 Front End to Oracle 10g Back End design thoughts
- From: "Jerry Whittle" <JerryWhittle@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 27 Feb 2006 09:07:30 -0800
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.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
"Corey-g" wrote:
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
- Follow-Ups:
- References:
- Prev by Date: Re: Access: make checkboxes editable not just border
- Next by Date: Re: Find a Contact and prospect management database using Access
- Previous by thread: Access 2k3 Front End to Oracle 10g Back End design thoughts
- Next by thread: RE: Access 2k3 Front End to Oracle 10g Back End design thoughts
- Index(es):
Relevant Pages
|
