asked on

Access 2010 DSN-less connection to SQL Server drops in VBA

I have an Access 2010 front end linking to SQL Server 2014 using the following procedure for creating a DSN-less connection:

https://support.microsoft.com/en-us/kb/892490

The linked tables work ok most of the time, except that it looks like the connection is periodically dropping. It just randomly happens, not consistent. Here's an interesting twist...when I get a connection error, it is always trying to open a recordset in code, but you can still open the linked table from within Access itself.

Below is a code sample. The error comes up on rst.Open. Sometimes the error goes away by re-linking the tables via Linked Table Manager, by doing a Compact and Repair, or by closing/reopening the database (which relinks the tables programmatically via AttachDSNLessTable from the link above). However, sometimes the error continues and only after closing/reopening or manually re-linking a few more times does it eventually comes back up. Could this be a network connectivity issue or is there something else going on? Is there a way to trap the error and relink programmatically?

Dim rst As New ADODB.Recordset
Dim str As String
str = "SELECT * FROM dbo_TicketTripInfo WHERE TicketID = " & TicketID
rst.Open str, CurrentProject.Connection, adOpenStatic, adLockOptimistic

Any input greatly appreciated!

crystal (strive4peace) - Microsoft MVP, Access

are you on a laptop? if so, perhaps the Power Options need to be set so that nothing is done to conserve the battery

Perhaps qualify the * ?
SELECT dbo_TicketTripInfo.* FROM dbo_TicketTripInfo ...

> Is there a way to trap the error and relink programmatically?

you should be able to capture the error number. Then test for that and take action accordingly (uncomment the select case statements) ... but if you can open the table manually but not programmatically, that is indeed strange

   On Error GoTo Proc_Err

   ' code statements

   Dim db As DAO.Database _
      , rs As DAO.Recordset

   ' code statements
 
Proc_Exit:
   On Error Resume Next
   'release object variables 
   if Not rs is Nothing then
      rs.close
      set rs = Nothing
   end if
   Set db = Nothing
   Exit function
  
Proc_Err:
'   select case err.number
'   case 123 'substitute the error number
      ' code statements
'   case 456 'substitute the error number
      ' code statements
'   else
       MsgBox Err.Description, , _
            "ERROR " & Err.Number _
            & "   ProcedureName " 'substitute the Procedure Name 
'   end select
   Resume Proc_Exit
   ' Ctrl-Break the MsgBox... then Debug
   ' then set Resume to be the next statement by right-clicking on it
   '            and choosing Set Next Statement from the shortcut menu
   ' then press F8 to step through code one line at a time to see what is wrong
   Resume

Open in new window

EE videos:

basic error handling code for VBA (3:48)
https://www.experts-exchange.com/videos/1478/Excel-Error-Handling-Part-1-Basic-Concepts.html

Error Handling Part 3 - Run and Fix Bugs (7:51)
https://www.experts-exchange.com/videos/1518/Excel-Error-Handling-Part-3-Run-and-Fix-Bugs.html

lorigottschalk

ASKER

No, it isn't a laptop. There are three desktops all connecting (via a wired connection through a hub) to a SQL database on one of them. Also, we are using ADO rather than DAO as in your error example above. Do you know if there are any reasons why ADO would be problematic?

ASKER CERTIFIED SOLUTION

crystal (strive4peace) - Microsoft MVP, Access

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lorigottschalk

ASKER

Do you mean that when I open a linked table directly in Access (which I mentioned usually still works even when it fails to open from the code) it is using DAO to connect? Could that explain why one works and the other doesn't?

crystal (strive4peace) - Microsoft MVP, Access

that seems to make sense although I meant that when Access is getting data from its tables, it is using DAO. The table is already linked, all you need is to open a recordset from it. Perhaps try using DAO in the code and see if it helps

PatHartman

I also only use DAO since that is the native Access method but I have never heard that ADO is the problem in this situation. I have always found it to be the network or in one case a bad version of firewall firmware on the server.

crystal (strive4peace) - Microsoft MVP, Access

I am not saying it is because I have no way to test what is happening in your environment ... perhaps try it and find out? Are you not using a linked table?

lorigottschalk

ASKER

Yes, we are using linked tables. I will try DAO next time it errors out and see if that helps. @PatHartman, if it is a network issue, I'm not sure the best course of action. Do you think trying to trap the network error with code is helpful, or is it better to go back to the network guys and make them deal with it?

PatHartman

Once the network connection drops, it is difficult to reestablish it programatically. Make sure ALL users are using a separate copy of the FE since if they are using a shared copy, what happens to one, happens to all.

Closing Access and reopening it is usually sufficient as long as the blip was transient. I train my users to always close the app if they see a message with "ODBC" in it. Then reopen and try again.

lorigottschalk

ASKER

I got the error and updated the code to use DAO; problem went away! How strange. I thought ADO was the newer technology, but for whatever reason this works. Thanks for your help!

PatHartman

Since the errors are intermittent, how do you know that converting to DAO solved the problem?

lorigottschalk

ASKER

I waited until I got the error, then while the project was still running replaced the ADO code with DAO code. The error went away immediately in that particular piece of code, but the error continued to come up in another part of the program that was still using the ADO method. I even reverted back to the ADO code and it came up again, so it seems pretty clear that fixed it. Wish I knew why!

PatHartman

Thanks for explaining your testing method. But I still don't think that fixed it. I think the DAO code created a new thread and so reestablished the connection whereas the ADO code was using an existing thread. If the DAO code ever stops and you remember this conversation, please try to switch to ADO and I think you'll see that it works.

You'll know if it keeps happening.

lorigottschalk

ASKER

If that were the case, why did the error come back up when I reverted back to the ADO code and went away again as soon as I replaced it with the DAO code? It was reproducible at that point.

At any rate, I'll post an update if it comes up again. For the moment, it appears that all the errors have gone away!

crystal (strive4peace) - Microsoft MVP, Access

Great! ~ happy to help. Thank you for testing.

ADO is a different technology than DAO. When Access 2000 made ADO the 'default' library for data access, a lot of confusion arose. Both technologies are needed and they both have their strengths. Now DAO is the default library again.