HELP! Insert into results in Run-time error 3155

[bev]

First, let me apologize in advance for posting a question which probably doesn't have enough information, but I'm hoping somebody can just help point me in the right direction.

In VBA in Access, I dynamically build an "INSERT INTO ..." statement. My program runs through hundreds of records, inserting them happily, and then throws up a Run-time error 3155 (ODBC Timeout).

I'm actually a newbie in terms of knowing how to debug my code. I've wracked my brain. I've displayed a msgbox that shows the "INSERT INTO ..." statement and I can't see any obvious difference between the ones that work and the ones that don't.

You folks are generally so very, very helpful that I'm hoping maybe you could point me in the right direction to try to solve this (like pointers in using the built-in debugging tools).

Thanks,
Bev

 

[jrbarnett]

Bev

A quick summary of how to use the debugging tools:

1. Go into the VB editor
2. Find the line that you want to stop your code at, and click the grey line next to the code you want it to stop at.
If it is done correctly, you will get a red dot in the left margin and the line of code will turn white on a red background.

3. Run your code in the normal way. When your program gets to the stage where the line will be executed, the VB editor will come up with that line highlighted on a yellow background.
You can then:
* View the contents of variables by hovering the mouse over it.
* dynamically change variable contents by putting assignment statements in the immediate window
(eg strVariable = "hello&quot
* Use the buttons on the debug toolbar to move forward one line at a time, out of the current sub/function/property routine etc or stop program execution altogether by clicking the Stop button on the toolbar.

John

 

[CajunCenturion]

Given that your program runs through hundreds of records, then throwups the following error:
Run-time error 3155 (ODBC Timeout)
I would have to wonder if you're not submitting records to your database faster than they call all be processed. I also wonder what the database engine is.

Stepping through the program as jrbarnett describes above is a great way to debug programs, but in this case, since this may be a timeout issue (ODBC Timeout), stepping through the program may not be your best approach.

Where, and what type, of database are you using, and how do you open it?

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[bev]

Thanks John and CajunCenturion. CajunCenturion, in answer to your questions: The backend is SQL 7. I'm using Access 2000 on the front end.

I've got a bit more info now about my problem. It seems that when I try to insert OR update a record which has the VALUE '114' on a VARIABLE called Service_Code, I get the timeout! I know it's hard to believe! I couldn't believe it myself and I kept hunting and hunting through my code for anything that could make this happen!!

I finally ran a record with this value in this variable through the same code but output it to a test copy of the table I'm writing to. No timeout error! I went back to my production table and when I got to the error (with an UPDATE this time, since I'd manually entered the row that would not insert), I went and started entering the line of code that was causing the timeout in the Immediate Window. I tried one variable after another checking that it didn't give an error, looking at the table to see that it was actually updating the values. This time I tried to change FROM a "114" value in the table and update it. It timed out on me again!

I thought maybe the index had gotten corrupted, since the variable Service_Code is indexed. I reindexed the variable, but there was no change.

I ran CHECKTABLE and no errors were found!

At this point, as near as I can tell, there's some problem with value "114" in the Service_Code variable. Through code I can neither INSERT nor UPDATE a record using "114" as a value. Nor can I change a record that has "114" in the the Service_Code variable to another value. Other values in that variable all seem to work fine!

Last night when I asked for help, I thought this was true, but it seemed too unlikely.

Any suggestion on things I should look at or try?

Bev
GO BLUE!

 

[CajunCenturion]

What does 114 in the Service_Code mean? Although the actual meaning is not important, it may help determine where to look next.

Are there any triggers on that table? Is so, what do the triggers do? (Are the triggers condional on the value of Service_Code?)

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[cmmrfrds]

It appears from what you have written that you could use the native OLE-DB driver with ADO instead of going through odbc. Are you linking the tables, is that why you are using odbc? Since the sql is being built dynamically there should be no need to use odbc. Can you go to Query Analyzer and insert the record that way? If so, then try the ADO approach. If you need an example, paste in the sql and connection information you are using.

 

[bev]

I wanted to thank you all for your help. I am now looking into ADO and OLEDB, but for the future. It turns out that by removing the Service_Code index, the problem was eliminated. (Of course I do not know why.)

What I can tell you though is the the field Service_Code only has 118 values, whereas the Table has 1,080,355 rows. So I'm sure the INSERT INTO and UPDATE statements were using a table scan anyway.

So I'm now a happy camper!

Thanks again!

Bev