MS Access 2000 Querying SQL Server 2000

I am converting a MS Access 2000 project to use SQL Server. This project has uncovered a number of problems, my latest seems to be very odd.

When I query using ADO against the SQL Server database, the results don't seemed to be returned immediately. It's like the access methods being used are waiting too long to write/read the data.

This could be a simple ADO configuration error, but I cannot find any settings that would make this behave so strange.

I'm using OLE/DB drivers with trusted security and attaching using client-side recordsets (I tried server-side as well, same results) to get data for the Access form. In many instances, the data from the form is not yet retrieved when I check the results using the debugger. If I execute the same section of code just seconds later, it works without fail.

I ran the SQL Server Profiler just for grins and found that records were not getting written to the database as I would have expected. Apparently ADO had generated a transaction and rolled it back. (why I don't know) I have looked in Microsoft's support database and on MSDN to no avail. Rather then rewriting my T-SQL as stored procedures for these functions, I'm hoping for some insights from anyone else who has been down this nasty road.

Here is the exact scenario:

Problem #1:
A form that accepts data and has an update button does not update the recordset being used to populate the form. I even coded a rst.Update statement into the code. The record within the recordset isn't updated until the DoCmd.GotoRecord , , acNewRec call has been made.
I suppose I could code the insert statement myself, but shouldn't Access be updating the database for me. (it does have r/w access)

Problem #2:
A subform that performs a query and allows records to be added to a table does not correctly detect data returned from the query. A check is made in the code for a field (which has a value of 0) but the code detects a null value. If I step through this code in the debugger, enough time passes that the value is present and I never see the problem. If I set a breakpoint at the error, the null condition is indicated, even though the debugger shows a value of 0 for the field.

SQL Server version is 2000, with SP3a applied.

I updated the MDAC to 2.8, JET to 8.0 and still the same problem. I cannot find anything in my ADO or Access books for a setting that would cause this behavior. My connection is using the following info:

Set Conn = New ADODB.Connection
Conn.CursorLocation = adUseClient
Set rst = New ADODB.Recordset
Connection string: Provider=SQLOLEDB;Data Source=myserver;Initial Catalog=mydb;Trusted_Connection=Yes;Integrated Security=SSPI;
Recordset Cursor type: adOpenDynamic
Recordset LockType: adLockOptimistic

I open the recordset using:
rst.Open mytable, Conn, adOpenDynamic, adLockOptimistic, adCmdTableDirect

Problem # 1

After manipulating the data in the form, I press a "save" button on the form. This button calls a number of subroutines, eventually executing the code below:

rst.Update
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Problem # 2
I set the recordsource using: (in the form_open)
Me.RecordSource = "Select * from mytable where myfield = 'data'"

In the form_unload, the problems occur. It tests for a value stored in the form from the query. This value is null at the time of the code execution, it has a value when inspected within the debugger however.

Maybe I'm missing something here, this project was originally coded using DAO. My conversion to ADO is 95% complete. (all of these two components are converted to ADO) This problem is causing me to miss out on sleep and look like a rookie.

Has anyone seen or heard of something such as this? Is there any hope for this snarled mess of 25K lines of code?

Thanks!