Error 3155- Is there no solution?

[Mark A. Sam]

Hello,

I am getting Error 3155- ODBC--insert on a linked table 'Order Entry ST
Material' Failed.

There are two tables I am popluating using DAO Addnew/Update methods. On is
a Materials table the other a Tasks table. The originating data is from
local tables. The Error comes after the Update method.

I have done a search on this error and there are hundreds of result dating
back from at least year 1999, and noone seems to have a solution other then
the possible reasons, which don't apply to my situation. The Tasks table
always populates without a problem. The Materials table is the problem
where it sometimes populates and sometimes bombs out. There doesn't seem to
be any discernable pattern. It bombs out when it does.

That Tasks and Materials are related to different Products. It mostly bombs
out for a particular product called a Spot Sheet, but sometimes it will
happen with all of the products on an order.

If an order is entered, however, and the database closed and reopened or the
user switches to another record and returns, then the problem doesn't occur.

Any help on how to solve this would be apprecated.

Thank you and God Bless,

Mark A. Sam

 

[gllincoln]

Hi Mark,

How many concurrent users - what are the chances of collisions? What kind of
locking? What is the back end - SQL? Access? How much server traffic is
there? How is the bandwidth?

Gordon

 

[Mark A. Sam]

Hello Gordon,

This has a SQL Server backend, I'm not sure of the version, maybe 7. Access
2000 is the front end. There are about 12 workstations on site in western
NY and maybe 4 or 5 remote users in Tn. This particular function, Order
Entry, usually has only two users in NY and I don't know how many input
remotely, at least 2.

I can reproduce the error with only myself on the whole system. I don't
know about bandwidth. This is a lan with outside access using terminal
server.

 

[gllincoln]

Hi Mark,

Do the users each have their own copy of the front end, including the remote terminal services users? Concurrent use of a copy of a front end application can create many severe side-effects. What you describe sounds like an issue of a table time out, good chance of that being due to the table being locked.You might want to look at all processes that use the Materials table - anything that sets an exclusive lock on a larger scale than a current record level. It doesn't have to be anything related to order entry, could be some inventory evaluation or demand estimating worksheet run by somebody in purchasing via an Excel workbook (just an example). Whatever is creating the conflict is most likely locking the table to read only, until it its procedure is complete.It's the intermittent nature of the problem description that suggests to me that an external and unrelated event locking the table is a strong possibility.

If this is a typical order entry process - it's relatively mission critical that the data be preserved but one can wait at least a short while to updata the back end table. You might create a temp incoming order copy of the materials table, then set a process to append the contents of that table to the main backend table on a set interval. The user could go on their way, you would have the data and could (worst case scenario) run a SQL query at the end of the day to append any records that weren't already in the main system and clean out the temp table for the next day use. This timeline may not work in your situation - but there is probably some variation of this concept that could work for you.

This is a brute force solution but I get the impression you have been working on this one for a while - a work-around lets the users be about their work and buys you time for unhurried and more deliberate investigation.

Hope this helps...
Gordon

 

[Mark A. Sam]

Hi Mark,

Do the users each have their own copy of the front end, including the remote terminal services users?

* Yes it is uploaded to their local drive when they log on.

Concurrent use of a copy of a front end application can create many severe side-effects.

*I agree.

What you describe sounds like an issue of a table time out, good chance of that being due to the table being locked.

*I really don't believe either is the case, becuase it only occurs with the one table. Also becuase this is a common error. I have searched it on google and read through many of the threads without see a solution.

You might want to look at all processes that use the Materials table - anything that sets an exclusive lock on a larger scale than a current record level.

*I wouldn't know how to investigate that. Since the process is adding records, I don't know how this could be a record locking issue.

It doesn't have to be anything related to order entry, could be some inventory evaluation or demand estimating worksheet run by somebody in purchasing via an Excel workbook (just an example).

*I have thought of that. The data is taken from default records, but they are rarely updated and not usualy that anyone accesses them. This is a constant occurance. This weekend I was the only person on the system late night Saturday.

Whatever is creating the conflict is most likely locking the table to read only, until it its procedure is complete.It's the intermittent nature of the problem description that suggests to me that an external and unrelated event locking the table is a strong possibility.

*I don't see how. It the table is being locked, I would think that is a problem. How could anyone use SQL Server in a useful way if tables lock up whenever data is being added.

