VBA Development questions

[Corey-g via AccessMonster.com]

Hi All,

I am creating a Access 2K3 front-end to an Oracle 10g back-end. From all of
the reading I have done, this is not as easy as it sounds. From that, I
started to think about the best way to design the application.

I figured that I should have a form load at start-up but set its visiblity to
false (this would give me a module that could be kept open until the app is
closed). This form would then use VBA to load the login form, and store the
UID and PWD for the session (eliminating the multiple ODBC Connection windows
I keep getting). Then, I figured I would use a DSN-less connection, and ...

Okay, I'm not sure where I would go next, which is why I'm posting. Do i
need to open / close this connection each time I need a query run or to
update a record? Also, can this be done using ADO, or does it need to be
DAO, or does it matter?

What is the smartest way to accomplish this? I am going to run the front-end
as an MDE over terminal services (Citrix) if possible.

Any thoughts / hints would be great, and a step by step example would be
hugely appreciated if someone is feeling really ambitious...

TIA,

Corey

 

[Corey-g via AccessMonster.com]

Okay,

Can anyone at least explain how to implement a 'global' module that will
store the users credentials, and handle the connection to the back-end
database?

My thought is to use the hidden form, but is this the best way? And how do I
implement this?

Start with the hidden form, and have it launched as the 'start-up' form.
Load the login form from VBA, and use an on_click event on the "login" button
to call a connection procedure in the hidden forms module? How do I declare
the user id and password variables - as constants or static (can I set a
static variable dynamically) - and set them in the connection procedure? Do
I open and close the connection each time, or leave the connection open until
the user quits the app, and then call a 'close connection' procedure?

Any input would be appreciated.

Thanks in advance,

Corey
PS: I have written the DSN-Less connection string and it works, but I
haven't built an Access front end in quite some time, so I hoped to get some
of your opinions before I went too far down a wrong path...