ADO MS Jet 4.0/3.51 OLE DB Providor Errors

[FUBAR]

I have had a slew of problems using ADO controls with MS Jet 4.0 or 3.51 providors connecting to Access 97 databases using VB 6.0 Enterprise Edition SP3 MDAC 2.1 SP2

My application uses an ADODC control to retrieve all or selected records from a table. From what I've read regarding troubleshooting these problems I am now using use OpenKeyset, Client side cursors, and ShareDenyNone (or other optomistic approaches).

There's not much code to it so I thought I'd explain the basic approach I'm taking before providing that.

I have text boxes bound to fields in the ADODC to display the current record values, I use MHSFlexGrids or DataLists bound to the same ADODC to navigate between records.

To recreate this problem simply create an Access 97 (sp2) database with each of the following field types:
Date,Time,Integer,Double,Single,Boolean(Yes/No) and AutoNumber (long integer no duplicates) as the primary key.

Create a new VB project with a form containing all the aforementioned controls and data providors. Create a couple of buttons and add code for: me.adodc1.recordset.addnew (New Button)
me.adodc1.recordset.update (Save Button)
me.adodc1.recordset.delete (Delete Button)
(put a quit button in there to close your form and end your project)

If you realy want to get into it make code for a dbl_click event on the datalist control to move to the record selected (I use the AutoNumber field to make it especially hard on myself)

I generally have to use a SQL query (cmdText) as opposed to a Table type or I get smothered in errors. Additionally I have to use jet 3.51 as opposed to 4.0

When using Jet 4.0 I get a "run-time error -2147217887 Cannot update '(expression)' field not updateable" when attempting to use any of the adodc.recordset operation such as movefirst, addnew, find, etc...

3.51 works better but I get "Field not updateable" errors for the bound text box on the AutoNumber field as well as occasional "Disk or Network Error.16389" when attempting to open the form and thus execute the SQL query.

Sorry about being verbose on explaining this problem. Im new to this message board, I hope you'll forgive me.

Things to note:
I included all these different field types because I found it was very important to maintain the same formatting applied in the Access 97 table as the VB 6.0 form (and in some cases with date fields I even had to set regional settings for the OS)

Why Don't I use ODBC (still learning and) ODBC timeouts when querying multiple tables in a single SQL statement (how do you evaluate how long it should take?)

Thanks for any help I've been pretty frustrated with the whole thing...

[Gerald]

FUBAR,

Your username is entirely fitting for the subject at hand. ADO!

From what I can see your ADO troubles are the result of a combination of at least a couple of things. One is an incomplete understanding of the intricacies of ADO. ADO is a complex beast and requires a lot of experimenting and trial and error to get a grasp of what it can and cannot do under various situations. Sadly, the available ADO documentation is sometimes incomplete, misleading, confusing and FUBAR! For example, I see you've tried using the Jet.OLEDB.4.0 provider with an Access '97 database. Much of the available documentation (see the link below) will lead you to believe this provider is compatible with previous versions of Jet, but obviously there are some problems with it.
http://www.microsoft.com/data/ado/adotechinfo/dao2ado.htm

However, reading this page will clear up the issue of which provider you should use with your database. It also touches on the topic of cursor location.
http://support.microsoft.com/support/kb/articles/Q225/0/48.ASP

The second cause of your trouble is that there ARE bugs and glitches in ADO. For example there is an acknowledged bug with ADO and autonumber fields. See the link below. There is also some rather bizarre behavior with the ADO DataGrid control, but there doesn't seem to be any acknowledgement on behalf of microsoft that any problems exist.
http://support.microsoft.com/support/kb/articles/Q190/3/70.ASP?LNG=ENG&SA=ALLKB

Another thing about autonumber fields is that you are not allowed to update them. This is likely the cause of your "Field not updateable" errors. Another thing that could be causing you some problems is that according to msdn documentation, the only cursor type available for client side cursors is the adOpenStatic cursor type. See the following link for an explanation.
http://msdn.microsoft.com/library/psdk/dasdk/mdap4j51.htm

I've adopted the following habits when using ADO for MS Jet databases.

A) Use the Jet.OLEDB.3.51 provider when accessing Access '95/97 databases.
B) Avoid the use of the autonumber fields whenever possible. They're more trouble than they are worth and the workarounds provided by microsoft for the ADO bugs are not always practical or don't always work. Write your own code to generate unique ID's for your records.
C) Whenever practical, use adUseServer and adOpenKeyset for CursorLocation and CursorType. I realize this is not always possible or desirable for reasons that are too numerous to get into here, but this can eliminate many error messages encountered when first learning to use ADO.
D) I've gotten in the habit of using "Move 0" in place of the "Update" method when adding new records. There are a couple of reasons for doing this, but it would require a rather lengthy explanation and I just don't feel like typing anymore. :)

I hope this helps,
Gerald