Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Location
    USA
    Posts
    6

    Unanswered: 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!

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578

  3. #3
    Join Date
    Oct 2003
    Location
    USA
    Posts
    6

    Re: MS Acc 2000 Querying SQL Svr 2000

    Thanks Satya,

    We are not using clustering, one of the configurations this is failing on is using MS Access 2000 on the same machine that MSDE is running on. (no real network activity)

    I see no failure messages of any kind, just the delayed data.

    The application appears to be working fine except for these two issues.

    I'll run the profiler against this and collect more info.


  4. #4
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    PROFILER may result some information and ensure to run from client machine.

    Also collect PERFMON trace.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •