How to name ADO connection while creating it in Excel VBA

Question

How to name ADO connection with VBA? Using ADO method of the following VBA code adds new connection which is named by default "Connection" or "Connection2" etc.

Sub Make_ADO_Connection()
Sheets(2).Cells.ClearContents

Dim oCn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim ConnString As String
Dim SQL As String

strFile = ThisWorkbook.FullName
ConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
Debug.Print ConnString

Set oCn = New ADODB.Connection
oCn.ConnectionString = ConnString
oCn.Open

SQL = "select asdf1,asdf2,asdf5 from [Arkusz1$] where asdf1 is not null"

Set oRS = New ADODB.Recordset
oRS.Source = SQL
oRS.ActiveConnection = oCn
oRS.Open

'-------------------
'Here new connection is established with a name "Connection", I want to use a different name for it.
Set objListObject = ActiveWorkbook.Sheets(2).ListObjects.Add(SourceType:=xlSrcExternal, _
Source:=oRS, LinkSource:=True, _
TableStyleName:=xlGuess, Destination:=Sheets(2).Range("A1"))
objListObject.Refresh

If oRS.State <> adStateClosed Then
oRS.Close
End If

If Not oRS Is Nothing Then Set oRS = Nothing
If Not oCn Is Nothing Then Set oCn = Nothing

End Sub

I would like to name new ADO connection while creating it i.e. "MyConnectionName" checking earlier if such a connection exists.

Edit. I can loop through the connections:

Dim conn As WorkbookConnection
For Each conn In ActiveWorkbook.Connections
    Debug.Print conn.Name 'This name I want to set up
Next conn

I know that the connection with name "Connection" is created with this statements:

Set objListObject = ActiveWorkbook.Sheets(2).ListObjects.Add(SourceType:=xlSrcExternal, _
Source:=oRS, LinkSource:=True, _
TableStyleName:=xlGuess, Destination:=Sheets(2).Range("A1"))

objListObject.Refresh

What do you mean 'name ADO connection'? Do you mean how to name to object? — Gareth
– Gareth, Commented Nov 17, 2015 at 11:40
I'm confused - I don't see how any of the answers in the linked thread could create a data connection in your workbook. — Rory
– Rory, Commented Nov 17, 2015 at 11:59

PaulG · Accepted Answer · 2015-11-17 12:12:23Z

1

To Create a connection to a DB with ADO use something like this:

Public Sub TestQueryInvokation()

Dim oCnn As New ADODB.Connection
Dim oCmd As New ADODB.Command

oCnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\TestDB.accdb;Persist Security Info=False;"

oCmd.ActiveConnection = oCnn
oCmd.CommandText = "Query1"
Call oCmd.Execute

End Sub

This is different from the connection stuff you're looking at there. If you import data using the Excel ribbon it creates a connection and maintains a link to the DB and puts it in that collection you've shown. I've experienced problems with this approach in the past and would recommend you create your own.

You need a reference to the ADO library. Alternatively type everything as Object and you'll not need the reference.

answered Nov 17, 2015 at 12:12

PaulG

1,2078 silver badges9 bronze badges

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

9 Comments

Przemyslaw Remin Over a year ago

(1) How to assign other parameters to your style connection? They are the same? I mean the SQL string, OpenRowset etc? (2) Can you please give an example of "typing everything as Object" - this is particularly interesting.

PaulG Over a year ago

By the way, this is the same for your example of using Excel like a DB. Use an ADO class and no need to refresh any links etc.

PaulG Over a year ago

The example shows how to connect for an update type query. You've already defined the connection string in your example for use the ACE driver. So in you're code, after you open the recordset use something like CopyFromRecordSet to copy to the range and then close the recordset. Don't use what you have after this w.r.t. Connection collections.

Przemyslaw Remin Over a year ago

What about typing the Object?

PaulG Over a year ago

Public Sub TestQueryInvokation() Dim oCnn As Object Dim oCmd As Object set oCnn = CreateObject("ADODB.Connection") set oCmd = CreateObject("ADODB.Command") oCnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\TestDB.accdb;Persist Security Info=False;" oCmd.ActiveConnection = oCnn oCmd.CommandText = "Query1" Call oCmd.Execute End Sub

|

Collectives™ on Stack Overflow

How to name ADO connection while creating it in Excel VBA

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related