mlcktmguy
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.
I 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
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
This is a single user machine.
Any idea what is causing the error or how to correct the issue?
I am trying to write another record to the table and getting the 3146 runtime 'ODBC Call Failed' error.
I 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
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
This is a single user machine.
Any idea what is causing the error or how to correct the issue?
ASKER
<have you tried adding a test record manually via the linked table?
and have you tried adding the new data directly? >
Yes both were successful
and have you tried adding the new data directly? >
Yes both were successful
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the responses. I am fairly new to SQL Server.
Vitor Montalvão <<By the way, why are you using a dbOpenDynaset type and not dbOpenTable type? ">>
I am also fairly new to DAO as I've always used ADO in the past. Why would I use dbOpenTable vs dbOpenDynaset? What are other options that might be applicable in other situations?
I'm trying to run the 'sp_configure 'user options'' but obviously doing it wrong.
I opened Sql Server Management Studio and created a new query that looks like this
But I am getting a compile error
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'server'.
Obviously I'm doing something very wrong but not sure what it is. Can anyone help?
Vitor Montalvão <<By the way, why are you using a dbOpenDynaset type and not dbOpenTable type? ">>
I am also fairly new to DAO as I've always used ADO in the past. Why would I use dbOpenTable vs dbOpenDynaset? What are other options that might be applicable in other situations?
I'm trying to run the 'sp_configure 'user options'' but obviously doing it wrong.
I opened Sql Server Management Studio and created a new query that looks like this
use JTSConversion
server.sp_configure "user options"
GO
RECONFIGURE
GO
Set nocount on
GO
But I am getting a compile error
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'server'.
Obviously I'm doing something very wrong but not sure what it is. Can anyone help?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, the values returned when I run sp_configure
Name User Options
Minimum 0
Maximum 32767
Config_Value 0
run_Value 0
Name User Options
Minimum 0
Maximum 32767
Config_Value 0
run_Value 0
ASKER
I was researching 'dbOpenTable' and saw a comment that 'dbOpenTable' only applied to Jet tables. Is that true?
These are SQL tables.
What would be the advantage of 'dbOpenTable' vs 'dbOpenDynaset' on a SQL table? dbOpenDynaset seems to be working fine on other SQL tables I am reading or writing.
These are SQL tables.
What would be the advantage of 'dbOpenTable' vs 'dbOpenDynaset' on a SQL table? dbOpenDynaset seems to be working fine on other SQL tables I am reading or writing.
and have you tried adding the new data directly?