Access 2000 and Transactional Processing

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

This might not be the correct group, but the best answers always seem to be
here.

Does Access 2000 support transactional processing? If so, does anyone know
of a good information source; book, web site etc, which covers this?

Maintaining data integrity in a screen involving 3 related tables has been
very difficult. Users have learned to avoid the data checks which prevent
them from exiting the screen by simply shutting down Access itself. The way
Access automatically updates and saves records would seem to make make
ENDING and ROLLING BACK transactions difficult, since Access wants to
update tables in its own fashion.

Any advice on this topic would be greatly appreciated.

Many thanks
Mike Thomas
 
AFAIK you're right -
Bound Access forms don't support transactions, although you can define
transactions if you're using DAO or ADO directly.

Data integrity across tables is often well enforced by defining
relationships among your tables, and enforcing relational integrity. Then
Access will refuse to save data which "breaks the rules".

Where are your "data checks which prevent them from exiting the screen"?
If you run them in the form's Unload event, you have the option of setting
Cancel=True
which will prevent the form from unloading, and thus prevent Access from
closing.

But let me add at the end what I usually put at the beginning -
if you have users who are intentionally entering incorrect data, this
may well be more of an HR problem than an IT one.

HTH
- Turtle
 
Thanks Turtle, I was afraid of that. My other battle is trying to get this
customer out of Access and into SQL server.

The data checks are in a procedure defined for each form. They are run not
when the user tries to exit the screen, but whenever they might need to move
off the current record. In this particular case, it is happenning when
parts come in and must be entered, but the users need to go to a third
company for costing, and this third company cannot deliver the info on the
spot. It's a situation which would seem ontolerable, but it is fairly
common.

I may try the referential integrity route, but I think it might cause more
problems than it solves.

Many thanks
Mike Thomas
 
It sounds as if you may have an application design (requirements) problem
here -

