Results 1 to 1 of 1
  1. #1
    Join Date
    Apr 2003
    Evansville, Indiana

    Angry Unanswered: E_FAIL status when adding record to ADO Recordset

    Hi all!

    I apologize for the length in advance, but I wanted to get all the things I've tried and some background info on the problem...

    I have a VB6 application talking to a SQL Server table through an Access linked table, and in some cases, I get the following error:

    Run-time error error '-214767259 (80004005)':
    Data provider or other service returned an E_FAIL status.

    The recordset object is set at a module level, and it is opened in the Form_Load event. It successfully opens, but when it tries to add a new record in the Form_Activate event, I get that error.

    Here's a scaled down version of the code:
    ' set at module level in form
    Dim rst As New ADODB.Recordset
    Private Sub Form_Load()
       rst.CursorLocation = adUseClient
       rst.Open "SELECT * FROM MyTable", cnn, adOpenKeyset, adLockOptimistic
    End Sub
    Private Sub Form_Activate()
       ' some stuff
       rst.AddNew ' this is where it fails
       ' do other stuff
    End Sub
    Also, the ActiveConnection is set to a Global/Public variable named cnn:
    ' set in separate module file
    Public cnn As ADODB.Connection
    ' runs on startup of application
    Set cnn = New ADODB.Connection
    cnn.Open "Provider=Microsoft.Jet.4.0;Data Source=C:\AccessDB.mdb"
    A little background. This was an application written in VB5, which was eventually upgraded to VB6 with an Access 97 backend, and we are in the first phase of upgrading both the application and backend.

    So far, we've moved the data to SQL Server and are wanting to just have linked tables from Access to SQL Server. Eventually, we'll get rid of the Access db with linked tables once the client is rewritten in .NET as that will talk directly to SQL Server.

    Currently, the VB6 app has no problems when accessing data physically stored in an Access database, but it has problems when going through linked tables to SQL Server. As a test, I changed the connection string to point directly to SQL Server, and I didn't have that error. looks like it's something to do with the linked tables.

    Here are some things I've tried and details on the environment:
    • Verified MDAC Version (currently it's v2.8)
    • Upgraded Jet Version (it's now 4.0.9025.0)
    • Ran MDAC Component checker
    • Removed project reference to DAO, and now am referencing ADO 2.8 (I also set it to 2.1, 2.5, etc., but that didn't work)
    • fully qualified variables to ADODB.Connection, ADODB.Recordset, etc. instead of just Connection, Recordset...
    • modified connection string of connection object to talk directly to SQL Server (no E_FAIL status error)
    • modifed connection string to talk to Access database with data stored in Access database (no E_FAIL status error)
    • SQL Server 2000 has SP4 instaled
    • Windows 2000 Professional on client with all updates
    • VB6 with SP6 installed
    • I tried changing the cursor location to adUseServer and it DID get rid of the error...but for various reasons, I need the cursor Client Side to expose certain functionality
    • I have the SQL Server user id/password saved in the linked tabls in Access (I verified the msysobjects table for this information)
    • I can manually add a record to the linked table by going to Datasheet view in Access
    • there is a Primary Key in the SQL Server table
    • no contraints or rules are present other than the PK on the table
    • no reserved keywords, spaces, funny characters, etc. are being used in the column names
    • I can reference data in the recordset object right after opening by doing a MsgBox rst("MyField1") so I know it's opening correctly
    • I checked various status, state, and other properties of the rst object right before the .AddNew method, and nothing seems in conflict here
    • there are no object-level permission issues on SQL Server for that table or any columns

    A couple of things that might be a little "weird" in the table is that there are bit (Yes/No) datatypes, and the primary key *isn't* the first column. Aside from that, the table is pretty normal.

    I'm starting to run out of ideas...maybe someone has some others???

    Thanks for any suggestions!
    Last edited by kaeldowdy; 10-20-05 at 20:17.
    Kael Dowdy, MCSD, MCDBA

Posting Permissions

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