Solved

intermittent ODBC connection error when updating recordset

Posted on 2012-03-21
15
417 Views
Last Modified: 2012-04-11
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
Comment
Question by:L_Malchiodi
  • 7
  • 7
15 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37749869
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
 
LVL 57
ID: 37749875
<<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
 

Author Comment

by:L_Malchiodi
ID: 37754017
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
 
LVL 57
ID: 37754079
I don't see these values:

lngHistItn, strHistDTime, strUserName, strCurrentForm, strIntrnKeyVal, strShortHistText

Being set any where...

Jim.
0
 

Author Comment

by:L_Malchiodi
ID: 37754096
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
 
LVL 57
ID: 37754289
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
 

Author Comment

by:L_Malchiodi
ID: 37754478
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 57
ID: 37756936
<<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
 
LVL 57
ID: 37756952
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
 

Author Comment

by:L_Malchiodi
ID: 37768340
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
 

Author Comment

by:L_Malchiodi
ID: 37768435
I'm not turning up any additional errors in the ODBC errors collection.
0
 
LVL 57
ID: 37768607
<<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
 

Author Comment

by:L_Malchiodi
ID: 37771429
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
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 37771512
<<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
 

Author Closing Comment

by:L_Malchiodi
ID: 37832895
Jim - we decided to break the data into chunks, and that worked well for us.  Thanks!
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now