What is the preferred method of dealing with the situation where the parts
come in, but the costing information is not available?
Should the entry not be made until the costing is available?
Or should the application permit users to enter parts without costing data?
If it were my business (which of course it isn't) I'd want the parts to
be entered into the system ASAP, with allowance for the costing information
to be added later.
I might even appreciate a separate switchboard or menu item which would
provide a convenient way for adding the costing information when it becomes
available.

BTW -
what's your motivation in wanting to move to SQL Server?

- Turtle
 
Mike said:
Thanks Turtle, I was afraid of that. My other battle is trying to
get this customer out of Access and into SQL server.

The data checks are in a procedure defined for each form. They are
run not when the user tries to exit the screen, but whenever they
might need to move off the current record. In this particular case,
it is happenning when parts come in and must be entered, but the
users need to go to a third company for costing, and this third
company cannot deliver the info on the spot. It's a situation which
would seem ontolerable, but it is fairly common.

I may try the referential integrity route, but I think it might cause
more problems than it solves.

Many thanks
Mike Thomas
Click to expand...

It sounds like the procedures should be changed to allow a graceful exit,
certainly safer than just closing down.
Some field could be filled with "Incomplete" and reports run to find which
ones had missing items.

This sounds like a program with a "good idea" in it. One which was not
discussed with the people who actually did the work.

More than once I've heard staff say to management "That's nice, but we don't
do it that way."
 
Maintaining data integrity in a screen involving 3 related tables has
been
very difficult. Users have learned to avoid the data checks which prevent
them from exiting the screen by simply shutting down Access itself.
Click to expand...

If the user tryies to navagnoe to a new reocrd, or navagatote to a existing
roecrd, or hits the "x" to close down ms-access, if you have correct field
verifity code, then users CAN NOT save the current roecrd. It is not clear
why, or how your users are getting around your "verify" code..but they
should not be able to.

I use hte follwing in a form to force certanly fields..and users CAN NOT
exit by shutting down ms-access. I mena, they can pull the plug on their
coputer, or do a ctrl-alt-del..and kill ms-access in the task manager..but
then again..their rocord will NOT be saved

Here is the code I use to force certain fields....

In the forrms before update event I go:

Cancel = MyVerify

I then paste in the folwling code into the forms module:

Private Function MyVerify() As Boolean

' just add each contorl that you want as required
' and a text msg you want to display

Dim colFields As New Collection

MyVerify = False

colFields.Add "TourDate,Tour date"
colFields.Add "Description,Description"
colFields.Add "City,City"
colFields.Add "cboProvince,Province"
colFields.Add "StartDate,Start date"
colFields.Add "EndDate,end date"

MyVerify = vfields(colFields)


End Function

Private Function vfields(colFields As Collection) As Boolean

Dim strErrorText As String
Dim strControl As String
Dim i As Integer

vfields = False

For i = 1 To colFields.Count
strControl = Split(colFields(i), ",")(0)
strErrorText = Split(colFields(i), ",")(1)
If IsNull(Me(strControl)) = True Then

MsgBox strErrorText & " is required", vbExclamation, AppName
Me(strControl).SetFocus
vfields = True
Exit Function
End If
Next i


End Function

So, you can see how I just add controls to that list of code that I want
"force" data entry for. Users cannot get around the above by trying to close
ms-access.
 
Thanks for your input, Turtle.

To answer your first question, parts entered with a zero price tend to cause
problems down the road when they go out again. We can catch them at that
time and essentially go through the same excercise - get the cost from the
supplier and get the cutomer to agree, and in the end, that's what we will
do. Preventing zero price parts is more of an issue with my customer than
with me.

My motivation for wanting to move to SQL Server is;

- I want to get away from bound forms - this is an extermely complicated
application - the front end is up to about 20 meg. In my opinion, unbound
forms and transaction processing is the best way to insure data integrity.

- The back end database is getting pretty large and slow - there are two and
each is up to 150 meg and growing. A year ago they were about 30 each. SQL
Server would be faster.

- There are getting to be a lot of users - about 50 now, and again, I don't
like Access managing all of the updates.

- Security is getting to be an issue and I think Access security is rather
weak.

- If we go to the trouble of using unbound forms in Access, we should go the
next step and get a more solid front end. See next.

- Corruption in the front end is getting to be more of an issue.

Pretty windy eh? If you can talk me out of wanting to get away from
AccessI'd appreciate it. I stay awake some night wondereing when the whole
thing is going to bomb.

Mike Thomas
 
Comments interspersed

Mike said:
Thanks for your input, Turtle. You're welcome.

To answer your first question, parts entered with a zero price tend to cause
problems down the road when they go out again. We can catch them at that
time and essentially go through the same excercise - get the cost from the
supplier and get the cutomer to agree, and in the end, that's what we will
do. Preventing zero price parts is more of an issue with my customer than
with me.
Click to expand...

It sounds as if there's some internal conflict with "your customer".
The folks doing the data entry on parts as they arrive want to be able to
put them in without costing them, but the folks selling them don't want to
be responsible for costing them,either.
It's still a "requirements" issue -
the customer needs to define when the price information is required, then
you can build to his specs.
Perhaps the sales folks could see items that don't have a price yet in a
different color, or have some other cue of this problem?
My motivation for wanting to move to SQL Server is;

- I want to get away from bound forms - this is an extermely complicated
application - the front end is up to about 20 meg. In my opinion, unbound
forms and transaction processing is the best way to insure data integrity.
Click to expand...

Unbound forms and transaction processing can be effective when the issues
with data integrity come from multiple users trying to change the record at
the same time.
However, Access offers several levels of record locking in its bound forms,
which are pretty effective in addressing that concern. Besides, it doesn't
sound as if that's really your problem.
Access's built-in referential integrity options and validations can take
care of all the issues I've seen in this thread.

I'd be interested in what you feel is superior about unbound forms and
transaction processing.
- The back end database is getting pretty large and slow - there are two and
each is up to 150 meg and growing. A year ago they were about 30 each. SQL
Server would be faster.
Click to expand...

150 MB is not big for an Access back-end -
Access can handle up to 2GB.
If speed is an issue, the first place I look is indexes, but you sound
like an experienced enough programmer that I'd expect you have that one
under control.
There are whole sites out there dedicated to improving Access
performance; if you google in this or similar newsgroups, I'm sure you'll
find them. (or perhaps someone with this information at their fingertips
will join this thread.)
- There are getting to be a lot of users - about 50 now, and again, I don't
like Access managing all of the updates.
Click to expand...

50 users doing simultaneous data entry? That could start to be problematic.
But in most cases I've heard of with that many users, actual changing of
data is a lot more restricted. I had an application once with 100 users and
next to no performance problems. (Of course, I've heard of poorly designed
applications where there were problems with fewer than 10 users...)
Managing updates is what Access does best. That's what it was conceived
and designed to do.
- Security is getting to be an issue and I think Access security is rather
weak.
Click to expand...

As far as I have heard so far, this is an entirely internal application. If
you have security problems Access can't address when you (or your customer)
have full control of the users, you've got people on staff maliciously going
out of their way to hack into your software in ways that are expressly
prohibited to them.
That, I would submit, is really more of an HR than an IT issue.
- If we go to the trouble of using unbound forms in Access, we should go the
next step and get a more solid front end. See next.
Click to expand...
I wonder what you'd consider a more solid front end?
- Corruption in the front end is getting to be more of an issue.
Click to expand...
Do you have individual copies of the front end on all workstations,
or are they using the same front end from a server?
If the latter, I'd suggest the change immediately; that's the
most common cause I know of for front end corruption.
Is the front end set up to compact on close? (Assuming you're using
a version of Access later than 97.)
If your users all have local copies of the front end, corruption
shouldn't be much of an issue - if it happens, just replace that copy from
the server. In fact, I've known of companies which made replacement of the
front-end part of each workstation's login script. That way the user
automatically got any changes, too. (And it made compacting the front-end
unnecessary - which was especially useful in Access 97.)
Again, there are whole sites out there dedicated to Access corruption.
Pretty windy eh? If you can talk me out of wanting to get away from
AccessI'd appreciate it. I stay awake some night wondereing when the whole
thing is going to bomb.
Click to expand...

Guess I've been at least as windy as you here.
I don't usually recommend Access for an application which must be available
24-7, and which is fully mission-critical.
I always recommend regular back-ups of Access data.
But other than that, I find that Access tends to get a "bad rap" among
programmers who have been schooled in some of the bigger databases.
In many cases, Access is both flexible and cost-effective. And its rapid
development environment really can't be beat.

I wouldn't want to shoulder the responsibility of "talking me out of wanting
to get away from Access" alone, but again if you'll browse or Google through
the newsgroups, I'm sure you'll find many others addressing this topic.

HTH
- Turtle
 
Back
Top