create a Connection name in the VBA code

[almagg]

so i have this.
now whenever it runs it creates a new connnection which is incremented i.e.,

Connection
Connection1
Connection2
etc...

i know i can go in the Connection/Properties window and create a named connection but then the excel file would have to be saved. the same if i put the query or the stored query name in the properties window.

what i want to do is create the connection name in the code so at the end i can delete the connection.
this way anyone can run it and there would not be a problem with any existing names or a lot of accumulated connections.

Sub CreateAccessQuery()

Dim objMyConn As New ADODB.Connection
Dim objMyRecordSet As New ADODB.Recordset
Dim strSQL As String
Dim objMyQueryTable 'As QueryTable

'This is the Access source (on my machine at home)

ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Program Files\Microsoft Office\Access Northwind\Northwind 2007.accdb;Persist Security Info=False"

'Somewhere in here create a 'named' connection so it can be deleted at the end.

objMyConn.Open ConnectionString

'strSQL = "[Product Orders]" 'if i were using the stored query

strSQL = "SELECT [Order Details].[Product ID], Orders.[Order ID], Orders.[Order Date], Orders.[Shipped Date], Orders.[Customer ID], [Order Details].Quantity, [Order Details].[Unit Price], [Order Details].Discount, ""Sale"" AS [Transaction], [Customers Extended].Company AS [Company Name], [Order Details].[Status ID]" & _
"FROM ([Customers Extended] INNER JOIN Orders ON [Customers Extended].ID = Orders.[Customer ID]) INNER JOIN [Order Details] ON Orders.[Order ID] = [Order Details].[Order ID]" & _
"ORDER BY Orders.[Order Date];"

objMyRecordSet.Open strSQL, objMyConn, adOpenKeyset

Set objMyQueryTable = ActiveSheet.QueryTables.Add(objMyRecordSet, Range("A1"))
objMyQueryTable.Refresh True

objMyRecordSet.Close
objMyConn.Close

Set objMyRecordSet = Nothing
Set objMyConn = Nothing

'Here I would delete the Connection
ActiveWorkbook.Connections("Connection").Delete

End Sub

thanks

 

[almagg]

i came across one solution which is delete the sheet which has the connection.
that would be ok.

just wondering if it is possible to actually 'name' the connection on the fly.