ADO cnxn - Provider then open OR just open cnxn_string?

Enigmachrysalis

Active Member
Joined
Apr 13, 2009
Messages
350
Both of these start the same:

Dim cnn as ADODB.Connection

Set cnn = New ADODB.Connection

but I have seen both of the following work:

Option #1

With cnn
.Provider = 'Provider string here
.Open 'Cnxn string here
End With

Option #2

cnn.Open 'Provider string & cnxn string here

The latter seems simpler, but I see the former in so many examples that I began to wonder if it is preferable for some reason. Is there any advantage to the first option?
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,795
Office Version
  1. 2019
Platform
  1. Windows
I don't think it matters. :)

That said, I prefer the "longer" version - I create all my objects at the beginning (Connection object, Recordset object). Then I open my recordset and do whatever it is I'm doing. Finally, I close my Recordset and close my Connection.

This just provides me with a consistent and familiar pattern, or basically its become a habit. If you use the "shorter" version, VBA will generally be friendly enough to work with you and clean up the objects after they've lost scope (after the routine ends). Although, with ADO it does seem to be recommended to close recordsets and connections as soon as your finished with them - so the explicit connection reference facilitates this kind of "housekeeping".

At the end of the day, I'd have to assume that if you open the recordset with a connection argument, the connection is closed with the recordset - I've seen many examples of this style also and I've never heard of problems with connections not closing. So we can assume the "shorter" version is a perfectly viable alternative and doesn't require explicit closing of the connection (though you may still want to close the recordset).
 
Last edited:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
You haven't posted all the code so it's hard to gauge if one method is better than the other.

The only thing I can think of is that the first methof would require a reference to the approriate ADO library - early binding.:)

The advantage of that would be you would have access to the object model of that library and also Intellisense.

One disadvantage would be that you might run into problems if the code was being used in different versions of whatever applications you are working with.

If you were to use late binding you might avoid that problem.:)
 

Enigmachrysalis

Active Member
Joined
Apr 13, 2009
Messages
350
I think I mislead you all unintentionally. I meant for all but the connection code to be the same, I meant for them to be identical except for the extra step to set the provider and open, instead of all at once.

Option #1

Dim cnn as ADODB.Connection
Dim strProvider as String, strFile as string
Set cnn = New ADODB.Connection

strProvider = 'Provider string
strFile = 'Connection string or file, etc.

With cnn
.Provider = strProvider
.Open strFile
End With


'More code goes here

cnn.Close
Set cnn = nothing

Option #2

Dim cnn as ADODB.Connection
Dim strProvider as String, strFile as string, strCnxn as string
Set cnn = New ADODB.Connection

strProvider = 'Provider string
strFile = 'Connection string or file, etc.
strCnxn = strProvider & strFile

cnn.Open strCnxn

'More code goes here

cnn.Close
Set cnn = nothing
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,795
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

In that case, it's purely subjective - there is no gain or advantage to be had either way. :cool:
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
What is a 'MVP approved response'?<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
You've not even posted the whole code.:unsure:<o:p></o:p>
<o:p></o:p>
I'm sorry if that sounds a bit dismissive/whatever but the only way we can help is if you give us adequate information - and you haven't.:)<o:p></o:p>
<o:p></o:p>
 

Enigmachrysalis

Active Member
Joined
Apr 13, 2009
Messages
350
Well oddly enough, an "MVP approved response" is a response approved by an MVP. So, xenou (MVP) responded with a solution ("In that case, it's purely subjective - there is no gain or advantage to be had either way.")

There is no more code to post. In fact, I wrote that extra code to clarify my point after xenou's first response. It's just an abstraction of of procedures I have seen in code examples. I was only interested in which was the optimal code to open a connection via ADO.

My last post was really just meant as a close-out post and a funny way of thanking Xenou for his help. Did it give you the impression that I was not satisfied?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,143
Messages
5,599,975
Members
414,354
Latest member
Flaxarn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top