If this is a typical order entry process - it's relatively mission critical that the data be preserved but one can wait at least a short while to updata the back end table.

*I can't do that. reports are printed and emailed right after the order is added. It would be a nuisense to delay the reports.

You might create a temp incoming order copy of the materials table, then set a process to append the contents of that table to the main backend table on a set interval. The user could go on their way, you would have the data and could (worst case scenario) run a SQL query at the end of the day to append any records that weren't already in the main system and clean out the temp table for the next day use. This timeline may not work in your situation - but there is probably some variation of this concept that could work for you.

*Good thought, but I can't do that. Many of the orders need to be processed and shipped that day.

This is a brute force solution but I get the impression you have been working on this one for a while - a work-around lets the users be about their work and buys you time for unhurried and more deliberate investigation.

*A long while. 4 or 5 years ago I converted the back end from Access to SQL Server. That opened a huge can of worms. I worked out all of the problems except this. I hadn't done any work for this client for a year and a half and thought it was resolved. It hadn't been and I need to get it done. I am going to try an append query. Thanks for your input and God Bless,


Hope this helps...
Gordon

 

[Klatuu]

Are there any contstraints on the fields in the offending table? If, for
example, you have a foreign key field in your table and no value is being
assigned to it, you will get such an error.

 

[Mark A. Sam]

Are there any contstraints on the fields in the offending table?

No.

If, for

example, you have a foreign key field in your table and no value is being
assigned to it, you will get such an error.

The only key is an Idenity column, but I doubt that is the problem. I use
the dbSeeChanges option when setting the recordset variable. This problem
only occurs when an order (the main Table) is created and products entered
immeditely aferward. But that isn't always true according ot the users. If
I open the form to an existing record, enter products, then populate
materials and tasks, the procedure will work fine.

This may have something to do with Identify Insert being turned on. In my
code I remmed out lines which assigned values to the Identity column.
Apparently that was working but caused problems when adding records through
the form. How can to turn the Identity column off?

 

[Klatuu]

I don't know, sorry.

 

[gllincoln]

If an order is entered, however, and the database closed and reopened or the

user switches to another record and returns, then the problem doesn't occur.

Any help on how to solve this would be apprecated.

Hi Mark,

Rereading your quoted observation gave me a fresh insight. It isn't a lock - it's something within your update code that conflicts with something else sometimes, or possibly bad compilation due to reference conflicts, or ????

What ever the cause - it's certainly a code or interpretation problem if your update code isn't working right, but, (as stated) the form will save changes when you edit or add a record then navigate away from the record so that the form's current record loses focus. That mean's Access's native code that saves the data is working when your code isn't. I don't know exactly why, but given your observation, this appears to be true.

Since the root cause appears to be elusive -
My amended recommendation is do exactly what does work per your stated observation.

On the order entry form - instead of running your current update code - make a 'save order' button
that navigates to a previous record or the first record or whatever, and then returns via a saved bookmark.

Hope this helps...
Gordon

 

[Mark A. Sam]

Hello Gordon,

I think your recommendation is fine, except that this form is loaded with subforms inside of subforms, which would make it exceding slow to navigate out and back. It is very slow changing to another record.

I am going to try to use append queries to transfer the data. I'm not sure why I didn't do that when I first developed the procedure, but it may have been just the habit of using DAO. I'll let you know how it goes.

God Bless,

Mark

If an order is entered, however, and the database closed and reopened or the
user switches to another record and returns, then the problem doesn't occur.

Any help on how to solve this would be apprecated.

Hi Mark,

Rereading your quoted observation gave me a fresh insight. It isn't a lock - it's something within your update code that conflicts with something else sometimes, or possibly bad compilation due to reference conflicts, or ????

What ever the cause - it's certainly a code or interpretation problem if your update code isn't working right, but, (as stated) the form will save changes when you edit or add a record then navigate away from the record so that the form's current record loses focus. That mean's Access's native code that saves the data is working when your code isn't. I don't know exactly why, but given your observation, this appears to be true.

Since the root cause appears to be elusive -
My amended recommendation is do exactly what does work per your stated observation.

On the order entry form - instead of running your current update code - make a 'save order' button
that navigates to a previous record or the first record or whatever, and then returns via a saved bookmark.

Hope this helps...
Gordon