Hi there
I have an excel sheet which queries a MySQL database.
It all runs well (if connected first, which it does when the Workbook is first opened)
However I am now trying to detect when there is no connection (for whatever reason) so it reconnects again before running any queries.
The problem is that (if not connected), the VBA code doesn't go to the error_handling line
I would asume the On Error GoTo error_handling line would make the code jump to error_handling line when an error happens.
But it doesn't. The program just breaks on the next line:
.Open sql_query
and a big message comes up saying err 3709 bla bla bla
Here is some of the code:
Public oConn As ADODB.Connection
' Create a recordset object.
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset
Dim rangearray As Variant
Set rangearray = Nothing
With rsPubs
' Assign the Connection object.
.ActiveConnection = oConn
sql_query = "SELECT DISTINCT etc etc etc";
On Error GoTo error_handling ' <<<<<<<<<< DOESN'T WORK ???
.Open sql_query
' Copy the records into cell A5
Range("A5").CopyFromRecordset rsPubs
lastrow = Range("B65536").End(xlUp).Row
rangearray = Range("A1:Z" & lastrow)
Range("M2") = rangearray(lastrow, 1)
.Close
End With
EXIT SUB
error_handling:
if err.number=3709 then
connect_to_MySQL ' << THIS ROUTINE CONNECTS TO MYSQL DB
.Open sql_query
resume next
else
end if
I hope someone can help
I have an excel sheet which queries a MySQL database.
It all runs well (if connected first, which it does when the Workbook is first opened)
However I am now trying to detect when there is no connection (for whatever reason) so it reconnects again before running any queries.
The problem is that (if not connected), the VBA code doesn't go to the error_handling line
I would asume the On Error GoTo error_handling line would make the code jump to error_handling line when an error happens.
But it doesn't. The program just breaks on the next line:
.Open sql_query
and a big message comes up saying err 3709 bla bla bla
Here is some of the code:
Public oConn As ADODB.Connection
' Create a recordset object.
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset
Dim rangearray As Variant
Set rangearray = Nothing
With rsPubs
' Assign the Connection object.
.ActiveConnection = oConn
sql_query = "SELECT DISTINCT etc etc etc";
On Error GoTo error_handling ' <<<<<<<<<< DOESN'T WORK ???
.Open sql_query
' Copy the records into cell A5
Range("A5").CopyFromRecordset rsPubs
lastrow = Range("B65536").End(xlUp).Row
rangearray = Range("A1:Z" & lastrow)
Range("M2") = rangearray(lastrow, 1)
.Close
End With
EXIT SUB
error_handling:
if err.number=3709 then
connect_to_MySQL ' << THIS ROUTINE CONNECTS TO MYSQL DB
.Open sql_query
resume next
else
end if
I hope someone can help