• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 432
  • Last Modified:

intermittent ODBC connection error when updating recordset

We have a bit of code in our Access 2007 app that we use to update records in a Sybase database table, via ODBC:

   Set RecOut = db.OpenRecordset("dbo_udt_hist", dbOpenDynaset,dbAppendOnly)
             RecOut.AddNew
             RecOut!udt_hist_no = GetItn("udt_hist_no")
             RecOut!udt_hist_dtime = GetServerDTime()
             RecOut!user_name = gobjEnvironment.LogonUserName
             RecOut!udt_obj_form_name = "UtilImport"
             RecOut!intrn_key_val = IntrnKeyVal(strTblName, rec)
             RecOut!short_hist_text = strShortHistText
             RecOut.Update
             RecOut.Close

Open in new window


It works very nicely when we use it to update the dbo_udt_hist table for a single record, and has worked for many years.  We also use it inside a loop, where we may be updating a few hundred or even a thousand records.  Of course, we have the open and close outside the loop, so we're not doing that repetitively.

Now we've got a situation where we're doing this for upwards of 13000 records, inside a loop (we need to store an itn for each record in the dbo_udt_hist table).   It gets through about 2000 - 3000 iterations, and then blows up with this error.

-2147467259:[Sybase][ODBC Driver] Client unable to establish a connection.

Does anyone have any ideas?  Thanks!
0
L_Malchiodi
Asked:
L_Malchiodi
  • 7
  • 7
1 Solution
 
Jeffrey CoachmanMIS LiasonCommented:
Can you post the loop code?

Sound like a timing issue.

1. Check the ODBC settings in the Access Advanced options and adjust as needed.
odbc options
2. You may need strategically placed "DoEvents" as well..

let's see what other Experts may post...

JeffCoachman
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Does anyone have any ideas?  Thanks!>>

Try adding:

 Set RecOut = Nothing

at the end of the procedure.

 That's a shot in the dark though.  I'm thinking it has to do more with the driver or connection pooling, but it's possibly the server as well.

 One other thing I'd try; add a 1 second delay at the end of the procedure and see if it then gets farther.

 If it does, then I'd think it had more to do with the server getting hit too hard and fast.

Jim.
0
 
L_MalchiodiAuthor Commented:
After some thought, I'm coming at this from a different angle.  I don't think 13,000 rapid fire db saves over ODBC are ever going to work out well for us.  I'd like to avoid playing with the timeout if at all possible, since this application is installed on potentially hundreds of PCs at a client site.  And even adding a 1-second delay - well, with 13,000 iterations of that loop, that's 216 minutes to complete the process, which our customers will never find acceptable.

So, I've decided to save the records to a temp table in Access, then do a single save to our database via ODBC with INSERT....SELECT *.  That works very nicely in other areas of our application.

But, I'm having a problem with the INSERT.  

 
'create the temp table
strSQL = "CREATE TABLE temp_udt_hist (udt_hist_no LONG, udt_hist_dtime DATETIME, user_name TEXT(12), udt_obj_form_name TEXT(64), intrn_key_val TEXT (64), short_hist_text TEXT(250), full_hist_text TEXT(16))"
ExecuteActionQuery strSQL

'open a recordset and loop through the data in another database
strSQL = "SELECT * FROM " & strWrkTblName & " IN '" & mstrWorkFilePathAndName & "';"
Set rec = db.OpenRecordset(strSQL, dbOpenSnapshot)
rec.MoveLast
lngTotalRecords = rec.RecordCount
rec.MoveFirst

        
strShortHistText = "Record imported to table '" & strTblName & "'"
strUserName = gobjEnvironment.LogonUserName
strCurrentForm = "UtilImport"

Do Until rec.EOF
       strIntrnKeyVal = IntrnKeyVal(strTblName, rec)
       lngHistItn = GetItn("udt_hist_no")
       strHistDTime = GetServerDTime()
            
       strSQL = "INSERT INTO temp_udt_hist(udt_hist_no, udt_hist_dtime, user_name, udt_obj_form_name, intrn_key_val, short_hist_text, full_hist_text) " _
            & "values(lngHistItn, strHistDTime, strUserName, strCurrentForm, strIntrnKeyVal, strShortHistText, null)"
            ExecuteActionQuery strSQL
    

   rec.MoveNext
 Loop
 rec.Close

Open in new window


The CREATE works fine, I can see the table, it's got the right number of columns, etc.  All of the values that go into the INSERT are good, just as they should be.  But the insert blows up with this error:

3061: Too few parameters. Expected 6

Well, there are 7 columns in the temp table, and I'm inserting seven values.  Where is the "Expected 6" coming from?

I know it's a stupid syntax thing, I usually use Sybase T-SQL, and I'm sure I've goofed up something with Access's SQL syntax.

Thanks!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I don't see these values:

lngHistItn, strHistDTime, strUserName, strCurrentForm, strIntrnKeyVal, strShortHistText

Being set any where...

Jim.
0
 
L_MalchiodiAuthor Commented:
Yep, they're all there, above and below the "Do Until recEOF".  I didn't add the DIMs in my code sample, but they're there, too.

