VB Forum / Database Access / October 2005

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()
  ' ...do 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.
So...it 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!

Kael