Solved

Access with SQL backend on an Azure machine randomly drops connection

Posted on 2015-02-22
14
129 Views
Last Modified: 2015-04-11
Hey Everyone

Well I got my employment squared away so I am back in the saddle!  I have an issue with a previous program I worked on.

I have an Access front end that connects to the data on the backend on an Azure server running SQL (I hope I am saying all this correctly.  I know very little about SQL and Azure).

For the most part the connection works great for everyone.  Every so often though a person will be using the program and they lose connection to the Azure server.  They have to exit the Access program (I have an Exit button) and then restart it.  

There does not seem to be a pattern.  It could be happening for one person but yet another person, at that same time, is having no issue at all.

I would love to see if anyone has an idea as to what could be going on, but I would also like to put into the Access program some sort of monitor that watches the connection (maybe in the Timer of the Main Form) every minute (or whatever is a good time frame).  I would love to have a red and green light on the Main Form that shows the connection status plus I would like it to add data to a table when the connection goes down.  This way I could start seeing if there is any pattern.

Thank You all and for anyone who answered my previous questions, I am really sorry I dropped out of existence, I lost my main job and was scurrying to make sure my family was ok.

Andy
0
Comment
Question by:alevin16
  • 5
  • 4
  • 2
  • +2
14 Comments
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40625170
You have asked the same question before:

http://www.experts-exchange.com/Database/MS_Access/Q_28552377.html

So, appearantly, that solution did not work?

/gustav
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 40625380
Azure is cloud-based, so you're at the mercy of your internet connection, and the cloud host. Unfortunately, there's really no way to insure the scenario you describe won't happen again other than a rewrite to use technology more compliant with cloud-based data (like a web application that handles dropped connections inherently).

Regarding capturing data, you could certainly create a local table, and dump whatever data you need in there. If you use the "always open" form from the link gustav provided, you could add a timer to that form, and try to connect to the Azure server in that timer's event. I'd use ADO for this, since you can generally get more information from an ADO Connection regarding errors:


'/ declare these in the General Declarations section of the form
Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset

'/ do this when the form opens
con.ConnectionString = "Your Azure Connection String"
con.Open

'/ do this in the Timer's Tick event:
If con.State <> adStateOpen Then
  '/ write to your local table
End If

Note the ADODB.Connection object has an Errors property you could use. There can be multiple errors, so you need to loop through them:

Dim err As Error
For Each err in con.Errors
  '/ capture data here
Next

ADODB.Connection.Errors atricle:https://msdn.microsoft.com/en-us/library/windows/desktop/ms675299(v=vs.85).aspx

Of course, to use this you'll need a reference to the Microsoft Activex Data Objects library. There are several versions; pick one that is a newer version (like 2.7 or 2.8).
0
 
LVL 69

Assisted Solution

by:Éric Moreau
Éric Moreau earned 250 total points
ID: 40625486
This is a know fact that the Azure database connections are not as reliable that one-premise database connections.

EF6 has support for it: http://blogs.msdn.com/b/appfabriccat/archive/2010/12/11/sql-azure-and-entity-framework-connection-fault-handling.aspx

They even created a pattern for that: https://msdn.microsoft.com/en-us/library/hh680934(v=PandP.50).aspx#sec2
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40625507
So, at any time, one should be prepared that the connection - though appearantly alive - is dormant, thus will fail, and you should catch that error and retry?

/gustav
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 40625517
yes, welcome to the Azure world!
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40625524
Well, anything has advantages and disadvantages.

How often do these transients occur? And are they dependant on wether the application is idle or not? If so, Scott's method may have a positive impact.

/gustav
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:alevin16
ID: 40625587
Hello Everyone

Thank you so much for all the info.  I am going to see what I can do with this, and /gustav, I thought that original solution worked but it turned out it was just coincidence, I thought it was working but then a few weeks later it started again (damn that Azure, getting my hopes up :D)

I am going to try working on this today.  

Andy
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40625602
OK.

No hurry, but could you be so kind to report back on your findings?

I have recently discussed a setup just like this with a client as it is a quite cheap method for hosting a true database in the cloud.

/gustav
0
 

Author Comment

by:alevin16
ID: 40629267
Hello Everyone,

I absolutely will report back.  I am going to start trying these today and tomorrow.  I am going to try that always open form first to see if that has an effect.

Thank you all again!

Andy
0
 

Author Comment

by:alevin16
ID: 40663790
Hello Everyone

So far so good.  I am going to give it a few more weeks to see if the non connecting issue appears again and then I will close this down.

Thanks
Andy
0
 

Author Comment

by:alevin16
ID: 40718224
I am sorry I completely forgot I had it open.  Yes please do close it.  Thanks!
Andy
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40718520
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40718599
> I absolutely will report back.

Thanks Andy, please do.

/gustav
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

762 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

20 Experts available now in Live!

Get 1:1 Help Now