I've stepped through this, and confirmed that lngHistItn, strHistDTime, strUserName, strCurrentForm, strIntrnKeyVal and strShortHistText are all valued.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
oops, not sure why I didn't catch this first time around; their variables.  Right now, you've got them in there as literal strings:

       strSQL = "INSERT INTO temp_udt_hist(udt_hist_no, udt_hist_dtime, user_name, udt_obj_form_name, intrn_key_val, short_hist_text, full_hist_text) " _
            & "values(lngHistItn, strHistDTime, strUserName, strCurrentForm, strIntrnKeyVal, strShortHistText, null)"

Should be:

       strSQL = "INSERT INTO temp_udt_hist(udt_hist_no, udt_hist_dtime, user_name, udt_obj_form_name, intrn_key_val, short_hist_text, full_hist_text) " _
            & "values( " & lngHistItn & ", #" & strHistDTime & "#, '" &  strUserName & "','" &  strCurrentForm & "', '" &  strIntrnKeyVal & "','" & strShortHistText & "'," & NULL & ")"

You'll what I mean if you put a breakpoint on:
            ExecuteActionQuery strSQL

and print strSQL in the debug window; it will show the names of the variables and not the values.

Jim.
0
 
L_MalchiodiAuthor Commented:
Oh, duh!  Thank you!  I should have seen that myself.   I've corrected the syntax, and am now getting through the INSERT without that error.

BUT...I'm now back to the ODBC correction error I had originally.  All I can think of is that I'm holding that recordset (rec) open for too long while I process all of these rows, and I'm hitting  a timeout. Does that sound like a possibility?

Is there a more efficient way to do all this than the way I've coded it in the example?

Thanks!
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<UT...I'm now back to the ODBC correction error I had originally.  All I can think of is that I'm holding that recordset (rec) open for too long while I process all of these rows, and I'm hitting  a timeout. Does that sound like a possibility?>>

 Hum, don't think so.  The error message seems to indicate no connectivity.  Hard to say what the issue is.  Might even be a network/server problem.

<< Now we've got a situation where we're doing this for upwards of 13000 records, inside a loop (we need to store an itn for each record in the dbo_udt_hist table).   It gets through about 2000 - 3000 iterations, and then blows up with this error.  >>

 This implies that it's probably some sort of a resource limit.

What I'd do is:

1. Add some error trapping and check if there are not additional ODBC errors that might indicate more of what's going on (why the connection could not be established).  See:

ACC2000: How to Trap Specific ODBC Error Messages
http://support.microsoft.com/kb/209855

  Which shows you how to loop through the errors collection.  There may be nothing additional, but then again there might be.  Best to check.

2. Check that the ODBC driver in use is up to date.

3. Consider turning on ODBC logging so you can see the calls being made to the BE.  2000-3000 calls is going to result in a pretty big file, but it might give you a clue where the failure point is.

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
BTW I think your going to find that it's either a problem with the ODBC driver or with Sybase itself and/or the server it's running on and nothing your doing in Access per say.

So in addition to the above, I'd also:

1. check the event logs on the server Sybase is running on.

2. Check the Sybase logs (if any).

3. Monitor the server CPU/memory while the operation is taking place.

Jim.
0
 
L_MalchiodiAuthor Commented:
Thanks, Jim.  I'm playing with the ODBC error trapping now.  I've checked the Sybase logs on the server and don't see any errors there.  

How would I turn on ODBC logging?
0
 
L_MalchiodiAuthor Commented:
I'm not turning up any additional errors in the ODBC errors collection.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<How would I turn on ODBC logging?>>

 On the client side, start/admin tools, ODBC data manager. Click the tracing tab and select the options as appropriate.


<<I'm not turning up any additional errors in the ODBC errors collection.>>

  Not really surprised.  Seems to be more like some type of a resource leak then anything else.

  I'd also check the event logs on the client side for anything.

Jim.
0
 
L_MalchiodiAuthor Commented:
By resource leak, do you mean insufficient memory on the PC, or something like that?  

I've looked at the ODBC setttings in the Access Advanced options, and I'm not really sure where to start.  While that's really not a preferred option for us, I'd at least like to give it a try and see where it takes us.  Any suggestions on which parameters to alter that might affect this ?

Thanks!
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<By resource leak, do you mean insufficient memory on the PC, or something like that?  >>

More or less.   Some type of resource that is being consumed; memory, file handles, # of connections, etc.

A "leak" is where a resource is allocated, but then not given back up at the end.   A repeating operation then at some point fails and often not quite in the same place.  That's usually a good indication that it is a leak of some type.   This is a result of basically a bug in a driver or software product.

 That's why in my opening comment, I told you to do:

 Set RecOut = Nothing

Which makes sure that the RecOut object is released.  Wanted to make sure nothing on the Access side was hanging up.

 It's also why I said to check the client, sever, and database logs.  Quite often when a resource runs out, it will trigger some type of error.   I actually expected something to show up in one of those logs.

  But you also may be simply running into some type of built-in limit.  All products have them.  For Access, once such is 2048 table IDs.  When you hit it you get an error and that's it.

  At this point, the only thing I can suggest is to make sure your ODBC driver is up to date.   Beyond that, your only option is to break the operation into smaller chunks doing a couple thousand recs at a time and then repeating that.

 Sorry I don't have a better answer then that for you, but this is the type of thing where you can burn a lot of time on it and never get to the bottom of it.  In those cases, it's sometimes better just to cut your losses and move on.

 Doing a couple thousand records at a shot should be fairly workable, so I'd do that I think and call it happy.

Jim.
0
 
L_MalchiodiAuthor Commented:
Jim - we decided to break the data into chunks, and that worked well for us.  Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now