Avatar of mlcktmguy
mlcktmguy
Flag for United States of America

asked on 

Run Time Error 3146, Writing SQl Record

Yesterday, I added a table to my SQL database to use as an execution log.  In the meantime I have run several processes in the MS Access 2013 application using the table that successfully created records in the table.  At the time of the error, there were over 25,000 records in the table.

I am trying to write another record to the table and getting the 3146 runtime 'ODBC Call Failed' error.
Runtime ErrorI checked the values of all passed fields and they are solid and contain expected values.

All of the records added to the table are added in this routine.  I am getting the error on statement
rsOut.AddNew
Public Sub logExecutionInfo(passedMessage As String, passedStartTime As Date, passedEndTime As Date)
'
wkDateTime = Now
wkUser = GimmeUserName
'
Dim wkDuration As String
wkDuration = getDuration(passedStartTime, passedEndTime)
'

Dim db As DAO.Database
Set db = getCurrentDbC
'
Dim rsOut As DAO.Recordset
Set rsOut = db.OpenRecordset("tblExecutionLog", dbOpenDynaset, dbSeeChanges)
'
rsOut.AddNew
'
rsOut![Operation] = passedMessage
rsOut![Duration] = wkDuration
rsOut![DateTimeAdded] = wkDateTime
rsOut![UserAdded] = wkUser
'
'
rsOut.Update
'
rsOut.Close
Set rsOut = Nothing
'
End Sub

Open in new window


I created the table in MS Access then used the SSMA For Access to migrate the table to SQL Server.  I have done this successfully with dozens of other tables.  I noticed when this table was migrated a Time Stamp wasn't added by SSMA.
Here is a print of the SQL Table definition
USE [JTSConversion]
GO

/****** Object:  Table [dbo].[tblExecutionLog]    Script Date: 1/22/2016 2:20:15 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblExecutionLog](
	[ExecID] [int] IDENTITY(1,1) NOT NULL,
	[Operation] [nvarchar](255) NULL,
	[Duration] [nvarchar](255) NULL,
	[DateTimeAdded] [datetime] NULL,
	[UserAdded] [varchar](50) NULL,
 CONSTRAINT [tblExecutionLog$PrimaryKey] PRIMARY KEY CLUSTERED 
(
	[ExecID] 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

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'ImportTest_wSQLLinks.[tblExecutionLog].[ExecID]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblExecutionLog', @level2type=N'COLUMN',@level2name=N'ExecID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'ImportTest_wSQLLinks.[tblExecutionLog].[Operation]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblExecutionLog', @level2type=N'COLUMN',@level2name=N'Operation'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'ImportTest_wSQLLinks.[tblExecutionLog].[Duration]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblExecutionLog', @level2type=N'COLUMN',@level2name=N'Duration'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'ImportTest_wSQLLinks.[tblExecutionLog]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblExecutionLog'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'ImportTest_wSQLLinks.[tblExecutionLog].[PrimaryKey]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblExecutionLog', @level2type=N'CONSTRAINT',@level2name=N'tblExecutionLog$PrimaryKey'
GO

Open in new window



This is a single user machine.

Any idea what is causing the error or how to correct the issue?
Microsoft AccessMicrosoft SQL ServerVisual Basic ClassicProgrammingMicrosoft Development

Avatar of undefined
Last Comment
mlcktmguy

8/22/2022 - Mon