Connection Info in a FE/BE DB Environment

[rdodge]

Given that neither DAO nor ADO coding has fully addressed the need that I have of when a user can go into edit mode, I have decided to use ADO coding with the Optimitic setting on it, and setup a central table to keep track of when users goes into edit mode on what records of what tables. However, this gets into concurrent user issues and one of such issues that I'm having to deal with is how to account for when the client side of the connection loses it's connection. I don't want to have a record be kept locked out due to a situation like that.

Here's the general steps that I would have the code do:

When user enters the form, an ADO recordset is created. On a timed based, the recordset is updated given the best cursor type that I can use is Keyset, and that doesn't allow for viewing new records, so the recordset would need to be refreshed either on a timed basis or when the user types in the ID number of the record, which I will probably go with the later option the majority of the time. At this point, the user is still in Read Only mode (at least, this is how I would like to have it setup as), but the moment the user starts changing information on the form other than the ID number of the record, that's when I would like to allow the user go into Edit mode provided the following 2 requirements are met:

User has the permission to edit the record
There are no other users currently editing the record.

Purpose for this:

I would like to allow 2 or more users that DOES have the permission to change such records to also be able to at least view the records, but only 1 of them to only be able to edit the record, ideally the one that begins to edit the record first. Under the current ADO coding, the first person to get to the record is the one that has the edit mode locked if the recordset is NOT set as Read Only, when in theory, the first user may only be viewing the information at that point of time, and then a second user also looks up the record, finds that it needs an adjustment, and would like to change it, but if it attempts to, under ADO coding, the second user can't unless the recordset is set to Optimistic, but then other issues can be presented.

The issue I'm faced with is how I'm going to have the code control who gets the edit mode, most of which I have already addressed in the planning stage, but the connection issue, I would like to allow for the program to determine when a user that's in edit or add new record mode has lost the connected unexpectedly without first completing the transaction, so as to remove the lock on the record that's being edited or to remove the new record entry. How can this be best achieved or how can one tell when a user has lost it's connection to the BE DB?

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[vbajock]

connection status is tested via the state property:

http://www.devguru.com/Technologies/ado/quickref/connection_state.html

You make it sound like connections pop on and off like corks from a champagne bottle. It is a good idea to test the connection state thru out your code, but once you establish a connection, unless you've got a crappy network, they stay open until you explicitly close them. If the connection does fail, your lock is gone as well, so I don't understand:
" I would like to allow for the program to determine when a user that's in edit or add new record mode has lost the connected unexpectedly without first completing the transaction, so as to remove the lock on the record that's being edited or to remove the new record entry "

If you want complete control over updating, base your form on a user-defined data structure or a buffer table, collect the data, then attempt to write it to your shared database from the buffer. Look at Begintrans, Committrans, Rollback in the help file and you can see how you can handle incomplete transactions if your update from a buffer fails. Roll the data back to the buffer and send the user a message on how to handle the situation in the case of failure. Localize your buffers for the user - put it on his hard drive or on his private network share.

 

[rdodge]

If you talking about the LDB file, that file will be the same regardless if it's just one user connected or 100 users connected, so what other lock is there, if any? Can this lock be tested from the server side of the DB application for any one user/login that's connected to the BE DB?

Soon after I started to dig into ADO programming, I learned rather quickly that it seems that OLE DB programming is tied rather largely to ADO coding, so I been going over the material on OLE DB Programming from Microsoft's MSDN Library web site. I'm currently on Chapter 9 of 28. After going into as far as I have, I see it will take some time to actually play around with the code to see exactly how it all works, and it raises the question, can all of this really work from within Access cause the coding examples are shown in C Programming, thus according to MS, C Programming is a low level machine language while VBA is a high level machine language cause it doesn't possess near the control that C Programming does.

Having the data buffered on the client side, that is in effect what I have done by setting up a FE/BE type environment and using UNBOUND forms. The only time when updating would be done onto the shared DB system is when it is to actually be updated and committed such as using IRowsetChange::SetData or IRowsetUpdate::Update for updating the information in an uncommitted state, then use ITransaction::Commit to confirm the data and commit it. I have also put in some extra coding to handle the updating modes on the Client side, but still need to have the necessary control in place on the server side.

There's other issues that I have to address too, but I don't want to try to take on too many issues at one time.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[vbajock]

C coding within Access defeats the purpose of Access, which is to avoid coding in low-level languages, accomplished by manipulating pre-built objects using Visual Basic. C coding is more appropriate for building the objects themselves, in other words, you would use a C based language to write ADO or the OLEDB constructs that support ADO, and you would use VBA to manipulate those objects. Usually the various vendors of database software products supply you with these pre-built objects, and then you write the code to manipulate the objects to achieve your solution. The VBA implementation of ADO is really simple and straightforward and should handle any locking problems you encounter. Any good book on beginning ADO would show you how.

If you are going to have more than 20 concurrent users, you should move from Access to SQL server or Oracle as your back end, and use Access as your front end tool only, for building screens and such, and for managing your code.

 

[rdodge]

I know about the user thing, but with the owners being so tight not to mention that's there's a major political thing that has taken place within the IT department, I'm basically stuck with using what I have to work with.

To tell the truth, there's a lot of reasons why not to use Access for the project that I'm working on, and in all actuality, it should really be worked on within the main DB program that we do have, but I basically feel as though I have been pushed off to the side as I can think of a few possible reasons why, but I would rather not say. If we were to all work together, we could address the various issues which then some of the issues that I'm having to try to deal with on my own would not be needed to be dealt with within the Main DB program since those more critical basic issues has already been addressed. It would only deal with some of the other issues that we do have, which can be built on top of the base solid foundation that has already been put into place with the main DB program.

I grant them one thing, there is the issue of upgradability, but one has to look at what's going to be better and how much of a difficulty would it be to upgrade. Yes, there is that chance of structural change too, which adds more concern.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.