MS ACCESS 2013-2016 continuous forms show an error when adding any record past the 3rd.
I have connected via ADO to SQL SERVER.
I have been working on this issue for days. I am hoping there is something I can modify in my connection string, use a different library, or anything I can do in VBA to avoid building multiple workarounds in a project that has existed and grown over 20 years.
Is this a known issue? Can this be fixed?
This is the form code I am using:
Option Compare Database Private Sub Form_Open(Cancel As Integer) Dim vConnString As String, vSQL As String Dim v_ADO_RS As New ADODB.Recordset Dim v_ADO_Conn As New ADODB.Connection vConnString = "Provider=SQLOLEDB;Data Source=Rockware19SQL_2012;" & _ "Initial Catalog=System;" & _ "User ID=***;Password=****;" vSQL = "SELECT * FROM setcolor" v_ADO_Conn.Open vConnString With v_ADO_RS Set .ActiveConnection = v_ADO_Conn .CursorLocation = adUseClient .Source = vSQL .LockType = adLockOptimistic .CursorType = adOpenKeyset .Open End With Set Me.Recordset = v_ADO_RS End Sub
This is an example of one of the SIMPLE SQL SERVER tables I am connecting to:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[setColor]( [ColorName] [varchar](50) NOT NULL, [MSAccess] [int] NULL, [Hex] [varchar](100) NULL, CONSTRAINT [PK_setColor] PRIMARY KEY CLUSTERED ( [ColorName] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO