Unanswered: E_FAIL status when adding record to ADO Recordset
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
Private Sub Form_Activate()
' ...do some stuff
rst.AddNew ' this is where it fails
' do other stuff
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???