VB6 (-2146500594) Automation error

Posted on 2009-04-16
Last Modified: 2012-06-27

I have an application build in VB6 that works fine on every computer but one. The process where the error occurred is an Import that use a .xls file to pass information to an Oracle DB, using Access to create a temporal "database" to process the information.

On my PC the process imports successfully 50,000 items in 2 hours and a half. I have a computer with WindowsXP, 4GB of RAM and a local connection of 1,000 Mega Bits with  Access 97 installed. I use Oracle11 and the computer where the Oracle Server is installed is only used for this.

The person that is having the problem is importing the same .xls file with 50,000 items but the process take FOREVER and fails after 9 hours with the (-2146500594) Automation error.

I am discarding that is a problem with the data (.xls file) since I'm able to do it without any problem in my computer. The person that is having this problem has a similar computer than mine, with the exception that his Oracle Server is not exclusive and he has so much information than me on his server, and he has Access 2003 installed on his computer.

I think that is a problem with a lost connection or something like that due to the import with him takes so many hours.

Any ideas of how can I solve this?
Question by:mharo
    LVL 16

    Expert Comment

    Almost every single time I've gotten an "Automation error", it has been because a compatible dll could not be found.  Now when you get into database access, there seems to be tons of dependancy dlls.  Have you tried using the VB "Package & Deployment Wizard" to create an install to ensure the correct versions of all dlls are installed on this computer?  (Of course even the Wizard doesn't seem to ALWAYS find ALL the dependantcies).

    As a gut feeling guess... I don't think the 9 hour process time and then "Automation error" are nessesarily linked.  The 9 hours can potentially be explained by other things happening on this person's machine.  If that is the case, then where you might want to look at exactly what is causing the "Automation error" is something that occurs after the bulk of your processing loop.  In other words, as a guess, since the process runs for 9 hours before failure, what ever is the bulk of the loop might be doing just great.  But then once the main loop is done and it goes to do the next step, that's where a dependant dll might be missing.

    But then again, since we're talking about a database, I could see where after 9 hours, something goes wrong with the connection and that causes the error... but that's not the first area I would look.

    Given how long it takes to generate the error, you might want to put together a DEBUG version of your project.  Basically, make a copy of the project and write a globle loging function.  Have the login function take a string variable as the message to write to a log file.  Have the function open the log file for append and append the message.  Then go back to major points in your code and put a bunch of log message.  For example, the the extent that you can, at the start of every function, call the logging function passing the function name.  Make sure to include messages at the start, in the middle, and at the end of primary looping logic.  And basically, given how long it takes to cause the error, try to put in as many messages as possible.  Then run the application and use the log to determine how far along the logic got before it blew up.  The KEY here is to NOT keep the log file open otherwise you risk losing data.  Each time open the file, write to it, and close it.  Once you run the DEBUG version, perhaps over the weekend, you'll have a clue as to which section of the code is causing the problem.  Then you can go into that section of the code and add more logging messages, perhaps after every single line of code, and try again.  Given that it takes 9 hours before failure, this obviously could take a while.  But using such a method you will likely be able to narrow down the problem to a single line of code by Wednesday (about 3 or 4 itterations).

    Author Comment

    Hi HooKooDooKu
    Thanks for your comment.

    The application is installed by a "Installer Wizard" in every computer so  everything is supposedly installed correctly in each machine. We have tried the re installation already several times in the machine that fails and the error is still there :(

    I already have a log file that give me the exact time when commits to DB are made and exception presented with the data (such a wrong name or something like that) I'll try to write more message like you are suggesting which will give me a clue of where the code is failing.

    My next step as you suggested also is install the dev environment in the computer that has the problem to try to debug my code there.

    Thanks for all your suggestion, I'll let you know what happens after trying all this.
    LVL 16

    Expert Comment

    "My next step as you suggested also is install the dev environment in the computer that has the problem to try to debug my code there."

    If you can do that, then cool... that will obviously allow you to find out exactly what line of code is causing the error without writting additional code.  Didn't think to mention that idea as I'm usually having to figure out where an automation error is occuring at a remote client site..

    Your idea should work perfectly at determining where the error occurs.  Just make sure under 'Tools/Options' to select "Break on Unhandled Errors" and it should stop right where the error is occuring the 1st time you run it.

    Author Comment


    I was able finally to reproduced the error on my computer.

    I add a MsgBox in the class and function that the Automation error appears throwing the Err.Description this time and the error after 4 hours in my computer was a little different.

    "Method '~' of object '~' failed." I was doing a research on the web about this error and everybody mention the MDAC or some DLL not properly registry. But I really don't understand why this error appears after several hours of working properly. I don't get it.

    Please Help! :(

    Accepted Solution


    I really never found out what the problem was with this Automation error issue and probably I'll never do, but I found a way to prevent this error to happen.

    Speaking on my case in specific I guess that this error appears because a class with a dictionary was called SO MANY times, because once that I convinced the user to import a smaller file or a file with a few fields empty the problem was solved.

    I guess VB gets tired of call a class or a dictionary so many times and fails without any reason and that's why the Automation error appears. I don't know I'm just guessing :(

    Any way. Thanks for your help.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
    I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
    This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    754 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

    16 Experts available now in Live!

    Get 1:1 Help Now