Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

intermittent ODBC connection error when updating recordset

Posted on 2012-03-21
15
Medium Priority
?
427 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 58
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 58
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 58
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
 
LVL 58
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 58
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 58
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 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

663 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