Solved

Access with SQL backend on an Azure machine randomly drops connection

Posted on 2015-02-22
14
152 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +2
14 Comments
 
LVL 51

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 85

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 70

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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 51

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 70

Expert Comment

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

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
 

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 51

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 48

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 51

Expert Comment

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

Thanks Andy, please do.

/gustav
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

630 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