none
ADO INSERT CAUSES #ERROR(S) IN PREVIOUS RECORD RRS feed

  • Question

  • I am upgrading a Ms Access 10 application to Ms Access 13 which uses ADO to access a SQL SERVER 14 Database.

    On Insert all columns of the previous record except for the row id show #Error while the Row Id becomes the insert id.

    I believe it is a configuration issue but I don't know. I hope someone can help.

    The connection string is

    ConnectADO = "Data Provider=SQLNCLI11" & _
                ";DataTypeCompatibility=80" & _
                ";MARS Connection=False" & _
                ";Data Source=" & TMServerName & _
                ";Initial Catalog=" & TMDatabaseName & _
                ";Persist Security Info=False " & _
                ";user id=" & TMUserName & _
                ";password=" & TMPassword

    The Cursor set up

    Set Rs = New ADODB.Recordset
            Rs.CursorType = adOpenKeyset
            Rs.CursorLocation = adUseClient
            Rs.LockType = adLockOptimistic

    The only code in the Ms Access form is

    Private Sub Form_Load()
        Dim TSQL As String
        TSQL = "SELECT * FROM TESTACCESS13"
        Set Me.Recordset = AdoDbOpenRecordSet(TSQL)
    End Sub

    The Form Result is the following

    Before Vendor Update

      Form1

       ROWIDVENDOR           TICKET

       3           LAFARGE          123654789

       4           HALTON           852369741

       5           DUFFERIN        852741639

       6          MILTON       

     After Vendor Update

      Form1

       ROWIDVENDOR           TICKET

       3           LAFARGE          123654789

       4           HALTON           852369741

       6           #ERROR            #ERROR

       6          MILTON        123



    Monday, May 15, 2017 4:54 PM

Answers

  • I never found the cause but I did create a work around. in the form on the before insert event close the ADO connection and after insert re-open it. Its a little extra code but it does the job.
    Thursday, February 21, 2019 4:41 PM
  • My ADODB Connection is configured like this;

    Connection = CreateObject("ADODB.connection")   

     Connection.ConnectionString = "Data Provider=SQL Server Native Client 11.0" & _

                ";Data Source=YOURSQLSERVER" & _

                ";Initial Catalog=YOURDATABASE" & _

                ";Persist Security Info=False " & _

                ";Integrated Security=SSPI "

        Connection.Provider = "MSDatashape"

        Connection.CommandTimeout = 60

        Connection.Mode = adModeReadWrite

        Connection.CursorLocation = adUseClient

    My ADODB Recordset is configured like this;

    Set AdoRecordset = CreateObject("ADODB.Recordset")

        AdoRecordset.SOURCE = YOURSQLQUERY

        AdoRecordset.CursorType = adOpenKeyset

        AdoRecordset.CursorLocation = adUseClient

        AdoRecordset.LockType = adLockOptimistic

        AdoRecordset.ActiveConnection = Connection

        AdoRecordset.Open

    My MS Access Form Events Are Configured like this;

    Private Sub Form_BeforeInsert(Cancel As Integer)

                    Connection.Close   

    End Sub

    Private Sub Form_AfterInsert()

    Connection.Open

    End Sub

    It took a long while of completely frustrating trial and error to find this work around. I hope it works for you. 

    Thursday, February 21, 2019 9:00 PM

