MS Query "Connections"

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a sheet that lets the user enter a chart of accounts in a column &
then a macro runs against each account (using QueryTable), hits an SQL
database, and returns data. My problem is that it adds a new connection each
time the mcro runs. I just deleted about 400 connections.

Is there a way to reuse the same connection (it's all going against the same
server/database) or is there a way to automatically delete connetions?

Thanks.
 
hmmm....I'm calling QueryTable inside a For loop in a single VB macro. The
loop will run for as many cells of data the user adds to a column in the
spread sheet.

I'm doing a Activesheet.QueryTable.Add.....

I'm not sure how to "refresh".

Can I do ...QueryTable.Refresh.... instead of Add?

Is there no "drop connection" command or method?

THANKS!
 
Create the variable that represents the connection as a global variable.
Before doing anything, open the connection. With each query, check the state
of the connection:

If oCn.State = adStateOpen Then
'do nothing
ELSE
oCn.open
END IF
 
OK, you're not using ADODB. I'm not too familiar with what you are doing.

ADODB gives you lots of flexibility for creating your queries and returning
results. And, you can close the connection after each query.

Option Explicit
Sub RunStoredProcedures()
Dim rng As Range
Dim intRows As Integer
Dim intParams As Integer

Dim objConn As New ADODB.Connection
Dim objCmd As New ADODB.Command
Dim objRs As New ADODB.Recordset

' On Error GoTo ErrHandler:

Application.ScreenUpdating = False

Range("A2").Resize(500, 10).ClearContents

' Connect to the data source.
Set objConn = GetNewConnection
objCmd.ActiveConnection = objConn

' Set CommandText equal to the stored procedure name.
objCmd.CommandText = "dbo.vbMSA_Rankings2"
objCmd.CommandType = adCmdStoredProc


' Automatically fill in parameter info from stored procedure.
objCmd.Parameters.Refresh

intParams = objCmd.Parameters.Count - 1 'first one is RETURN value

' call the stored procedure
objCmd(1) = Range("ticker")
objCmd(2) = 1 ' signifies we want PENDING

objCmd(0).Direction = adParamReturnValue
Set objRs = objCmd.Execute

If objRs.EOF = True Then
MsgBox "No Data", vbCritical
GoTo ErrHandler
End If

Set rng = Range("TopLeft")
rng.CopyFromRecordset objRs
Set rng = Range("Bank")
intRows = 1 + Range("B5000").End(xlUp).Row - rng.Row


ActiveSheet.Names.Add Name:="Print_Area", _
RefersTo:="=" & rng.Resize(intRows, 10).Address

Range("ticker").Offset(2, 0).Value =
objRs.NextRecordset.Fields("Name").Value

Application.ScreenUpdating = True

rng.Select


ErrHandler:
'clean up

If objConn.State = adStateOpen Then
objConn.Close
End If

Set objConn = Nothing
Set objCmd = Nothing

'Application.Calculation = xlCalculationAutomatic

If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
Stop
Resume 'Next
End If

End Sub


Private Function GetNewConnection() As ADODB.Connection
Dim oCn As New ADODB.Connection
Dim sCnStr As String

sCnStr = "Provider=sqloledb;User ID=duke; pwd=buckeyes; " & _
"Data Source=sandbox; Initial Catalog=BranchData"


oCn.Open sCnStr

If oCn.State = adStateOpen Then
Set GetNewConnection = oCn
End If

End Function
 
You may add this to your macro to delete the names before/after your fetch.

For Each Name In Sheets("Data").Names
Name.Delete
Next Name
========
or use a refresh like this with variable interspersed.

With sheets("sheet1").QueryTables(1)
.Connection = _
"URL;http://www.speedtv.com/schedule/index.php?m=&do=&week=" & X &
"&ts=&wholeMonth=&subcat=&program=&usecal=yes&startMonday=1"
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingAll
.WebTables = "1,""speedListing"""
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
=======
or

Sub GP()
With Sheets("sheet1").QueryTables(1)
.Connection = _
"URL;http://postcalc.usps.gov/MailServices.aspx?Country=Domestic&M="
& [mytype] & "&P=0&O=" & [myounces] & "&OZ=78734&DZ=78731"
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
 
Once you build the query, then yes, you just use the refresh property after
you change the string. The query is automatically dropped.. What you are
talking in the names collection are not connections.
 
