Access with SQL backend on an Azure machine randomly drops connection

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
alevin16Asked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
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
 
Gustav BrockCIOCommented:
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
 
Éric MoreauConnect With a Mentor Senior .Net ConsultantCommented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Gustav BrockCIOCommented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
yes, welcome to the Azure world!
0
 
Gustav BrockCIOCommented:
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
 
alevin16Author Commented:
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
 
Gustav BrockCIOCommented:
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
 
alevin16Author Commented:
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
 
alevin16Author Commented:
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
 
alevin16Author Commented:
I am sorry I completely forgot I had it open.  Yes please do close it.  Thanks!
Andy
0
 
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
 
Gustav BrockCIOCommented:
> I absolutely will report back.

Thanks Andy, please do.

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.