Excel VBA SQL Query

Question

I have built a macro, wherein data is first imported into 'Raw' worksheet. Before importing, 'Raw' sheet is first cleared of previous data.
Then i query it using SQL in VBA and get the final output in desired format.
Lately i have been facing an issue, which gives a error message "The Connection for viewing your linked Microsoft Excel Worksheet was lost".

The same macro works fine if i dont clear the 'Raw' sheet before importing data. Any help is appreciated.

Option Explicit
Public ROW As Integer
Public Cnn As New ADODB.Connection
Public Rs As New ADODB.Recordset
Public StrSQL As String

Public Sub OpenDB()
    If Cnn.State = adStateOpen Then Cnn.Close
    Cnn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & _
    ActiveWorkbook.Path & Application.PathSeparator & ThisWorkbook.Name
    Cnn.Open
End Sub

Public Sub CloseRS()
    If Rs.State = adStateOpen Then Rs.Close
    Rs.CursorLocation = adUseClient
End Sub

Sub MonthlyYorN()



StrSQL1 = "SELECT * FROM [RAW$] where SOME CONDITION"
StrSQL1 = "SELECT * FROM [RAW$] where SOME CONDITION2"

StrSQL = StrSQL1 & " UNION " & StrSQL2

CloseRS
OpenDB

Rs.Open StrSQL, Cnn, adOpenKeyset, adLockOptimistic
If Rs.RecordCount > 0 Then
'Do Something
End If
End Sub

So much code to do something so simple - why not just use MS Query — Dave Sexton
– Dave Sexton, Commented Jan 16, 2015 at 12:33
@DaveSexton I have trimmed the Query while posting. The actual StrSQL1 and StrSQL2 are little complex query. — Maruthi
– Maruthi, Commented Jan 16, 2015 at 12:37
You can paste SQL statements directly into MS Query (or via VBA) so no matter how complex your statement that wouldn't be an issue.MS Query would handle all the data refreshing of the worksheet for you. — Dave Sexton
– Dave Sexton, Commented Jan 16, 2015 at 14:59

ashleedawg · Accepted Answer · 2017-10-30 06:51:31Z

1

This is not a VBA issue, it's a problem with the ODBC Driver. Go to →ODBC to open Data Sources, then into the Advanced Settings for the Excel ODBC Driver, and make sure to turn on checkbox:

☑ Enable Automatic Reconnect

MS Access ODBC can experience the same problem, fixed the same way.

answered Oct 30, 2017 at 6:51

ashleedawg

21.9k9 gold badges82 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA SQL Query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related