QueryTables.Refresh and Insert SQL

[daan.pretorius]

Hi,

This is my 1st post on Mr Excel! - For years I just read and used solutions posted by others but I finally registered and hope I can also post something that somebody else can use.

I have a list of MySQL databases (All sitting on remote machines) which I want to extract information from. The connections is on a page called "Static Data", where the name of the data source is in column B, the sheet name it must query to is in column C and the connection string (DSN less) is in column E. Should I want to query a specific database, I mark the row in Column A with a "x". It only queries the MySQL databases which have a "x" until it gets to the end of the list. If a line was selected it opens a new page for that query onto the sheet name in column C. If the query was successful, it colours the line green, otherwise it colours red. I type a normal SELECT statement into Worksheets "Static Data" in Range "B2" (also named Range("SQL"). eg. SELECT * from ledger_transactions where ledger_transaction_type=1 etc etc.

My problem is with the qt.Refresh line. With select queries this works fine. I try to use this only when it is SELECT queries (With the If), like in the example below, but when I use INSERT, it errors on this line but still do the INSERT. If I take out the .refresh line the INSERT does not happen?

'If UCase(Left(Range("SQL").Value, 6)) = "SELECT" Then
qt.Refresh False
'End If


My Question: What other procedure can be used to "execute" the INSERT statement

The consolidateq is a procedure that consolidates all the lines marked with "x" into one page.

Thanks in advance.

Daan Pretorius



Sub runq()

Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False


Dim continue As Boolean
Dim x, y As Double

y = 6
While Worksheets("Static Data").Range("B" + Trim(Str)).Value <> ""
y = y + 1
Wend


Range("B6:B" + Trim(Str(y - 1))).Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With


x = 6

While Worksheets("Static Data").Range("B" + Trim(Str(x))).Value <> ""

continue = True
On Error GoTo err:

Dim conn As String
Dim sheetname As String

sheetname = Worksheets("Static Data").Range("C" + Trim(Str(x))).Value

If Worksheets("Static Data").Range("deletesheets").Value = "Yes" Or Worksheets("Static Data").Range("A" + Trim(Str(x))).Value = "x" Then
If SheetExists(sheetname) Then
Worksheets(sheetname).Delete
End If
End If


If Worksheets("Static Data").Range("A" + Trim(Str(x))).Value = "x" Then

conn = Worksheets("Static Data").Range("E" + Trim(Str(x))).Value

Sheets.Add
ActiveSheet.Name = sheetname
ActiveSheet.Range("A1").Value = Worksheets("Static Data").Range("B2").Value
ActiveWindow.Zoom = 85

Dim a As QueryTable

For Each a In ActiveSheet.QueryTables
a.Delete
Next

Dim b As Double

b = ActiveWorkbook.Connections.Count

While b > 0
If ActiveWorkbook.Connections(b).Name = "" Then
ActiveWorkbook.Connections(b).Delete
End If
b = b - 1
Wend

connstring = conn
sqlstring = Worksheets("Static Data").Range("B2").Value

Set qt = ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A4"), Sql:=sqlstring)
qt.FillAdjacentFormulas = True

'If UCase(Left(Range("SQL").Value, 6)) = "SELECT" Then
qt.Refresh False
'End If


End If


If continue = True And Worksheets("Static Data").Range("A" + Trim(Str(x))).Value = "x" Then

Worksheets("Static Data").Select
Range("B" + Trim(Str(x))).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
.PatternTintAndShade = 0
End With

End If
x = x + 1

Wend

If Range("autoconsolidate") = "Yes" Then
consolidateq
End If


err:

If err.Number <> 0 Then
continue = False
Worksheets("Static Data").Select
Range("B" + Trim(Str(x))).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Worksheets("Static Data").Range("A" + Trim(Str(x))).Value = "Failed"
Resume Next
End If

 

[Fazza]

HI Daan. Suggest using ADO instead of query tables. Query tables are OK for basic stuff but there are better approaches. These old threads are not specific to your question but some have links that will be. Google will find lots of info on ADO. If searching within the forum, suggested terms are INSERT & ADO. HTH, Fazza

http://www.mrexcel.com/forum/showthread.php?t=315768

http://www.mrexcel.com/forum/showthread.php?t=332472

http://www.mrexcel.com/forum/showthread.php?t=379290

http://www.mrexcel.com/forum/showthread.php?t=300478

 

[daan.pretorius]

Hi Fazza,

Thanks for the reply. It seems that ADO will work better that Querytables!

Thanks for putting me on the right track.


Regards



Daan