Which ODBC driver? 1

[CP60]

I have several odbc (32) drivers on my system for sql server and am confused as to which is best to use:

SQL Server Drivers which can be selected in ODBC32.exe:

1. SQL Server sqlsrv32.dll version 6.1.7601 from 2010

2. ODBC Driver 11 for Sql Server msodbcsql11.dll version 12.0.2000 from 2014

3. SQL Server Native Client 11.0 sqlncli11.dll version 11.0.2100 from 2012

I will be connecting to a local and a remote SQL Server instance with in the same network.
The SQL Server versions being used may be from 2008 R2 to 2016.

I have a legacy VB6 program and want to connect using ODBC using ADODB.

Could someone give some advice as to which I should best be using?

Thank you!

 

[Olaf Doschke]

You may keep all drivers, as they might be in use by several applications, eg the PHP extension for MSSQL uses the ODBC Driver 11, other software uses NativeClient etc.
Using VB6 ADODB means using OLEDB Providers. These are not listed in the ODBC manager at all.

Bye, Olaf.

 

[CP60]

You may keep all drivers, as they might be in use by several applications, eg the PHP extension for MSSQL uses the ODBC Driver 11, other software uses NativeClient etc.

This I realize. But the thread isn't about which driver to keep, but about which driver would be best to use.

Using VB6 ADODB means using OLEDB Providers

I can use the OLEDB provider for Sql Server, or I can use ODBC with all three drivers mentioned to establish an ADODB connection.
So, would you please clarify what you mean here?

I wish to use ODBC and not the OLEDB provider. So I am back to my original question in the OP.

 

[Olaf Doschke]

What don't you understand about "ADODB means using OLEDB Providers"?

The architecture of ADO is using OLEDB Providers. They in turn might use ODBC drivers, but that's about their internal way of working.
If you don't want to use OLEDB Providers, you don't want to use ADO.

Bye, Olaf.

Edit: There is a way through DSN using an ODBC driver, but you're then using the geneeral ODBC via OLEDB Provider.

If you want to go that route, I'd take the most modern ODBC driver. That is the Driver 11.

 

[fredericofonseca]

CP60
have a look at

http://www.connectionstrings.com/sql-server/

(and keep that link on your MUST KEEP FOREVER links)

unless there is a specific requirement to use a particular version of a driver (did happen long ago that one upgrade of a particular vendor broke lots of code due to a bug fix on the driver itself), you should always use the most recent version.

you should also use dsnless connections avoiding the need to maintain DSN entries.

Olaf, your quote "ADODB means using OLEDB Providers" - could you provide us with a link to that statement?

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[CP60]

They in turn might use ODBC drivers, but that's about their internal way of working.

I am not discussing the ADODB architecture here, but which SQL Server drivers for a DSN are best to use, and in my case to use with ADODB.
Sorry, maybe I should have originally worded it differently, or left the mention about ADO out completely as not to confuse the issue.

Thank you.



Hi fredericofonseca,

All of these seem to work:

oConn.ConnectionString = "Provider=sqloledb;Data Source=MyServer;Initial Catalog=MyDatabase;Integrated Security=SSPI;MARS_Connection=yes;"

oConn.ConnectionString = "Data Source=My SQL Server DNS"

oConn.ConnectionString = "Provider=SQLNCLI11;Data Source=MyServer;Initial Catalog=MyDatabase;Integrated Security=SSPI;MARS_Connection=yes;"

oConn.ConnectionString = "Driver={SQL Server};Server=MyServer;Database=MyDatabase;Trusted_Connection=Yes;MARS_Connection=yes;"

oConn.ConnectionString = "Driver={SQL Server Native Client 11.0};Server=MyServer;Database=MyDatabase;Trusted_Connection=Yes;MARS_Connection=yes;"

oConn.ConnectionString = "Driver={ODBC Driver 11 for SQL Server};Server=MyServer;Database=MyDatabase;Trusted_Connection=Yes;MARS_Connection=yes;"

,you should always use the most recent version.

So that would mean I would want to use the ODBC Driver 11 for Sql Server msodbcsql11.dll version 12.0.2000 from 2014.

Thank you.

 

[Olaf Doschke]

This is describing more details than I even know myself:

https://www.microsoft.com/msj/0699/oledb/oledb.aspx

Google IMAGES for VB ADO architechture -NET, and you'll find diagrams all telling ADO goes through an OLEDB layer, even if that is using ODBC for the direct data access.

Bye, Olaf.

 

[CP60]

What don't you understand about "ADODB means using OLEDB Providers"?

The architecture of ADO is using OLEDB Providers. They in turn might use ODBC drivers, but that's about their internal way of working.
If you don't want to use OLEDB Providers, you don't want to use ADO.

(emphasis mine)

Hi OlafDoschke,
ADODB does not use an OLE DB Provider to access ODBC data, as you imply above.
But it does use an OLEDB layer - as you changed to in your last post - which then branches off to either the OLE DB provider or the ODBC drivers.

 

[Olaf Doschke]

Well, give me an example of using a connection string, I'd guess checking the Connection object Provider property after making the connection you'll find "MSDASQL" used, the generic Microsoft OLEDB Provider for ODBC, even if you don't specify a provider in the connection string but use an ODBC driver connection string.

If you're using OLEDB you better use OLEDB Providers than ODBC drivers, you only add another layer of marshalling ODBC data types to the OLEDB layer, that's unnecessary.

Microsoft stopped further development of the whole OLEDB Provider branch, so in the long run I'd opt out of classic ADO overall and use direct ways to ODBC, but currently there are the Native Client OLEDB Provider variants similar to the Native Client ODBC drivers, also capable to connect and support SQL2014.

Bye, Olaf.

PS: I just did so with the conceptual connection string "Driver={ODBC Driver 11 for SQL Server};Server=MyServer;Database=MyDatabase;Trusted_Connection=Yes;MARS_Connection=yes;" taylored to my server/db, of course, and the used provider reported is indeed "MSDASQL.1".

When you already use an OLEDB Provider, you better use one, that directly connects to the database and avoid an additional layer, or you avoid the OLEDB layer overall by not using (classic) ADO.

The only advantage of MSDASQL is, you can use ODBC drivers later than the newest OLEDB providers, but that will play a role from SQL2016 onwards, not for SQL2014, the disadvantage is, you always go through MSDASQL and the ODBC driver specified and have an additonal layer you don't have using ODBC directly. I'm no Access VBA or VB6 expert, but I'd say DAO or RDO are data access variants not using an OLE layer.