All replies

  • Hello,

    Does the issue persist?

    If the issue has been resolved, we would appreciate if you could share your solution here.

    If the issue still exist, i would suggest you test if the issue could be reproduced on another computer.

    According to your connection string, you are using SQL Server Native Client 11.0 ODBC Driver, i would suggest you use OLEDB Driver to connect.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, May 26, 2017 5:42 AM
    Moderator
  • For starters the connection Provider MsDataShape has been redacted. There are new Providers that should work with a proper SQLOLEDB driver, like Microsoft.Ace.12.0 but I have not been able to find a combination of data provider {SQL driver} and Provider {DAO/ACEDAO Driver} That works and I tried them all including the SQL native client. That being said, I didn't just give-up I switched to a DSN Less Linked set-up with advanced security to remove the UID and Password from the linked tables. I am still transferring T-SQL to JET-SQL to make it all go but the solution is secure and that's what the user wanted.
    Friday, May 26, 2017 7:38 PM
  • Yes the issue persists. I posted my work around, but never truly solved the problem.

    Friday, May 26, 2017 7:45 PM
  • If the ADO Active Connection is open when the forms insert occurs, the #Error behavior occurs.

    If the connection is closed everything is good. Inserts and updates still happen. Therefore, close the connection as soon as possible. I did it like this. (The Cnn.RecordsetOpen function is custom and has a connection.open statement in it before the ADODB recordset open statement.)

     

    ‘FORM LOAD EVENT

    Sub load

    On Error Goto Standard_Err

                Dim TSQL As String

                Set Me.Recordset = Cnn.RecordsetOpen(SQL)

    Standard_Exit:

                Cnn.connection.close

                Exit Sub

    Standard_Err:

                Msgbox err.description

                Resume Standard_Exit

    End sub

    Saturday, November 18, 2017 5:34 PM
  • Hi Simon - I have exactly the same issue.  Any table accessed through ADO to SQL Server ALWAYS gives me this annoying #Error on continuous forms when I try and insert.  Did you ever get to the bottom of this???  
    Thursday, February 21, 2019 9:56 AM
  • I never found the cause but I did create a work around. in the form on the before insert event close the ADO connection and after insert re-open it. Its a little extra code but it does the job.
    Thursday, February 21, 2019 4:41 PM
  • Thank you Simon - sorry to be a real pain but is there any way you could post me both the full open code and the close code? I am setting up the connection and recordset on the parent form and then setting the subform recordset to that.  I have tried cnn.close immediately (in the parent form) after setting the subform's recordset, but this hasn't worked.  Are you saying I should do a connection.close inside the subform Form_BeforeInsert ? And then re-create the connection again?  My only workaround so far is to immediately save the record as soon I have populated the key values which still shows the #Error briefly.  Also been having lots of issues with odbc driver 13 and my nvarchar(max) fields, so have had to truncate them to nvarchar(4000) or use sql native client instead (though my users would prefer a dsn on odbc 13).

    Any help greatly appreciated - I am baffled as to why you and I seem to be the only people with this issue!

    Thanks,

    Chris

    Thursday, February 21, 2019 6:17 PM
  • My ADODB Connection is configured like this;

    Connection = CreateObject("ADODB.connection")   

     Connection.ConnectionString = "Data Provider=SQL Server Native Client 11.0" & _

                ";Data Source=YOURSQLSERVER" & _

                ";Initial Catalog=YOURDATABASE" & _

                ";Persist Security Info=False " & _

                ";Integrated Security=SSPI "

        Connection.Provider = "MSDatashape"

        Connection.CommandTimeout = 60

        Connection.Mode = adModeReadWrite

        Connection.CursorLocation = adUseClient

    My ADODB Recordset is configured like this;

    Set AdoRecordset = CreateObject("ADODB.Recordset")

        AdoRecordset.SOURCE = YOURSQLQUERY

        AdoRecordset.CursorType = adOpenKeyset

        AdoRecordset.CursorLocation = adUseClient

        AdoRecordset.LockType = adLockOptimistic

        AdoRecordset.ActiveConnection = Connection

        AdoRecordset.Open

    My MS Access Form Events Are Configured like this;

    Private Sub Form_BeforeInsert(Cancel As Integer)

                    Connection.Close   

    End Sub

    Private Sub Form_AfterInsert()

    Connection.Open

    End Sub

    It took a long while of completely frustrating trial and error to find this work around. I hope it works for you. 

    Thursday, February 21, 2019 9:00 PM
  • Yes the issue persists. I never found a solution but I did manage a work around which I have shared. Thank-you for showing your interest. And by the way the OLEDB driver makes no difference.
    Thursday, February 21, 2019 9:36 PM
  • Thanks Simon for all your help - I haven’t been able to get it to work yet, but I couldn’t get the object late binding method that you use to work properly for me so it might be that? I declared an object connection variable at the module level, then get an error when doing the create object call, so I tried my original connection method declaring as an adodb.connection and closed the connection before insert but that didn’t solve it. Will keep plugging away, thanks again.
    Sunday, February 24, 2019 10:28 PM
  • Was this ever reported to Microsoft as a glitch since it works perfectly well in ACCESS 2010 and not ACCESS 16?

    Unfortunately, your work around only helps when typing a new record, but it does not work when copying an existing record and pasting it. (This works perfectly in ACCESS 2010.)

    Thank you so much for sharing what you did!! I know this was awhile ago, but can you verify that you can copy paste a record without this issue appearing?

    I think I matched your code as closely as possible, and unfortunately when I copy a record and paste it, the record above the new record disappears and is replaced with a blank record with "#error" in the primary key field.

    Here is a copy of all code from a test form I created which is linked to a table with one primary key as a varchar(50)


    Option Compare Database
    Dim v_ADO_Conn As ADODB.Connection
    Dim vConnString As String
    
    Private Sub Form_AfterUpdate()
    	v_ADO_Conn.Open
    End Sub
    
    Private Sub Form_BeforeInsert(Cancel As Integer)
    	v_ADO_Conn.Close
    End Sub
    
    Private Sub Form_Open(Cancel As Integer)
        Dim vConnString As String, vSQL As String
        Dim v_ADO_RS As ADODB.Recordset
        
        
        Set v_ADO_Conn = CreateObject("ADODB.Connection")
        v_ADO_Conn.ConnectionString = "Data Provider=SQL Server Native Client 11.0" & _
                                    ";Data Source=Rockware19\SQL_2012" & _
                                    ";Initial Catalog=System" & _
                                    ";Persist Security Info=False " & _
                                    ";Integrated Security=SSPI"
        v_ADO_Conn.Provider = "MSDatashape"
        v_ADO_Conn.CommandTimeout = 60
        v_ADO_Conn.Mode = adModeReadWrite
        v_ADO_Conn.CursorLocation = adUseClient
        v_ADO_Conn.Open
        
        vSQL = "SELECT * FROM setcolor"
        Set v_ADO_RS = CreateObject("ADODB.Recordset")
            v_ADO_RS.Source = vSQL
            v_ADO_RS.CursorType = adOpenKeyset
            v_ADO_RS.CursorLocation = adUseClient
            v_ADO_RS.LockType = adLockOptimistic
            v_ADO_RS.ActiveConnection = v_ADO_Conn
            v_ADO_RS.Open
        
        Set Me.Recordset = v_ADO_RS
    End Sub


    • Edited by pcarrisalez Thursday, August 6, 2020 2:17 PM Formatting
    Thursday, August 6, 2020 2:14 PM