1

When DAO connection is used with a connection string does that mean that it completely bypasses the ODBC connector or does it just bypass the step to retrieve the pointer(DSN) to ODBC?

If it does bypass the ODBC Connection, is it similar to OLEDB connection used by ADO?

Edit: Connection string for dsn-less looks like this:

Driver={SQL Server};Server=myServerAddress;Database=myDataBase;
Trusted_Connection=Yes;

An ADO connection using the connection string would look like this

Dim conn As New ADODB.Connection
conn.ConnectionString = "Driver={SQL Server};Server=myServerAddress;Database=myDataBase;
Trusted_Connection=Yes;"

In DAO we can utilize the connection string using:

Dim db As Database
Dim conn As String
conn = "Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Trusted_Connection=Yes;"
set db=opendatabase("",false,false,conn)
June7
  • 19,874
  • 8
  • 24
  • 34
namko
  • 627
  • 1
  • 14
  • 27
  • 1
    If the connection string specifies ODBC, it will use ODBC. Not sure what you mean by connector. It might help if you add an example to your question for clarification. Regarding ADO and ODBC, ADO can access ODBC drivers using the MSDASQL OLE DB Driver. I don't think that's really the same as what DAO does, although it is a connection string spec in both cases. – Dan Guzman Dec 13 '18 at 00:37
  • I added the code. The reason I am confused is that I thought one big advantage of using ADO was that it bypasses the ODBC driver. But, it turns out that we can use DAO with connecting string without DSN and ODBC as well. Wouldn't this make it similar to ADO? – namko Dec 13 '18 at 01:13
  • 1
    The ODBC driver is issued in both your connection string examples. The are similar in that both ADO and DAO provide the higher level objects that use the underlying native SQL Server ODBC driver and are intended for legacy applications. Note that DAO and the legacy SQL Server ODBC driver are deprecated. Furthermore, ADO is a mature technology that hasn't changed in over 15 years. – Dan Guzman Dec 13 '18 at 03:13
  • 1
    @DanGuzman: That statement was correct about 15 years ago. For at least the last decade, DAO has been and still is the default choice in Access, and ODBC drivers for _SQL Server_ are very much updated, currently at [version 17](https://www.microsoft.com/en-us/download/details.aspx?id=56567) - no _legacy_ here. – Gustav Dec 13 '18 at 08:53
  • 1
    Possible duplicate of [what is the difference between OLE DB and ODBC data sources?](https://stackoverflow.com/questions/103167/what-is-the-difference-between-ole-db-and-odbc-data-sources) – Gustav Dec 13 '18 at 08:53
  • Sorry, I thought this was not an ODBC connection as the connection says Driver={SQL Server}. So all connections to SQL server ADO or DAO happens over ODBC? – namko Dec 13 '18 at 09:48
  • 1
    @Gustav, I agree ODBC is alive and well. I called out the legacy deprecated ODBC driver named `SQL Server`. The newer separately installed SQL Server drivers like the one in your link are current technologies. Default or not, DAO and Jet are deprecated according the [official Microsoft data access roadmap](https://msdn.microsoft.com/en-us/library/ms810810.aspx). – Dan Guzman Dec 13 '18 at 11:27
  • 1
    The `Driver` keyword in ADO and DAO specifies the specific ODBC driver to use, which could be a newer driver rather than `SQL Server`. However, DAO and ODBC are not aware of the newer types introduced since SQL Server 2000. ADO can also use OLE DB instead of ODBC but one should specify [`DataTypeCompatibility=80`]{https://learn.microsoft.com/en-us/sql/connect/oledb/applications/using-ado-with-oledb-driver-for-sql-server) in the connection string because ADO isn't aware of the new SQL types. – Dan Guzman Dec 13 '18 at 11:34
  • 1
    @DanGuzman: Oh, that's bad. It's not DAO that is deprecated, but the page that is obsolete. I've left a request at MS for an update. DAO is the default in Access, and the full Office pack is available in both 32- and 64-bit versions with the 64-bit version to be recommended onwards. – Gustav Dec 13 '18 at 12:45
  • 1
    @Gustav, I think the confusion is you are referring to the ACE DAO components which are different than the legacy DAO components that ship with Windows. I agree MS needs to update the SQL Server data access roadmap to make the distinctions clearer. – Dan Guzman Dec 13 '18 at 13:23
  • 1
    Right @DanGuzman. The confusion arises from the renaming of the JET engine to ACE. JET was left at DAO V3.6, and the next version (for Access 2007) was renamed ACE. And it is ACE, not JET, that is current and does exist as 32- and 64-bit. However, in VBA it is still the DAO object to use. In any case, ADO still works fine, but since Access 2007, DAO has been the default object model in Access VBA and ACE (with the accdb file extension) the default engine. – Gustav Dec 13 '18 at 14:10

0 Answers0