Results 1 to 13 of 13

Thread: Timing issue with ADODB.connection/.MDB database

  1. #1

    Thread Starter
    New Member
    Join Date
    Dec 2016
    Location
    usa
    Posts
    6

    Timing issue with ADODB.connection/.MDB database

    This is my first post.

    First and foremost, I found a workaround already.

    Secondly, I'm a software developer intern and proven myself enough to work on projects like this, so forgive me as I haven't had a lot of "working" experience although I'm about to graduate etc.

    Anyway, this program creates an .MDB database from text data that is output from another in house system. It then allows the user to perform manipulations on it. Then, it allows output of the .MDB into Excel by certain business rules (dictated by who consumes the output)

    I've been able to knock out problem after problem in this application, including this one, but I don't like the way I solved it.

    Original:
    Code:
    rs.Open "select count(*) as Cnt from QAQC_Log", frmMain.cn
    Used to check count from .MDB and determine program flow later. frmMain.cn is a ADODB.connection to an MDB file opened elsewhere. rs is a ADODB.Recordset

    The problem is that only sometimes it would crash the entire program at this point with "Run-time error " '-2147217865 (80040e37)': The Microsoft Jet Database engine cannot find the input table or query 'QAQC_Log'. Make sure it exists and that its name is spelled correctly."

    The problem is the .mdb does, and always does have this table at this point of execution.

    I tried a bunch of different things, but what eventually worked was creating a wait statement and waiting for a bit, then continuing execution. I had a lead this would work because when I went into debug mode after the error, and clicked resume, it would run fine. It took me a while to come to that conclusion though.

    Working Code
    Code:
    wait (2500) 'Line below this has timing issues, this is a workaround.  Increase if needed.
    rs.Open "select count(*) as Cnt from QAQC_Log", frmMain.cn 'The Trouble Statement!
    Problem is, this is such workaround code and I know it. I've tried creating code that waits for frmMain.cn.State to be Open instead of executing but it never catches (it stays in the open state it seems, execute state isn't the problem?) for example, my current code block in question
    Code:
                '-----------WIP
                Debug.Print ("")
                Debug.Print (Format(Now, "yyyy-MM-dd HH:nn:ss") & Right(Format(Timer, "#0.000"), 4))
                Debug.Print (frmMain.cn.ConnectionString)
                'Debug.Print ("rs.State: " & rs.State)'rs likely not the culprit
                'Debug.Print ("frmMain.cn.State: " & frmMain.cn.State)
                'Do While (frmMain.cn.State And ObjectStateEnum.adStateExecuting) > 0
                '    Debug.Print ("cn not ready")
                'Loop
                Debug.Print ("")
                '-----------END WIP
                wait (2500) 'Line below this has timing issues, this is a workaround.  Increase if needed.
                rs.Open "select count(*) as Cnt from QAQC_Log", frmMain.cn 'The Trouble Statement!
    I don't know. Perhaps one of you veterans knows something I don't about working with these classes/objects. Oh, here is the connection string

    Code:
    Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=A:\BusBook\debug\Feb17_passtimes.mdb;Mode=Share Deny None;Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=4;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;

    Sorry if my post is bad, my first time really asking for help or advice on a forum relating to programming.

  2. #2
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Timing issue with ADODB.connection/.MDB database

    Data Source=A:\BusBook\debug\Feb17_passtimes.mdb
    A: ????? Surely you are not using a floppy?

    Where/when is that table being created. You say it is there at that point in the execution but are you sure because you also say that if you wait a short period you get no error. Of course given that if you were to go and look when the error occurs it may very well be there but still may not have been when the code executed.

    Personally I have been working regularly with MDB files in VB for about 20 years and I have never had it fail to see a table that was actually there.

  3. #3
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,522

    Re: Timing issue with ADODB.connection/.MDB database

    I wonder if there is a way to query the schema model to see if it exists first... or incorporating it into the query so that it doesn't tank.

    I don't know enough about Access to get into it that far. The only time I've seen tables "fail" is when they are generated on the fly (either created right up or dropped and re-created) and the log hasn't quite caught up ... it almost sounds like the case here. The connection string is interesting... I haven't see "A:\" in anything in a long time. If you are in fact on a floppy there, that is likely your problem. You've got a latency issue where the file write to the table hasn't finished yet. It's what is known as a race condition. First thing I'd do is move that database off the floppy and get it on to the hard drive and see if the problem still persists.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: Timing issue with ADODB.connection/.MDB database

    If an async open was done on the Connection that could also be a problem. Seems very unlikely though since most newbies copy/paste crude synchronous sample code derived from ancient ASP scripts.

  5. #5
    PowerPoster
    Join Date
    Jun 2015
    Posts
    2,224

    Re: Timing issue with ADODB.connection/.MDB database

    is A: is a mapped network share? maybe you can use a connection timeout.

  6. #6

    Thread Starter
    New Member
    Join Date
    Dec 2016
    Location
    usa
    Posts
    6

    Re: Timing issue with ADODB.connection/.MDB database

    Quote Originally Posted by DataMiser View Post
    A: ????? Surely you are not using a floppy?

    Where/when is that table being created. You say it is there at that point in the execution but are you sure because you also say that if you wait a short period you get no error. Of course given that if you were to go and look when the error occurs it may very well be there but still may not have been when the code executed.

    Personally I have been working regularly with MDB files in VB for about 20 years and I have never had it fail to see a table that was actually there.
    Not a floppy, dear god, can you imagine haha. I know traditionally A was used for floppy, but I chose it randomly to map a network drive. Yeah exactly, it is hard to tell if the table is actually there during error-ed execution. Still trying to figure out a way.

  7. #7

    Thread Starter
    New Member
    Join Date
    Dec 2016
    Location
    usa
    Posts
    6

    Re: Timing issue with ADODB.connection/.MDB database

    Quote Originally Posted by techgnome View Post
    I wonder if there is a way to query the schema model to see if it exists first... or incorporating it into the query so that it doesn't tank.

    I don't know enough about Access to get into it that far. The only time I've seen tables "fail" is when they are generated on the fly (either created right up or dropped and re-created) and the log hasn't quite caught up ... it almost sounds like the case here. The connection string is interesting... I haven't see "A:\" in anything in a long time. If you are in fact on a floppy there, that is likely your problem. You've got a latency issue where the file write to the table hasn't finished yet. It's what is known as a race condition. First thing I'd do is move that database off the floppy and get it on to the hard drive and see if the problem still persists.

    -tg
    Hmm yeah I could try some hacking around with the sql query... interesting idea. Perhaps query'ing the schema first too, I will see what I can do.

    Which "log" are you referring to when tables are generated on the fly?

    Not on a floppy, haha. A is network drive, sorry for the confusion.

  8. #8

    Thread Starter
    New Member
    Join Date
    Dec 2016
    Location
    usa
    Posts
    6

    Re: Timing issue with ADODB.connection/.MDB database

    Quote Originally Posted by dilettante View Post
    If an async open was done on the Connection that could also be a problem. Seems very unlikely though since most newbies copy/paste crude synchronous sample code derived from ancient ASP scripts.
    Nope, just a very basic connection string is specified, so defaults otherwise.

  9. #9

    Thread Starter
    New Member
    Join Date
    Dec 2016
    Location
    usa
    Posts
    6

    Re: Timing issue with ADODB.connection/.MDB database

    Quote Originally Posted by DEXWERX View Post
    is A: is a mapped network share? maybe you can use a connection timeout.
    Yes network share. What would connection timeout help?

  10. #10

    Thread Starter
    New Member
    Join Date
    Dec 2016
    Location
    usa
    Posts
    6

    Re: Timing issue with ADODB.connection/.MDB database

    Sorry for the multiple posts. I will condense it into one in the future. I just wanted to say thanks for all your replies, I repped all of you.

    Quick note for DataMiser, I wanted to ammend/add on to this thought process: Basically I was mistaken when I wrote in the original post that "the table always exists at this point" what I mean to say, what is only known is that the code to create/insert this table etc. is called before this problem line of code.

    What is so weird about all of this, is it isn't like this vb6 application is multithreaded implicitly. Like, this wasn't built by dedicated software developers, I would highly doubt they would even know what threading is. What I'm getting at is there has to be some object (if that is what its called in vb speak) that is acting async on its own thread by internal design. This is so strange!

  11. #11
    Frenzied Member
    Join Date
    Mar 2008
    Posts
    1,210

    Re: Timing issue with ADODB.connection/.MDB database

    Personally I would not map a share to drive A: or B:. Just be sure that is not causing a problem try mapping the share to a letter greater than B:

  12. #12
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,207

    Re: Timing issue with ADODB.connection/.MDB database

    Quote Originally Posted by luminos1ty View Post
    What is so weird about all of this, is it isn't like this vb6 application is multithreaded implicitly.
    It probably isn't - but Timers (or the occasional DoEvents-Call) could cause the effects you see.

    In your Opener-Post you write: "...this program creates an .MDB database from text data that is output from another in house system."

    My question would be: "How does it do that, exactly?"

    Is this importing-task from a Text-File done only once, at startup of this Application -
    if yes - how often does this App "start-up" - is it maybe done in a cycically triggered Batch-Process?

    Or is it done "cyclically at App-Level" (e.g. in a Timer)?
    And if done in such a repeating fashion - are you really sure there are no DoEvents in this routine
    (in case your Text-Import is a time-consuming operation)?

    If there are no DoEvents involved whilst running the Import-Task, is there maybe a (compiled)
    instance still running, doing the cyclic Imports (maybe from another machine), whilst you develop
    against the same DB-File (against the same Share-Location)?

    And how exactly does this Import-Routine ensure "the new Table-Data"?

    This goes along with techgnomes thoughts, that "someone else" might be changing the Schema on that DB (perhaps cyclically).
    E.g. when you want the new to import Text-Data to land in a "fresh Table" - what exaclty are the Steps for that?
    What I mean is, that this could be done basically in two ways:
    1) - SQL: "Drop Table SomeTable" ... "Create Table SomeTable(FieldList,...)" ... fill-in the new Text-data
    2) - SQL: "Delete From SomeTable" ... fill-in the new Text-data

    The first one does Schema-Changes, whilst the second one does not.

    Also note, that there's always the chance that "using the *.mdb on a Share" is the culprit.
    Starting with, that choosing "A:" or "B:" for the Share-Mapping is probably not a good idea -
    followed by the concern, that not all Shares (related to the OS-instance which serves them)
    work "well enough with the locking-mechanisms, a Desktop-DB requires for MultiUser-Mode".

    E.g. when the Share is hosted on a Linux- or some other "non-Windows-OS" (as often found
    on these little NAS-Boxes), then File-exchange is usually working well enough and stable, but
    performing high-frequent requests (moving FilePointers and such) on the same File (from different Clients),
    as well as setting/removing FileLocks in a higher frequency - and in the correct order - this
    is where some SMB/CIF-implementations (on the Server-machine which hosts the Share)
    might cause problems - with the effect that you begin to "loose the *.mdb" somehow (as in
    "weird behaviour starting to happen").

    To rule out some of that, you could start with developing against a true local-copy of
    the Text-Import-File and a true local copy of the DB-File (on your C: Drive) - if the errors
    don't ever happen again in this "local mode", then the Share is in all likelihood the culprit.

    If the problems remain (in local mode), then you will probably have some "Timer- or DoEvents- problem" in the VB-App itself.

    Olaf

  13. #13
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: Timing issue with ADODB.connection/.MDB database

    Another thought:

    If you are using multiple Connection objects opened to the same database it is possible to see any number of strange "timing" issues due to caching. In order to maintain performance and reduce network traffic writes normally get posted to the file in a "lazy" manner. The Jet OLEDB Provider exposes a number of tweaks to alter this behavior in special cases. Depending on what you are doing Connection Pooling can be going on under the covers as well... which means even when your code "closes" a Connection it doesn't really get closed.

    Normally a program should open one Connection and keep it open and use it for everything, only closing it when the program ends or you know you are completely done with it. If you are not doing this then all bets are off. Each Connection is a separate client, and at that point concurrency control becomes very important even if only one copy of the program is being run.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width