Acc 2.0 -> 2000 conversion, redesign ?

[RichMorrison]

Overview
=======
I am converting several apps from Access 2.0 to Access 2000. The apps will have the same function and look-and-feel.

Your experiences and suggestions will be much appreciated.

Boring Detail Section
==============
One app is a batch data update. The business purpose is workflow tracking. Each user records the number of "Incoming" work pieces they receive. Some users record daily, others at different intervals.

The app is used by approximately 150 people. The app consists of a front end with forms, queries, etc and a back end with tables. All parts are currently Access 2.0 and will be converted to Access 2000

The Incoming work falls into standard categories so the app presents a list of possible categories to each user. The user just fills in the number of Incoming pieces.

Each user belongs to a department and the categories of Incoming work are linked to the departments. It is a many to many relationship; each group receives many types of Incoming and one type of Incoming can be received by two or more departments.

Process Flow
=========
1) User launches app and enters User Name and password.
2) App takes user name and looks up department.
3) App takes department and selects Incoming types for the department.
4) The selected Incoming type records are appended to a "work" table along with the user's unique User Name. This is done with a form sub and uses DOA methods to insert records.
5) A form is displayed with the Incoming categories for the user. The form is bound to the "work" table via a query that has a parm for the User Name. The form is continuous with a line for each category of Incoming.
6) The user enters a number of pieces in each appropriate line. If the users enters nothing, the default is zero.
7) The user clicks a Save button. The code behind the button uses ADO to copy all the records from the "work" table to the permanent Incoming table. Then the code deletes all records for this user from the "work" table.
Should the user click the Cancel button, the "work" data is deleted and not copied to the permanent table.

Possible Changes ?
=============
The data entry form could be bound to a DAO recordset with the Form.RecordSource property (instead of bound to the "work" table)

Or the form could be bound to an ADO recordset, or a "disconnected" ADO recordset I think.

When the user clicks Save, the form sub could use DAO or ADO code to insert data in the permanent Incoming table.

There are certainly other options I have not thought of.

Thanks for your attention, please reply with the usual brilliance.

RichM

 

[pono1]

Rich,

One thought...

If I read this right, after a user logs in records are created in a work table that's located, I presume, on the server. The client opens a form bound to this table, which means this set of recs is pulled across the wire. After the user saves, the same set of records moves back across the wire -- from client to server -- appending to a permanent Incoming table.

An option: Put the work table in the front end. You would then, of course, need to create the recs using code based on your dept number logic (as opposed to, say, appending "template" recs from the server). Since your work form would be bound to a local table with records locally created, no recs would be pulled over the wire initially.

But I also wonder, vaguely, as you did, if you can't somehow eliminate the middle man, the work form, connecting to your permanent table...

In the end, I'm not sure if any of this would be worth the time and effort to implement: it doesn't sound like a lot of recs are involved.

Regards,
Tim

 

[RichMorrison]

You wrote
<<
If I read this right, after a user logs in records are created in a work table that's located, I presume, on the server. The client opens a form bound to this table, which means this set of recs is pulled across the wire. After the user saves, the same set of records moves back across the wire -- from client to server -- appending to a permanent Incoming table.
>>

Yes, that's right. Records are appended to a work table and later copied to a permanent table.

Although the work and permanent tables are in a back end on a server, we don't have any performance problems; small amount of data and fast network.

But.... I am thinking about eliminating the work table and binding an update form to an internal recordset, either old fashioned DAO or newer ADO. I think I can do either.

Do you have any experience using recordsets as "RecordSource" for a bound form or subform ?

Thanks,
RichM

 

[Pat Hartman]

If you use a bound form to update the data, you won't need DAO or ADO.

 

[pono1]

Rich,

You mean the "disconnected ADO recordset" you referred to in your first post? No -- that, I thought (please don't quote me), is more common in an n-tier app than a single or 2-tier app. The few ADO code samples I've seen make it look straight forward, though.

Regards,
Tim

 

[RichMorrison]

Tim,

After more research, this seems to be the situation:

With Access 2K I can use either an ADO or a DAO recordset as the source for a form. But an ADO recordset from the "Jet" provider is not updatable

So it looks like a DAO recordset is the only option if I want to avoid using a query or table as a form recordsource, and it looks like there is no great advantage to using a recordset.

RichM

 

[Pat Hartman]

I don't understand why you are looking for a way to avoid using a query or table as the form's recordsource.

 

[RichMorrison]

Pat,
<<
I don't understand why you are looking for a way to avoid using a query or table as the form's recordsource.
>>

Some reasons:
1) it *might* be more reliable, multiple users would not be updating the same work table at the same time. I think a disconnected ADO recordset would be the most reliable but that does not seem to be an option.

2) it *will* be simpler to use a form-level recordset instead of a work table. Functions to copy data from the work table to the permanent table and delete data from the work table will not be needed.

RichM

 

[pono1]

Though I haven't conducted a survey, generally an unbound form is more reliable than a bound form (less prone to corrupting a table in a networked, JET environment)-- and, traffic-wise, more efficient. The unpleasant trade-off, of course, is the time and code involved in creating a reliable, efficient unbound form.

In this context, the mildly interesting thing about Rich's DB is that no front-end form is bound to the permanent Incoming table. Therefore -- if you believe what various others say -- there's less chance that the incoming table will become corrupt. The work table is the fall guy. Putting that work table in the front-end further dilutes the risk because if it becomes corrupt (less likely -- because only one user will ever be bound to it); the back-end will not be affected. Of course with a SQL back end, you wouldn't need to think about all of this --- and it doesn't sound like there's ever been a corruption problem with Rich's DB, so I suppose, as it turns out, I am, like most people on the Internet, simply talking to myself. Note: Stop at store and pick up eggs, bread, Worcestershire sauce, oranges, green beans, wine, diet Coke, white pepper, and, if on sale, frozen pizza and tuna.

Regards,
Tim

 

[RichMorrison]

Tim,

You wrote
<<
In this context, the mildly interesting thing about Rich's DB is that no front-end form is bound to the permanent Incoming table. Therefore -- if you believe what various others say -- there's less chance that the incoming table will become corrupt.
>>

That's right. I did not mention this concept in any earlier posts but I think it is important for any kind of high volume data entry application. Some authors refer to a general type of "store and forward" application where data is held in some temporary space and later posted as a "batch" to a permanent space.

Access lets you do this and that's the way I have designed this type of app for lot of years.

"Orange juice, coffee beans, toothpaste, razors". Thanks for the reminder.

RichM