Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 31
  1. #16
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    Quote Originally Posted by Gicu View Post
    I don't think you need to do anything at all, I would suggest simply adjust the ODBC refresh interval to something that works for you:
    Attachment 49485
    Requerying is updating the record source of a form or report (or row sources of combos and listboxes) but if you use dLookup to search for the ticket it will not help.
    Cheers,
    I think I will do that then. Is there anyway to manually do that such as maybe a form refresh?

    So refreshing tabledef connections and recreating the connections with the [FixConnections()] function are only for if there are structural changes in the table.
    Requerying the recordset will update the record source making it re-retrieve all the records and set it to the first record.
    Similarly lowering the odbc refresh interval will refresh and check for changes without bringing you to the first record but is on a timer instead of on command.



    Please correct me if I am wrong and thank you both so much for teaching me! I greatly appreciate it!

  2. #17
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Yes, I think you got it, with the small correction that the form requery would reset it to the first record, the recordset.requery should leave you on the current record (but I did have one occasion when it was also resetting the form to go to the first one).

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #18
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    May have missed this being covered, but if not

    Don't confuse requery with refresh, they do different things so advisable not to mix the terminology.

    Requerying a recordset will completely repopulate the recordset so will add or remove records actioned by others as well as any field value changed by others as dictated by the recordsource. Refreshing a recordset on the other hand simply returns the (revised) field values for the existing recordset - i.e. reflects changes made by others but does not add any new ones whilst deleted ones will be replaced with 'deleted'. I've never felt the need to test it but if user A opens a recordset with the criteria of say 'invoiceDate=date()' and whilst this recordset is open user B changes that invoice date to a date other than today. On refresh user A will see the revised date, even tho' it is now excluded by virtue of the criteria. On requery, the record would disappear.

  4. #19
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    Quote Originally Posted by Gicu View Post
    Yes, I think you got it, with the small correction that the form requery would reset it to the first record, the recordset.requery should leave you on the current record (but I did have one occasion when it was also resetting the form to go to the first one).

    Cheers,
    So after letting the form sit idle open for a while it prompts me to type in the password for the SQL server again. Using the tdf refresh doesn't fix it but using the FixConnections function that I linked in the OP fixes it.

    Is there anyway to make it so it won't lose the connection info? My goal is to make it super user friendly.
    Since the program will probably be left open all day if not left open all the time, I will probably make it so that after the connection times out it closes the main form and opens another form that prompts them to click a button to recreate the connections. Maybe it would be easier to just make it close out the entire database so they have to open it again. I just need to find out how long the connection stays active.

  5. #20
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    There are quite a few examples around for closing an Access database on idle:
    https://learn.microsoft.com/en-us/of...-or-inactivity
    https://www.iaccessworld.com/set-pro...automatically/
    Note that this method will fail if the computer itself goes to sleep before the timer runs out.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #21
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Opening a hidden form loaded with some records is supposed to maintain a persistent connection?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #22
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    Quote Originally Posted by Gicu View Post
    There are quite a few examples around for closing an Access database on idle:
    https://learn.microsoft.com/en-us/of...-or-inactivity
    https://www.iaccessworld.com/set-pro...automatically/
    Note that this method will fail if the computer itself goes to sleep before the timer runs out.

    Cheers,
    Thanks! I will look into these. I wish there was a way to detect when the connection is ending and recreate it automatically. I also need to look into some things I saw about optimizing the odbc connection. It currently takes about 2 full seconds (so long I know) but I wanna see if I can bring that down. It is connecting to about 10 tables.

    Is there any way to store the password and username in the code as variables and have it use them to reconnect when it detects disconnection? Or to just force the connection to stay open?


    Quote Originally Posted by Micron View Post
    Opening a hidden form loaded with some records is supposed to maintain a persistent connection?
    No, the idea is that the hidden form will come up after x amount of time and require them to click a button to bring up the main form. Said button will recreate the connections so that everything works smoothly.

    P.S. Apologies for the delayed reply. I only have access to here on the work computer.
    Edit: after some googling I can't seem to find anything on how long it holds the connection info before requiring you to login again. Any ideas?

  8. #23
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    OK, sorry but that really wasn't a question based on your situation. I'm saying that a hidden form with a record(s) is supposed to maintain a persistent connection, so I don't see the need for a button to refresh connection if that is true. I have never needed to use that method.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #24
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    If you put a timer on the hidden form to requery the data every 10 minutes or so , it should maintain the connection until the database is closed down.
    This isn't required on an Access backend but can be on a SQL backend as the connection is maintained differently.

    You may find if you change to a more recent SQL ODBC Driver that the connection no longer drops, as they have changed how Access interacts with the version 17 onwards SQL ODBC Client.
    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
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    Quote Originally Posted by Minty View Post
    If you put a timer on the hidden form to requery the data every 10 minutes or so , it should maintain the connection until the database is closed down.
    This isn't required on an Access backend but can be on a SQL backend as the connection is maintained differently.

    You may find if you change to a more recent SQL ODBC Driver that the connection no longer drops, as they have changed how Access interacts with the version 17 onwards SQL ODBC Client.
    I may recommend trying it to my boss. Hopefully it will work with Access 2000.
    Also that's kind of what I did. I made it so that after 30 minutes the hidden form pops up a message box saying that the program has disconnected and pressing "ok" runs the FixConnections Function.
    Although if requerying the data resets the idle disconnect timer then I may just do that since I believe the requerying was nigh instantaneous. Otherwise I might just leave the current method.



    Quote Originally Posted by Micron View Post
    OK, sorry but that really wasn't a question based on your situation. I'm saying that a hidden form with a record(s) is supposed to maintain a persistent connection, so I don't see the need for a button to refresh connection if that is true. I have never needed to use that method.
    I think it's like Minty said that the SQL database disconnects me after a time. Otherwise I don't know. I just left the form open for a few hours, came back and attempted to use it and it wanted me to re-enter the SQL username and password which people won't have access to. I know that the people this is intended for won't be closing the program often.

  11. #26
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    But the form you are referring to has no recordset, thus it is not making a connection, yes? Or did I miss that your form is actually loading records from a table?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #27
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    Quote Originally Posted by Micron View Post
    But the form you are referring to has no recordset, thus it is not making a connection, yes? Or did I miss that your form is actually loading records from a table?
    It is sort of loading records from a table.
    The combo box loads the names from the employee table.
    The textbox then uses DLookup in the after update event to search a linked table. So I suppose that part is not loading the entire recordset.

  13. #28
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    For anyone that stumbles upon this in the future; I decided to have a hidden form with a timeout. It isn't ideal but I also don't want a permanent connection to the SQL server if the program is left open.
    After x minutes the form creates a msgbox saying "Please press ok to reconnect" and then runs the code to relink the tables. (I have it relink them when opening the program because I noticed if people quit it out mid link then tables go missing and it causes errors. so I just reused the command here.) After linking the connection stays stored for however long access stores it (I couldn't find any documentation on how long it stores connections) or until the pop-up comes up, whichever is first.

  14. #29
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    As stated before you might lose the connection to a table but you wont lose the link details.
    Worst case normally is you simply need to close and reopen the form if the connection has dropped, and it will remake it. This won't happen if the data is being regularly accessed.

    I think you are confusing two different things -
    A) The Linked table details (e.g. the stored table def connection string)
    B) The actual current connection to the table.

    Your problem with tables going missing is precisely because you are relinking them, and not simply refreshing the connection by re-querying some data in the table.
    I use Azure hosted SQL databases in about 90% of our clients applications. I have never had to relink a table in normal use, unless I have changed its underlying design, or I've switched from a development BE to a live one and hence changed the BE database and therefore the connection string.
    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
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    Quote Originally Posted by Minty View Post
    As stated before you might lose the connection to a table but you wont lose the link details.
    Worst case normally is you simply need to close and reopen the form if the connection has dropped, and it will remake it. This won't happen if the data is being regularly accessed.

    I think you are confusing two different things -
    A) The Linked table details (e.g. the stored table def connection string)
    B) The actual current connection to the table.

    Your problem with tables going missing is precisely because you are relinking them, and not simply refreshing the connection by re-querying some data in the table.
    I use Azure hosted SQL databases in about 90% of our clients applications. I have never had to relink a table in normal use, unless I have changed its underlying design, or I've switched from a development BE to a live one and hence changed the BE database and therefore the connection string.
    When I was bug testing I had a few cases where if I closed it out while it was connecting and the linked table details (The actual table in the object menu) disappeared because the fixconnections script deletes them and reconnects them.
    I attempted the methods above to refresh the actual connection and there were some other things I discovered that this method would not fix so I just deemed it best to use the fixconnections function along with a function that checks to see if the tables exist and if they don't it creates that connection. Ensuring that the tables always exist and then refreshing the connection.

    I can't remember the other problem I had as it was a while ago but I know this method fixed it and worked.

    Currently I have a variable with multiple dimensions (I think thats how you say it? its 'Dim TableName(0 To 6) As String')
    Then a for loop that cycles through them and checks if they exist, else it creates the linked table using 'DoCmd.TransferDatabase acLink, "ODBC Database"'
    After that it Calls FixConnections to ensure that all the tables are actively connected (because say table 1 existed and table 2 was missing then this function would create table 2, which would then be linked but that does not refresh the connection for table 1).

    The only downsides of this are that it takes 5-10 seconds for access to load when this project is opened or when the msgbox pops up and runs this function it also takes 5-10 seconds.
    Granted if the timeout is set to 3 hours then 5-10 seconds is no big deal.

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

Similar Threads

  1. ODBC connection fail in remote computer
    By uoghk in forum Programming
    Replies: 3
    Last Post: 03-04-2022, 02:55 AM
  2. Replies: 2
    Last Post: 06-28-2013, 10:14 AM
  3. Replies: 4
    Last Post: 11-29-2012, 12:45 AM
  4. Replies: 1
    Last Post: 05-25-2012, 10:36 AM
  5. Replies: 7
    Last Post: 01-23-2011, 12:32 PM

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