Trying to avoid "Bad Programming" - please help

[Corey-g via AccessMonster.com]

Hi all,

I have been reading through tons of posts, and many refer to poor design,
poor programming, etc... as the cause of the problems being addressed. I
would like to avoid being one of these people

What I'm trying to do is get opinions on the best way to build an application.
I need to develop a multi-faced app (order-entry / procurement / asset
management / shipping & receiving / rentals / and reporting on all of these)
that will be "used" by 5 - 7 users quite heavily and another 10 - 20 users
(occasionally) for various reporting & such. I HAVE to use Oracle as the
back-end, and Access for the front end. I have read many posts on this and
understand that it is not a trivial task, but also that it is do-able.

The users are required to log in, and each user will have a valid Oracle
account. The environment where this will be installed is quite locked down,
so the app front-end will be accessed using Citrix.

What is the smartest / best way to handle the database connection(s), and
queries? How can I have the users authenticate only once per session, and
can I keep the session open, or should it be closed each time? How do build
data entry forms - should I link the tables and build the queries, then
remove the links and save the SQL to use in code?

Any thoughts, tips, help, etc... will be greatly appreciated.

Corey

 

[RD]

Hi all,

I have been reading through tons of posts, and many refer to poor design,
poor programming, etc... as the cause of the problems being addressed. I
would like to avoid being one of these people

What I'm trying to do is get opinions on the best way to build an application.
I need to develop a multi-faced app (order-entry / procurement / asset
management / shipping & receiving / rentals / and reporting on all of these)
that will be "used" by 5 - 7 users quite heavily and another 10 - 20 users
(occasionally) for various reporting & such. I HAVE to use Oracle as the
back-end, and Access for the front end. I have read many posts on this and
understand that it is not a trivial task, but also that it is do-able.

The users are required to log in, and each user will have a valid Oracle
account. The environment where this will be installed is quite locked down,
so the app front-end will be accessed using Citrix.

What is the smartest / best way to handle the database connection(s), and
queries? How can I have the users authenticate only once per session, and
can I keep the session open, or should it be closed each time? How do build
data entry forms - should I link the tables and build the queries, then
remove the links and save the SQL to use in code?

Any thoughts, tips, help, etc... will be greatly appreciated.

Corey

Hi Corey,

Are you going to have a hand in designing the database? It's a royal PITA to
design to someone else's db. If you do read up on proper db design. It will
save you a lot of headaches down the road.

Plan a naming convention and STICK with it! There are plenty of resources
concerning naming conventions so you don't have to re-invent the wheel.

Keep tabs on this group. Many of the MVPs have their own sites with gobs of
code examples, articles, white papers, downloads and advice.
http://www.mvps.org/access/
http://www.rogersaccesslibrary.com/index.htm
http://home.bendbroadband.com/conradsystems/accessjunkie.html
http://www.heuveltop.nl/BasCB/msac_index.html
http://www.aylott.com.au/software.htm
http://www.trigeminal.com/
http://allenbrowne.com/tips.html
http://www.accessmvp.com/DJSteele/SmartAccess.html
http://www.accessmvp.com/DJSteele/AccessIndex.html

If I've forgotten anyone I very much apologize.

As far as connecting to Oracle, I had our dba create a special account for me.
I then created a data link file (UDL) which sits in the same directory as the
Access front end. I built a simple function who's only job is to write records.
I only open a connection when needed and close it immediately. Doing it this
way, the users don't need individual accounts, the front end handles the
database actions. Of course, that puts all the security on the Access front end
which, depending on your situation, might not be desirable.

Function fWriteRecord(sSql As String, sUDL As String) As String
' This function's only job is to take a SQL statement and a connection
' and write a record to the database

On Error GoTo ErrorHandler
Dim cnn As New ADODB.Connection

cnn.Open sUDL
cnn.Execute sSql
fWriteRecord = "True"

ExitPoint:
Set cnn = Nothing
Exit Function

ErrorHandler:
Debug.Print Err.Number & ": " & Err.Description
' Stop
fWriteRecord = "Error: " & Err.Number & " occurred in fWrite Record."
Resume ExitPoint
End Function


I hope this helps,
RD

 

[Corey-g via AccessMonster.com]

Thanks for your input RD!!!

You are using the UDL, so you have an Access project then, correct (I thought
that the UDL was done for projects & SQL Server - but Oracle could be used as
well)? I have set up a DSN-less connection, but I wasn't sure if I should
open the connection once and re-use (if possible) or close and open as
required. Also, I have been using ADO rather than DAO - does this matter?

I have already done the database design (I'm the only one working on this
project), and I have it done to 3rd normal form (although I would like an
opinion on one of my decisions - having a category table (ID, Name) and a
sub_category table (ID, Category_ID, Name). 29 tables in total, largest one
with 22 columns...

I would like to use the Oracle security over Access security, so is it
possible to do (that's wrong, I know it's possible, but just not sure the
best way to implement - My thought was to store and re-use the login info
every time I connected during the session). So, along that line - is it good
practice to use a hidden form, so that you have a module open all the time,
and variables in that module perstist as long as the app is open (for that
session)?

I would also like to build a single error handling routine that will insert
errors into a audit table. What is the smartest way to do this?

I think I have a mis-understanding of the modules - Access class object
modules (form modules) variable / procedures are only in scope as long as the
form is open - and Modules are available to any procedures, anytime - is that
correct? If so, then there is really no need for the hidden form, as I can
just use a module (basGlobal or something) to hold all of the functions I
want available to all procedures - like the error handling, and db
connections. Again, is this good or bad practice?

Thanks agaon,

Corey

 

[RD]

Well ... I'm no expert in good practice/bad practice. I'm still learning after
10 years. I'm sure that there are ways I'm not using good practices but I
do my best and adapt when I see a better way of doing something. One thing ...
Comment, Comment, Comment. Document your code. The first few years I was into
this I didn't comment my code very much and it would always jump up and bite me
in the butt. I comment my code A LOT now.

So, yes I am using a UDL for this particular application but your use of a
DSN-less connection works. I've done it both ways for Oracle, Sybase and SQL
Server in both Access and straight VB applications. You've already said that
each user has their own account so security should be easy.

As far as a connection, it's my understanding that leaving a persistent open
connection over the network is bad juju, especially if you're going to have
several people using it at the same time. I've never done it, myself. I open a
connection when I need it and close it when I'm done. Keeps the network support
wonks happy.

The code I provided in my last message was ADO. I use DAO for Jet and ADO for
everything else.

I'll have to leave it to others to respond to the idea of a single error
handling routine.

In a module, if you don't declare a procedure Private then yes, it's available
to other procedures. I don't usually throw all of my procedures in a single
module, though. I like to group them according to similar functionalities and
name the modules something appropriate. When I make use of APIs I usually put
them in a module of their own. Were I building something like you you describe
I would probably have separate modules for error handling, record handling,
reporting, etc.

Is any of this good/bad practice? It's worked for me for years in both solitary
and team oriented projects. I've been asked to join our Application Development
Standards Committee and a co-worker (a definite killer coding ninja monkey) has
said he'd rather have me on his team than the folks he's got.

Kind of a can of worms. What is good practice for one coder might be rubbish to
another. Like I said before, keep up with this group and check out the sites I
provided links for. If you get in a bind, ask a question. There is plenty of
help around.

Cheers,
RD