Yesterday after debugging the macro, I opend up my "connections" button and
there were like a hundred and eighty connections, connection1 -
connection189. Those are the one's I'm concerned about. I can delete them
but I can't always be certain the user will remember to delete them. I don't
know how many connections Excel will create before it crashes.

All I did was record a macro of connecting to SQL Server and downloading
some data. Excel built the macro actually using QueryList....I changed it to
QueryTable....now it seems I should be using ADO. Sigh.....

Thanks!
 
Thanks. But I don't have names. I have a querytable connection inside a for
loop:

For Counter = 1 To 100 '....pick a number, I
could probably count the number of populated cells and get an exact #...

With Worksheets("Sheet1")
Result = ActiveCell.Offset(0, 1).Address 'Place return data
one cell to the right of current cell
End With
Worksheets("Sheet1").Range(Result).Clear 'Clear result cell
If MasterAccount = "" Then GoTo NEXTFOR 'Skip empty cells
If MasterAccount = "***" Then Exit For 'End of file token

With Range(Result).Cells 'Format return data
.NumberFormat = "$###,###,###.00"
.Font.Bold = True
.Font.Size = 10
.Font.Color = RGB(75, 0, 255)
.Font.TintAndShade = -0.5
End With

If InStr(MasterAccount, "~") = 0 Then 'check to see if two
account#'s concantenated
FirstAccountNumber = MasterAccount 'single account, set
account number parameter for query
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER=SQL
Server;SERVER=LLCSERVER2;UID=john.church;Trusted_Connection=Yes;APP=2007
Microsoft Office system; " & _
"WSID=JC;DATABASE= & DataB & ",
Destination:=Range(Result), Sql:= _
"Select Sum(AMTRANDTL.AMTRDTranAmt) from AMTRANDTL "
& _
"left outer join GLACCTHDR on GLACCTHDR.GLACHNumber
= AMTRANDTL.GLACHNumber " & _
"left outer join AMTRANHDR on AMTRANHDR.AMTRHNumber
= AMTRANDTL.AMTRHNumber " & _
"where GLACHCode= '" & FirstAccountNumber & "' and
AMTRANHDR.AMTRHPeriod = '" & Period & "' Group By GLACCTHDR.GLACHCode")
.FieldNames = False
.RefreshStyle = xlOverwriteCells
.PreserveColumnInfo = True
.AdjustColumnWidth = False
.Refresh BackgroundQuery:=False
End With

Don Guillett said:
You may add this to your macro to delete the names before/after your fetch.

For Each Name In Sheets("Data").Names
Name.Delete
Next Name
========
or use a refresh like this with variable interspersed.

With sheets("sheet1").QueryTables(1)
.Connection = _
"URL;http://www.speedtv.com/schedule/index.php?m=&do=&week=" & X &
"&ts=&wholeMonth=&subcat=&program=&usecal=yes&startMonday=1"
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingAll
.WebTables = "1,""speedListing"""
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
=======
or

Sub GP()
With Sheets("sheet1").QueryTables(1)
.Connection = _
"URL;http://postcalc.usps.gov/MailServices.aspx?Country=Domestic&M="
& [mytype] & "&P=0&O=" & [myounces] & "&OZ=78734&DZ=78731"
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
--
Don Guillett
SalesAid Software
(e-mail address removed)
Johnslg said:
I have a sheet that lets the user enter a chart of accounts in a column &
then a macro runs against each account (using QueryTable), hits an SQL
database, and returns data. My problem is that it adds a new connection
each
time the mcro runs. I just deleted about 400 connections.

Is there a way to reuse the same connection (it's all going against the
same
server/database) or is there a way to automatically delete connetions?

Thanks.
Click to expand...
Click to expand...
 
Can you pass a sql string with ADO? I'm using parameters & was trying to
keep things simple.



Sub SQL_Query_Test()

Dim Period As String, FirstAccountNumber As String, SecondAccountNumber
As String, Result As String, MasterAccount As String, DataB As String
Dim MyPos As Integer, MyLength As Integer

Worksheets("Sheet1").Range("B4").Activate 'sets active cell to
first cell with account data

With Worksheets("Sheet1")
Period = .Range("B2") 'cell B2 contains
the period user enters
DataB = .Range("B1")
MasterAccount = ActiveCell 'Account Number of
first account to be queried
End With

