M
Mark A. Sam
Recently I had a revival of Error 3155- ODBC--insert on a linked table
'<Some Table Name>' Failed. After spending much time with this I had come
to the conclusion that Identity Insert was on for the particular table and I
had tried to turn it off using both a Stored Procedure through Enterprise
Manager as well as a Pass-Through query in in my VBA code.
The stored procedure looked like this:
CREATE PROCEDURE [dbo].[InsertOff] AS
SET IDENTITY_INSERT [Some Table Name] OFF
GO
The Pass-Through query looked like this:
set identity_insert [Order Entry ST Materials] off
with the Return Records Property set to off to avoid a message in VBA when
you open the query
DoCmd.OpenQuery "OrderEntryMatSetOff"
That theer query won't return records.
I posted the above becuase I think that Identity Insert (I'm not sure if the
terminology is correct) for my partricular table is set to On and that it
won't go off. As far as I know, Identity Insert On means that you are able
to insert a value into the Identity Column which is like a Autonumber field
in Access. Also as far as I know, only one table at a time can have it
turned On.
Since (to the best of my knowledge) I couldn't turn the Identity Insert on
the table to off (I tried turning it On to a test table) I assigned a value
to the Identity Column. The method is below in line 2 of the code Snippet.
rsOrdMat.AddNew
rsOrdMat![sqlID] = NEXTprodMatsqlID ,<------------------ This is the
solution.
rsOrdMat![prodmatType] = rsCustMat![prodmatType]
rsOrdMat![prodmatDesc] = rsCustMat![Description]
rsOrdMat![prodmatNotes] = rsCustMat![prodmatNotes]
rsOrdMat![invID] = rsCustMat![prodmatDesc] 'rsCustMat![ProdmatDesc] is not
the [InvID] Number
rsOrdMat![ordDetID] = [ordDetID]
rsOrdMat![Posted] = True 'Marks as being posted from Customer Profile
If IsNull(rsCustMat![Mat Price]) Or rsCustMat![Mat Price] = "" Then
rsOrdMat![prodMatUnitCost] = 0
Else
rsOrdMat![prodMatUnitCost] = rsCustMat![Mat Price]
End If
If IsNull(rsCustMat![Mat Shipping Cost]) Or rsCustMat![Mat Shipping Cost] =
"" Then
rsOrdMat![prodMatShipCost] = 0
Else
rsOrdMat![prodMatShipCost] = rsCustMat![Mat Shipping Cost]
End If
rsOrdMat.Update 'ODBC Error occurs here
The line
rsOrdMat![sqlID] = NEXTprodMatsqlID
solved it where [sqlID] is the Identity Column for the table and
NEXTprodMatsqlID us a function to obtain the next incremented value of
[sqlID]
ADDITIONAL INFO
If you are interested, the function, NEXTprodMatsqlID, looks like this:
Public Function NEXTprodMatsqlID() As Long
NEXTprodMatsqlID = DLookup("[currentid]", "MaxProdMatsqlID") + 1
End Function
Where MaxProdMatsqlID is a stored query that looks like this:
SELECT Max([Order Entry ST Materials].sqlID) AS CurrentID
FROM [Order Entry ST Materials];
I ran into this problem 2-1/2 years ago on the same procedure but addressing
a different table. If search with the terms,
turn off "Identity Insert" SQL Server Error 3155
In Google, you will see a thread entitled Insert Failed. That is a copy of
my post of this problem at the time. Near the end of the thread, I stated
that the problem was cleared up, and it was until a couple of months ago
when according to my client, it suddenly popped up. I hadn't touched the
application since that time, and niether had anyone else. The solution
above was incorporated then remmed out for another one, but I have no idea
what it was. I think it involved relinking the tables, but that didn't work
in my current situation.
I hope this helps anyone encountering Error 3155. This error has been
around since at least 1999, and doesn't seem to have been addressed by
Microsoft.
"Surprise, Surprise, Surprise!!!" Gomer Pyle USMC.
God Bless,
Mark A. Sam
'<Some Table Name>' Failed. After spending much time with this I had come
to the conclusion that Identity Insert was on for the particular table and I
had tried to turn it off using both a Stored Procedure through Enterprise
Manager as well as a Pass-Through query in in my VBA code.
The stored procedure looked like this:
CREATE PROCEDURE [dbo].[InsertOff] AS
SET IDENTITY_INSERT [Some Table Name] OFF
GO
The Pass-Through query looked like this:
set identity_insert [Order Entry ST Materials] off
with the Return Records Property set to off to avoid a message in VBA when
you open the query
DoCmd.OpenQuery "OrderEntryMatSetOff"
That theer query won't return records.
I posted the above becuase I think that Identity Insert (I'm not sure if the
terminology is correct) for my partricular table is set to On and that it
won't go off. As far as I know, Identity Insert On means that you are able
to insert a value into the Identity Column which is like a Autonumber field
in Access. Also as far as I know, only one table at a time can have it
turned On.
Since (to the best of my knowledge) I couldn't turn the Identity Insert on
the table to off (I tried turning it On to a test table) I assigned a value
to the Identity Column. The method is below in line 2 of the code Snippet.
rsOrdMat.AddNew
rsOrdMat![sqlID] = NEXTprodMatsqlID ,<------------------ This is the
solution.
rsOrdMat![prodmatType] = rsCustMat![prodmatType]
rsOrdMat![prodmatDesc] = rsCustMat![Description]
rsOrdMat![prodmatNotes] = rsCustMat![prodmatNotes]
rsOrdMat![invID] = rsCustMat![prodmatDesc] 'rsCustMat![ProdmatDesc] is not
the [InvID] Number
rsOrdMat![ordDetID] = [ordDetID]
rsOrdMat![Posted] = True 'Marks as being posted from Customer Profile
If IsNull(rsCustMat![Mat Price]) Or rsCustMat![Mat Price] = "" Then
rsOrdMat![prodMatUnitCost] = 0
Else
rsOrdMat![prodMatUnitCost] = rsCustMat![Mat Price]
End If
If IsNull(rsCustMat![Mat Shipping Cost]) Or rsCustMat![Mat Shipping Cost] =
"" Then
rsOrdMat![prodMatShipCost] = 0
Else
rsOrdMat![prodMatShipCost] = rsCustMat![Mat Shipping Cost]
End If
rsOrdMat.Update 'ODBC Error occurs here
The line
rsOrdMat![sqlID] = NEXTprodMatsqlID
solved it where [sqlID] is the Identity Column for the table and
NEXTprodMatsqlID us a function to obtain the next incremented value of
[sqlID]
ADDITIONAL INFO
If you are interested, the function, NEXTprodMatsqlID, looks like this:
Public Function NEXTprodMatsqlID() As Long
NEXTprodMatsqlID = DLookup("[currentid]", "MaxProdMatsqlID") + 1
End Function
Where MaxProdMatsqlID is a stored query that looks like this:
SELECT Max([Order Entry ST Materials].sqlID) AS CurrentID
FROM [Order Entry ST Materials];
I ran into this problem 2-1/2 years ago on the same procedure but addressing
a different table. If search with the terms,
turn off "Identity Insert" SQL Server Error 3155
In Google, you will see a thread entitled Insert Failed. That is a copy of
my post of this problem at the time. Near the end of the thread, I stated
that the problem was cleared up, and it was until a couple of months ago
when according to my client, it suddenly popped up. I hadn't touched the
application since that time, and niether had anyone else. The solution
above was incorporated then remmed out for another one, but I have no idea
what it was. I think it involved relinking the tables, but that didn't work
in my current situation.
I hope this helps anyone encountering Error 3155. This error has been
around since at least 1999, and doesn't seem to have been addressed by
Microsoft.
"Surprise, Surprise, Surprise!!!" Gomer Pyle USMC.
God Bless,
Mark A. Sam