Results 1 to 5 of 5
  1. #1
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    156

    Random and permanent loss of connection to OBDC

    Recently moved my backend into SQL Server and the cloud.



    and now on two separate occasions I have completely lost the ability to connect to the ODBC.

    I will have been working in the access front end just updating data. then leave to go do other things and when I come back several hours later I am unable to access any of the linked tables.

    when ever clicking on a linked table I get an instant error message that says
    Click image for larger version. 

Name:	Cap1.jpg 
Views:	23 
Size:	18.0 KB 
ID:	45589
    its like its not even trying to connect.

    what is really odd is that I can still connect and access everything via SSMS and can even access the back end using Excel

    But the Access Front end is like it is lost or locked out.

    I have tried Rebooting the Access app, my computer, my router. tried accessing the backend (still from my computer but on a different network)


    I am also unable to re-link the tables using the Linked table manager

    Click image for larger version. 

Name:	Capture4.PNG 
Views:	23 
Size:	80.9 KB 
ID:	45593

    Click image for larger version. 

Name:	cap3.jpg 
Views:	23 
Size:	25.2 KB 
ID:	45591

    The first time I deleted all of the linked tables and went through the entire process of bringing everything back in and it worked fine.

    This time I just deleted one of the linked tables and went through the process of bringing it back in, and in doing so now all of the tables are working again.


    Any thoughts or experiences would be appreciated, Thank you
    Last edited by Tuckejam; 06-28-2021 at 01:27 PM. Reason: wrong image

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    2,107
    Your description is a little confusing - If all your tables are in SQL server then you should simply use the SQL ODBC Driver 17.
    Your linked table manager is indicating a separate ODBC source - which one is actually failing?

    Secondly - if you are using a laptop and a wireless connection the laptop can silently switch off the wireless adapter to "save power" this will lose any connection you had, forcing you to close and re-open the access DB to reestablish the connection.
    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. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    1,464
    I spotted this
    Recently moved my backend into SQL Server and the cloud.
    and thought that was a big No No ?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+access+vba



  4. #4
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    156
    Minty -
    yesterday while working through some things I realized that having the two sources is possibly the issue. in the process of getting thing thing up and running, first I split the database and put the backend in SQL Server that was on my computer.
    then found a service who would actually host the Backend in SQL Server (you know in an actual server). and I think having a local instance of SQL Server still running on my machine might be causing the issue.

    Welshgasman-
    I probably used a poor choice of words to describe what I ment.
    yes putting Access or SQL Server just into any "Cloud" I think is a no no. but MS Azure has solutions specifically for this. (although I did not use Azure as i was really struggling to understand it, couldn't get much help form Azure who just kept referring me to their online articles. and it felt like it was a massive enterprise size solution)

    I have five users who will access the database a few times a month. so Azure felt like getting a 90lb Jackhammer for driving a finishing nail.
    anyway I found a service who could properly Host the database on a actual server and handle the backend IT service i was struggling with.

    but if you were thinking I put it in google drive or something like that, no. I just meant the cloud as in no a local company server and also not Azure

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    1,464
    In my last place of work, we actually used an Access database created by a company running on their server, via a browser that ran on Terminal Server I think.?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+access+vba



Please reply to this thread with any new information or opinions.

Similar Threads

  1. OBDC Save connection String
    By jbcom41 in forum Access
    Replies: 5
    Last Post: 01-12-2021, 07:59 AM
  2. Replies: 19
    Last Post: 04-09-2018, 05:56 PM
  3. Replies: 2
    Last Post: 05-29-2015, 11:16 AM
  4. Random Loss of Data
    By jbstearns in forum Access
    Replies: 14
    Last Post: 01-26-2015, 02:40 PM
  5. Replies: 13
    Last Post: 07-01-2014, 12:17 PM

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