For Counter = 1 To 100 '....pick a number,
I could probably count the number of populated cells and get an exact #...

With Worksheets("Sheet1")
Result = ActiveCell.Offset(0, 1).Address 'Place return data
one cell to the right of current cell
End With
Worksheets("Sheet1").Range(Result).Clear 'Clear result cell
If MasterAccount = "" Then GoTo NEXTFOR 'Skip empty cells
If MasterAccount = "***" Then Exit For 'End of file token

With Range(Result).Cells 'Format return data
.NumberFormat = "$###,###,###.00"
.Font.Bold = True
.Font.Size = 10
.Font.Color = RGB(75, 0, 255)
.Font.TintAndShade = -0.5
End With

If InStr(MasterAccount, "~") = 0 Then 'check to see if two
account#'s concantenated
FirstAccountNumber = MasterAccount 'single account, set
account number parameter for query
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER=SQL
Server;SERVER=LLCSERVER2;UID=john.church;Trusted_Connection=Yes;APP=2007
Microsoft Office system; " & _
"WSID=JC;DATABASE= & DataB & ",
Destination:=Range(Result), Sql:= _
"Select Sum(AMTRANDTL.AMTRDTranAmt) from AMTRANDTL "
& _
"left outer join GLACCTHDR on GLACCTHDR.GLACHNumber
= AMTRANDTL.GLACHNumber " & _
"left outer join AMTRANHDR on AMTRANHDR.AMTRHNumber
= AMTRANDTL.AMTRHNumber " & _
"where GLACHCode= '" & FirstAccountNumber & "' and
AMTRANHDR.AMTRHPeriod = '" & Period & "' Group By GLACCTHDR.GLACHCode")
.FieldNames = False
.RefreshStyle = xlOverwriteCells
.PreserveColumnInfo = True
.AdjustColumnWidth = False
.Refresh BackgroundQuery:=False
End With
 
Yes - you can pass a query string through ADO. HOWEVER, your query really
ought to be converted to a stored procedure, allowing you to use code very
much like what I posted, so that you simply pass in 2 parameters. The reason
for using the stored procedure route is that it is faster.

If you truly want to pass in the query string, change

objCmd.CommandText = "dbo.vbMSA_Rankings2"
objCmd.CommandType = adCmdStoredProc
to
objCmd.CommandText = your query string
objCmd.CommandType = adCmdText

and drop all the stuff relating to parameters

The key is that you can open & close the ADODB connection at will, and if
you are cycling through a number of account numbers on a sheet, open the
connection once and close it when you are done AND in your error handler.
 
Thanks. I'll start playing around with this......

I suppose after I get it all debugged I could use stored procedures. I may
be hitting different databases for different companies but I could probably
handle that with my connection string.
 
Why when I try to add a ADODB connection type like this:

Sub SQL_Query_Test()

Dim cnnConnect As New ADODB.Connection

....
...
..

do I get a compile error: User-defined type not defined


???
 
I'm going to use the sql string until I get it debugged.....question
Set cnnConnect = New ADODB.Connection
cnnConnect.Open
"Provider=SQLOLEDB;SERVER=LLCSERVER2;UID=john.church;Trusted_Connection=Yes;DATABASE=" & DataB & " "

can I pull the sever & database & user information out of the connection
string & have the user polled for input?
 
In the Data ribbon (xl2007 - query table is kind of a POA...it has to be
refreshed and every time it refreshes it creates a new connection)

.....BUT....I switched my code to ADO and (almost) everything is going
fine....thanks to you and Mr. Carey.

I just have one last problem. I want to pop a window and ask the user to
input UID/PWD to connect :

cnnConnect.Open "Provider=SQLOLEDB;SERVER=" & SvrName & ";DATABASE=" &
dbName & ";UID=" & user & ";PWD=" & pass & " "

This fails. If I replace UID/PWD with "trusted_Connection=yes" it logs in.

We use windows authentication, no user accounts in SQL server. Is this
connection string trying to find a SQL Server account????
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Question on Deleting Querytables 3
Connecting Questions on Querytables 1
QueryTables, some basic questions 5
Need Help with Refreshing Querytables. 8
call multiple macros in workbook_open? 6
Querytable Class Module 3
how delete old querytables? 2
Change SQL in VBA 0

Back
Top