07 Set 2015 10:15
Microsoft is Aligning with ODBC for Native Relational Data Access
What are you doing since ADO is being depreciated?
... The other reason often mentioned in the surveys is the ease of programming with ODBC. The interfaces are simple and straight forward. With this alignment, C/C++ developers can now focus on one set of APIs for all their native client applications. This will also make porting applications to cloud more seamless.
Question2: Why is Microsoft making this change now? What are the advantages?
Answer: More customers are adopting cloud these days. To enable client applications running on various different platforms to connect to cloud, Microsoft chose ODBC as the de-facto standard client connectivity API for native client applications connecting to SQL Azure. If you are writing applications that run against standalone SQL Server and SQL Azure, or if you are planning to port your application to SQL Azure, using ODBC APIs make the transitions pretty seamless. ODBC APIs use industry standard interfaces and are very simple and straight forward to use. Using multiple result sets, memory management and specifying required/optional properties in OLE DB is more complex. Overall, customers get the benefit of easy programming and cross-platform support with this alignment and can now build applications that can be uniformly ported between various platforms.
This seems crazy! I agree that OLE DB is complicated - but then most people are using ADO.NET for simple scenarios. For performance and flexibility, OLE DB has been vastly superior to ODBC. Can we expect the same performance from the new ODBC driver implementation? Will we have full access to all features? What is the overriding motivation?
07 Out 2015 10:30
This is very specific and narrow deprecation -- there are still many products that use OLEDB exclusively (e.g. SSAS being an example).
I do think in long term, Microsoft will move away from OLEDB and therefore ADO. Furthermore, ADO has been in maintenance mode and has not had new features or major enhancements. In contrast, Microsoft has updated ODBC more than few times already. However, the enhancements introduced to ODBC isn't really visible to Access because Access need to be also updated to take advantage of the new features introduced to ODBC.
In worst case scenario, I suspect the solution to continue using ADO would be to use MSDASQL (aka OLEDB provider for ODBC) -- more layer between the application and the database but you still get the niceties of ADO that way.
07 Out 2015 11:04
Case "ADSLOCAL"
cString = "Provider=Advantage.OLEDB.1;" & _
"Mode=Share Deny None;" & _
"Show Deleted Records in DBF Tables with Advantage=False;" & _
"Data Source=" & Sistema.PathDefault & ";Advantage Server Type=ADS_Local_Server;" & _
"TableType=ADS_CDX;Security Mode=ADS_IGNORERIGHTS;" & _
"Lock Mode=Compatible;" & _
"Use NULL values in DBF Tables with Advantage=True;" & _
"Exclusive=No;Deleted=No;"
07 Out 2015 12:07
Case "MYSQL"
cString = "Driver={MySQL ODBC 3.51 Driver};" & _
"Server=server.xxx.com.br;" & _
"Option=131072;Stmt=;" & _
"Database=xxx;" & _
"User ID=xxx;" & _
"Password=xxx;"
'"Compress=true;"
Case "SIXCDX"
cString = "Provider=ApolloOLEDB7.ApolloOLEDB7;" & _
"Data Source = " & Sistema.PathDefault & "; " & _
"AccessMethod= amLocal; " & _
"TableType=ttSXFOX; " & _
"CommitLevel = clNormal;" & _
"FetchCount=1;"
Case "ACCESSLOCAL"
cString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Sistema.PathDefault & "jpa.mdb;" & _
"Persist Security Info=False"
Case "SQLSERVER"
cString = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;Persist Security Info=False;" & _
"Initial Catalog=tabela1;Data Source=SQLJPA;"