Is there a way to detect if establishing a data connection has failed using VBA before the warning message boxes appear?

Question

My concern is that there is a read-write lock in the file I want to connect to because it is being used by an application as shown: Locked MS Access File

When I try to refresh the data connection in MS Excel, these errors/notifications occur because it is unable to make a connection to the MS Access file that is being used:

I am wondering if there is a way for VBA to detect if establishing a data connection has failed. I have no idea how to approach it and the VBA code to refresh the query is shown below:

With ActiveWorkbook.Connections("Query - MS Access File")
    .OLEDBConnection.BackgroundQuery = False
    .Refresh
End With

The connection string of my Excel data connection to a Access file is shown below:

Provider=Microsoft.ACE.OLEDB.12.0;
User ID=Admin;
Data Source=C:\Users\ACER\Desktop\Test.MDB;
Mode=Share Deny Write;
Extended Properties="";
Jet OLEDB:System database="";
Jet OLEDB:Registry Path="";
Jet OLEDB:Engine Type=5;
Jet OLEDB:Database Locking Mode=0;
Jet OLEDB:Global Partial Bulk Ops=2;
Jet OLEDB:Global Bulk Transactions=1;
Jet OLEDB:New Database Password="";
Jet OLEDB:Create System Database=False;
Jet OLEDB:Encrypt Database=False;
Jet OLEDB:Don't Copy Locale on Compact=False;
Jet OLEDB:Compact Without Replica Repair=False;
Jet OLEDB:SFP=False;
Jet OLEDB:Support Complex Data=False

score 0 · Answer 1 · edited Sep 10 '18 at 10:07

0

You should try to use the method state to check your connection:

if ActiveWorkbook.Connections("Query - MS Access File").OLEDBConnection.State <> 1 then
   ' Your connection is not ready
end if

edited Sep 10 '18 at 10:07

Erik A

31,639
12
42
67

answered Sep 10 '18 at 09:30

kiks73

3,718
3
25
52

1

Thanks @Vityala I took the code from C# and forgot to convert to VB – kiks73 Sep 10 '18 at 09:46
@kiks73 - you are welcome. However `ConnectionState.Open` is also a .net option. In VBA, the constant is simply `1` - https://stackoverflow.com/a/17611807/5448626 – Vityata Sep 10 '18 at 09:49
@kiks73 Thanks for the answer although I get a `Run-time error 438: Object doesn't support this property or method` error. I use this code: `If (ActiveWorkbook.Connections("Query - Test").OLEDBConnection.State <> ConnectionState.Open) Then MsgBox "Failed to connect" End If` – Pherdindy Sep 10 '18 at 09:51
@Pherdindy - see the answer from the comment above, it is quite well written. – Vityata Sep 10 '18 at 09:52
@kiks73 The error must be somewhere in `.OLEDBConnection.State` I still get the same error. Is there any class or module I should retrieve? In the `OLEDBConnection` class in the `Object Browser` there is no member with the name `State` – Pherdindy Sep 10 '18 at 10:02

score 0 · Answer 2 · answered Sep 10 '18 at 10:05

0

In VBA the .IsConnected is a boolean property which works like this:

If ActiveWorkbook.Connections("Query - MS Access File").OLEDBConnection.IsConnected Then

It helps taking a look at the VBA built-in libraries, by pressing Ctrl+Space and looking for something meaningful:

OLEDBConnection.IsConnected Property (Excel)

answered Sep 10 '18 at 10:05

Vityata

42,633
8
55
100

Thanks I never knew about the shortcut for the built-in libraries besides `F2`. Although my concern is: I have a refresh code in the `Workbook.Open()` method and is meant to refresh all data connections. By default everything is not connected when I open it, and i'm wondering if it's possible for VBA to actually catch if establishing connection has failed before all warning message boxes as shown in the main post appears. So I can do something like: `If connection has failed, then use this alternative method to connect`. I have a VBA alternative to bypass the failure but it's much slower – Pherdindy Sep 10 '18 at 10:14

Is there a way to detect if establishing a data connection has failed using VBA before the warning message boxes appear?

2 Answers2