Page 2 of 6 FirstFirst 123456 LastLast
Results 16 to 30 of 82
  1. #16
    pdanes is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    Sep 2019
    Posts
    208


    Quote Originally Posted by Minty View Post
    Not true with the SQL drivers that is for certain, I would update to the latest ODBC Driver 17 for SQL Server.
    https://docs.microsoft.com/en-us/sql...l-server-ver15

    Can you alter your start-up code to do a simple
    SET rs = Currentdb.openrecordset ("SELECT top 1 from AnyOldLinkedTable" , dbseechanges)

    Then rs.movelast
    MsgBox rs.recordcount & " Record queried"

    And see if that stops it crashing?
    Had to change it to:
    Code:
    Set rs = CurrentDb.OpenRecordset("SELECT top 1 1 from AO_vwStratigrafieLito", dbOpenDynaset, dbSeeChanges)
    Downloaded the latest ODBC, per your link. Removed the old one and installed the new one. Msgbox shows 1 record and stops. It sits there until I click Ok, then continues, sometimes bombing and sometimes not.

  2. #17
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    My purpose was to see if having a connection open prevented the crash, in the short term.
    Or at least see if the problem was related to that.

    Move that simple test recordset code step by step further through your opening process, up to the point where a user would have to do something.

    Do you have any error handling in anywhere your opening code, if you do can I suggest you comment it all out while trying to fathom this out?
    (Everywhere - functions and subs that could be called etc.)

    The reason for this is that later versions of Access VBA are "fussier" about certain syntax's and things that used to work, even if not quite correct, now won't work or give you strange problems.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #18
    pdanes is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    Sep 2019
    Posts
    208
    Quote Originally Posted by Minty View Post
    My purpose was to see if having a connection open prevented the crash, in the short term.
    Or at least see if the problem was related to that.

    Move that simple test recordset code step by step further through your opening process, up to the point where a user would have to do something.

    Do you have any error handling in anywhere your opening code, if you do can I suggest you comment it all out while trying to fathom this out?
    (Everywhere - functions and subs that could be called etc.)

    The reason for this is that later versions of Access VBA are "fussier" about certain syntax's and things that used to work, even if not quite correct, now won't work or give you strange problems.
    I can try making that recordset a global a simply leave it open. I had it in the Load event, as a local variable.

    I have VERY little error handling in the entire app. My general programming philosophy is to test for acceptable conditions beforehand, rather than blindly try and subsequently recover from failure. Almost everything I can deal with this way, by simply detecting in advance that a certain action will not end well, and not even attempting it. Not always is that possible, but usually, and I do so whenever I can.

    I have tried single-stepping to find a failure point, but once again, there is no code running when Access just quits. I had your suggested recordset and Msgbox as the last thing in the main form's Load event. I caught it there and single-stepped through the next statement, which was End Sub. It did that and the debugger went silent - nothing further is being executed. The main screen sits quietly for a few seconds, then the entire Access application simply vanishes.

  4. #19
    pdanes is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    Sep 2019
    Posts
    208
    Thought I was onto something for a bit. I noticed that if I simply click somewhere on the main form, it seems to block the quitting, regardless of whether I actually hit a button that does something. I then tried putting an explicit SetFocus to a text box at the end of the form's Load event. I got five clean starts in a row, and was preparing to uncork a bottle, but the sixth attempt crashed again. But I kept on, putting a few explicit SetFocus calls in the start-up form, with DoEvents between, and it seems to have lessened the incident count. Purely a subjective impresson, and I've been fooled before, but maybe that is an indication. I suppose it's possible that something in the start-up form is not being explicitly de-allocated, and Access subsequently tries to reach for a construct that has since vanished. Clean-up of object variables has improved in recent years, but there may still be a few gremlins in the mix.

    Next step is to go through the start-up form with a microscope, and see if there is anything I should be explicitly de-allocating.

  5. #20
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I haven't read all the recent posts in detail so apologies if this has already been suggested.

    Coming back to my earlier point about a hidden form loaded at startup and remaining open in the background....
    Bind that form to a small linked table -ideally one field and one record to reduce overhead.
    This will also have the advantage of providing a persistent connection to the BE and so should improve performance as a side benefit.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #21
    pdanes is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    Sep 2019
    Posts
    208
    Quote Originally Posted by isladogs View Post
    I haven't read all the recent posts in detail so apologies if this has already been suggested.

    Coming back to my earlier point about a hidden form loaded at startup and remaining open in the background....
    Bind that form to a small linked table -ideally one field and one record to reduce overhead.
    This will also have the advantage of providing a persistent connection to the BE and so should improve performance as a side benefit.
    No, that has not been suggested. Thank you, I'll give it a try. Tomorrow - I'm home tonight (for a change), so I'll put that together in the morning and see what happens.

    Improved performance would also be great. I've got the SQL stuff pretty well tuned - when I execute the prepared queries directly in SSMS, it generally responds with zero noticeable lag. But sending the same query in from Access can sometimes take quite a while. It may be that persistent connection you mention - I think I recall reading something about that.

  7. #22
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Good luck. Let us know how it works for you.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #23
    pdanes is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    Sep 2019
    Posts
    208
    Quote Originally Posted by isladogs View Post
    Good luck. Let us know how it works for you.
    No luck. I put a 'KeepAlive' form in DB and open it in the start-up form, but the uncommanded closing continues.

    I've noticed some oddities, though.

    1. When the hourglass appears for more than an instant during the start-up form's activities, that hourglass then appears again on the main form after the start-up form closes, and shortly after that, Access quits. If the hourglass apears only briefly and changes quickly back to a standard pointer, everything is fine.

    2. When I click the X to close the entire database, the main form's Load event runs again, even though the form is already open. This seems to have started with the addition of the KeepAlive form, but that makes no sense to me. There is no code in that form, just one text field from a linked table. But it definitely runs. I have put in Debug.Print statements to verify, and it does run.

    Now trying to fiddle with the start-up form - separate some code out from the Open event into the Load event, put DoEvents after every new object instantiation and similar desperate measures.

  9. #24
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Quote Originally Posted by pdanes View Post
    1. When the hourglass appears for more than an instant during the start-up form's activities, that hourglass then appears again on the main form after the start-up form closes, and shortly after that, Access quits. If the hourglass apears only briefly and changes quickly back to a standard pointer, everything is fine.


    Now trying to fiddle with the start-up form - separate some code out from the Open event into the Load event, put DoEvents after every new object instantiation and similar desperate measures.
    This observation is probably critical. Something is occupying Access to make the hourglass appear.
    I would simply start commenting out chunks of code, or call a procedure that writes out something to a text file of progress through the stages so you can "see" where it keels over?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  10. #25
    pdanes is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    Sep 2019
    Posts
    208
    Quote Originally Posted by Minty View Post
    This observation is probably critical. Something is occupying Access to make the hourglass appear.
    I would simply start commenting out chunks of code, or call a procedure that writes out something to a text file of progress through the stages so you can "see" where it keels over?
    Yes, Access is doing something, but MY code is not. That has been the brain-frying part of this exercise. The main form's Load event finishes and all VBA code is done, verified multiple ways (debugger, title bar, toolbar Run icon...).

    If it were a simple matter of tracking executing VBA code until I find the spot where it falls on its face, I would not even be bothering the forum with it - that is well within my abilities. The problem here is that it runs, finishes, goes idle, and only several seconds after all activity has apparently ceased, Access simply winks out of existence.

    And this only happens sometimes, so changes do not immediately manifest as successful or not. This morning I was looking at something with one of the users, and on my development machine, it took around a dozen tries before it finally started and stayed. But just before that, on my way in, one of the girls who uses it stopped to tell me how great the new version was, and how it had stopped crashing. All morning it has been sporadic on my machine - good starts and failures, sometimes several in a row, sometimes alternating - completely random.

  11. #26
    pdanes is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    Sep 2019
    Posts
    208
    Verified - when I comment out the line that opens the invisible KeepAlive form, closing the database with X does NOT run the Load event of the main form. Uncommenting that line opens the form again, and makes the Load event run on database close again. Same behavior when the KeepAlive form is opened visible.

    I also tried putting a Debug.Print into the Load event of the KeepAlive form, to see if maybe all open forms are being activated. They are not - KeepAlive does nothing, but the main form's Load event keeps firing on database close, whenever the KeepAlive form is open.

  12. #27
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You say "maybe all open forms being activated" , does that mean you have some (a lot?) of forms open but not visible?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  13. #28
    pdanes is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    Sep 2019
    Posts
    208
    Quote Originally Posted by Minty View Post
    You say "maybe all open forms being activated" , does that mean you have some (a lot?) of forms open but not visible?
    No, only the main form. All other forms in the DB only open in dialog mode in reponse to user actions, and close when their task is completed. At this point, I also have the KeepAlive form open, based on a suggestion from another poster. Nothing else.

  14. #29
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I'm not sure short of seeing the code associated with the forms I can suggest much else.
    As the problem appears to be associated with changing to O365 I suspect it's either a ODBC issue or some code that was fine, that is now generating a random problem due to the VBA being updated, and becoming more "picky".

    I would probably remove all the extraneous DoEvents, and other test code and go back to the basic code, and if possible let us have a look.
    My final thought would be how are you validating against your SQL server for access?
    Is it a associated Domain login?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  15. #30
    pdanes is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    Sep 2019
    Posts
    208
    Quote Originally Posted by Minty View Post
    I'm not sure short of seeing the code associated with the forms I can suggest much else.
    As the problem appears to be associated with changing to O365 I suspect it's either a ODBC issue or some code that was fine, that is now generating a random problem due to the VBA being updated, and becoming more "picky".

    I would probably remove all the extraneous DoEvents, and other test code and go back to the basic code, and if possible let us have a look.
    My final thought would be how are you validating against your SQL server for access?
    Is it a associated Domain login?
    I have the latest ODBC driver, installed yesterday - no change.

    I can remove all DoEvents and Debug.Print statements that I have put to try tracking the problem.

    Access to the SQL Server machine is via Windows login credentials. No other validation is used, and access to the linked tables and queries that read and write those linked tables works fine, as long as I start the database with Shift, so that the start-up form does not run.

    I am willing to send the app to anyone who is interested. It won't run very far for anyone, since it immediately starts looking for the server, and quits (with a warning message) if communcation with the server is not established. It's also around 30MB, because it contains some graphic images. But there is nothing in it I would consider sensitive, or needing protection. Quite the opposite - if there is anything in my code that would benefit some else, they are more than welcome to swipe it.

Page 2 of 6 FirstFirst 123456 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Access Runtime Crash
    By martinmcnally in forum Access
    Replies: 12
    Last Post: 01-24-2023, 10:02 AM
  2. Access crash
    By JennyL in forum Access
    Replies: 6
    Last Post: 01-19-2017, 03:42 AM
  3. debugging access 2013 crash
    By DKP in forum Programming
    Replies: 6
    Last Post: 10-06-2014, 10:23 AM
  4. Access crash
    By Tjaaaa in forum Access
    Replies: 10
    Last Post: 03-07-2014, 05:41 PM
  5. Diagnose Access Crash
    By Gray in forum Forms
    Replies: 0
    Last Post: 05-28-2011, 01:50 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums