Error -2147217887 when inserting a row into a Microsoft Access table migrated to SQL Server

Question

Error -2147217887 when inserting a row into a Microsoft Access table migrated to SQL Server

Bikhod 61

Hello,

Using the Microsoft SQL Server Migration Assistant tool, we have migrated an Access database to SQL Server 2017 but we are getting error -2147217887 when trying to insert a record into a migrated SQL Server table using ADO Recordset from VBA. The migrated table has an IDENTITY based primary key.

If we remove the IDENTITY then we don't get the error. As soon as we put the IDENTITY back on the primary key column, we get the error.

Error text: Run-Time Error '-2147217887 (80040e21)' Multiple-Step Operation Generated Errors

I found some articles mentioning changing the length of VARCAHAR fields to 250. That didn't make a difference.

Here is the migrated table DDL in SQL Server

CREATE TABLE [dbo].tblDecisionTree

Any ideas?

Viorel 122.6K Reputation points

2022-12-13T17:50:54.027+00:00

Are you inserting values to this column too? Show some details about the ADO code.

4 answers

Your answer

Viorel 122.6K Reputation points

2022-12-13T17:50:54.027+00:00

Are you inserting values to this column too? Show some details about the ADO code.

Answer 1

I have found the cause of our issue. The ID column of our table is declared as an INT in SQL Server by the Access Migration Assistant tool. So, in the Access VBA code, the ADO Recordset is matching SQL Server with an Integer data type, which is VBA is limited to is limited to -32768 to +32767.

But, the values that have been migrated from Access to SQL Sever for this column are currently t 57566 which is too big for VBA Integer.

Solution: changed the SQL Server Identity column to be a BIGINT which meant the ADO recordset column became a LONG and now the code is working.

Answer 2

YufeiShao-msft 7,146

Hi @Bikhod ,

If you use ADO to insert a new record through a client-side recordset into a SQL Server table that has a non-nullable datetime field with a default value, you may receive this error message if you do not supply a value for the datetime field

Please check out this article, hope it can solve your issue
https://learn.microsoft.com/en-us/troubleshoot/sql/connect/error-when-you-use-client-cursor-add-record

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Bikhod 61

Hi

Thanks for the link. Unfortunately, what the article suggest doesn't work me.

I even created a simple table with just one column:

CREATE TABLE [dbo].[Test]
(
[ID] [int] identity(1,1) NOT NULL
) ON [PRIMARY]

And the VBA code below still gets the same error!

Sub Test()

On Error GoTo ErrorHandler  

Dim cnn As ADODB.Connection  
Dim rst As ADODB.Recordset  
  
Set cnn = New ADODB.Connection  
cnn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=CARR_SQL;Data Source=XXX\YYY;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False"  

Set rst = New ADODB.Recordset  
With rst  
    .CursorLocation = adUseClient  
    .Open "SELECT * FROM Test", cnn, adOpenStatic, adLockOptimistic  
    .AddNew  
    .Fields("ID").Value = 57633  
    .Update  
End With  
  
Debug.Print rst("ID").Value

CleanExit:
If Not rst Is Nothing Then
Set rst = Nothing
cnn.Close
End If
Stop
Exit Sub

ErrorHandler:
Debug.Print Err.Description
GoTo CleanExit

End Sub

Answer 3

Gustav 717 MVP

You probably need an insert query to overwrite an identity field. Use an action query and SQL similar to:

INSERT INTO Test ([ID]) VALUES (57633)

Answer 4

AlphonseG 236

You can't insert into a SQL Server Identity column unless you set IDENTITY_INSERT ON for the table.
https://learn.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql?view=sql-server-ver16

Share via

Error -2147217887 when inserting a row into a Microsoft Access table migrated to SQL Server

4 answers

Your answer