Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: Difference between DAO, ADO, and ODBC

  1. #1
    Join Date
    10th November 2004
    Posts
    51

    Difference between DAO, ADO, and ODBC

    Hi guys,

    I'm obviously just starting out with VBA too, and we were all talking about ways to connect VBA and datasets stored in MS SQL Server so that we pull data sheets automatically. I'm just confused, cos I have heard the terms ADO, DAO, and ODBC before. Can anyone explain how all these components work together / describe what they are?

    Also, how do you know when it is a necessity to pass an argument byVal instead of ByRef? I'm all lost in the semantics at this point, and just need to understand these so I know when to apply them.

    Thanks for all the help!

    -G

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    8th July 2004
    Location
    Belgium
    Posts
    737

    Re: Difference between DAO, ADO, and ODBC

    To answer this question:

    byVal instead of ByRef
    If we have following example:

    VB:
    Public Sub main() 
        dbNumber        As Double 
         
        dbNumber = 100 
        ChangeNumber dbNumber 
        msgbox dbNumber 
    End Sub 
    
    
    ByVal is the standard option, if you don't add Byval or Byref => byval is taken

    VB:
    Public Sub ChangeNumber(ByVal dbTest As Double) 
        dbTest = 0 
    End Sub 
    
    
    In this example the result of dbNumber = 100, the variable doesn't change.

    If we use ByRef:
    VB:
    Public Sub ChangeNumber(ByRef dbTest As Double) 
        dbTest = 0 
    End Sub 
    
    
    the result is 0. Means that the variable will change when it's taken to another sub or function.

    Hope this is clear.

    Gollem

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,678

    Re: Difference between DAO, ADO, and ODBC

    Rather than rehash it all myself, I will quote from an article by a guy called Mahesh Chand, which pretty much covers what you have asked

    What are the Microsoft Data Access Components?

    The Microsoft Data Access Components (MDAC) are the key technologies that enable Universal Data Access. Data-driven client/server applications deployed over the Web or a LAN can use these components to easily integrate information from a variety of sources, both relational (SQL) and non relational. These components include Microsoft ActiveX Data Objects (ADO), OLE DB, and Open Database Connectivity (ODBC).

    ActiveX Data Objects (ADO)

    ADO is the strategic application programming interface (API) to data and information. ADO provides consistent, high-performance access to data and supports a variety of development needs, including the creation of front-end database clients and middle-tier business objects that use applications, tools, languages, or Internet browsers. ADO is designed to be the one data interface needed for single and multi-tier client/server and Web-based data-driven solution development. The primary benefits of ADO are ease of use, high speed, low memory overhead, and a small disk footprint.

    ADO provides an easy-to-use interface to OLE DB, which provides the underlying access to data. ADO is implemented minimal network traffic in key scenarios, and a minimal number of layers between the front end and data source-all to provide a lightweight, high-performance interface. ADO is easy to use because it uses a familiar metaphor-the COM automation interface, available from all leading Rapid Application Development (RAD) tools, database tools, and languages on the market today. ADO is a nice wrapper for OLD-DB.

    OLE DB

    OLE-DB is the Microsoft strategic system-level programming interface to data across the organization. OLE DB is an open specification designed to build on the success of ODBC by providing an open standard for accessing all kinds of data. Whereas ODBC was created to access relational databases, OLE DB is designed for relational and non relational information sources, including mainframe ISAM/VSAM and hierarchical databases; e-mail and file system stores; text, graphical, and geographical data; custom business objects; and more.

    OLE DB defines a collection of COM interfaces that encapsulate various database management system services. These interfaces enable the creation of software components that implement such services. OLE DB components consist of data providers, which contain and expose data; data consumers, which use data; and service components, which process and transport data (such as query processors and cursor engines). OLE DB interfaces are designed to help components integrate smoothly so that OLE DB component vendors can bring high-quality OLE DB components to market quickly. In addition, OLE DB includes a bridge to ODBC to enable continued support for the broad range of ODBC relational database drivers available today.

    Open Database Connectivity (ODBC)

    The ODBC interface is an industry standard and a component of Microsoft Windows Open Services Architecture (WOSA). The ODBC interface makes it possible for applications to access data from a variety of database management systems (DBMSs). ODBC permits maximum interoperability-an application can access data in diverse DBMSs through a single interface. Furthermore, that application will be independent of any DBMS from which it accesses data. Users of the application can add software components called drivers, which create an interface between an application and a specific DBMS.



    When should you use OLE-DB, ADO, DAO, or ODBC ?


    ADO is a wrapper around OLE-DB so you can use ADO or OLE-DB.

    Non-OLE environment : If a database supports ODBC and and that database is on a server that don't support OLE then ODBC is your best choice.

    Non-SQL environment : ODBC is designed to work with SQL. If you have non-SQL environment then OLE-DB is better choice.

    OLE environment : If you already have ODBC drives then you can use ODBC, otherwise use OLE-DB.

    Interoperability required : If you need interoperable database components, then OLE-DB is your best choice.

    16-Bit data access support : ADO don't support 16 bit so ODBC is the only choice.

    Using multiple databases - If you are using databases that support Microsoft's jet engine then definite choice is ADO or DAO. By using ADO you get workspace level support for transaction. That means you can connect more than one database at a time in an application, which is impossible by using ODBC. You can only connect one database at a time by using ODBC.



    ADO vs. DAO

    ADO is a superset of DAO in functionality point of view. In fact ADO is a combination of DAO + RDO. I would prefer ADO in these cases. 1. If you are proficient in COM programming. 2. If your server supports OLE environment. 3. If you want workspace-level and multi database type ( Relational, indexed, ISAM type, text files supports ). DAO is best choice when you have DAO components installed on your machine and you are using Microsoft's jet database engine based databases such as MS-Access, SQL Server, MS-Excel or Paradox. This is fastest and easiest mathod o access databases.

    DAO vs. ODBC

    DAO is best choice when you have DAO components installed on your machine and you are using Microsoft's jet database engine based databases such as MS-Access, SQL Server, MS-Excel or Paradox. This is fastest and easiest method to access databases. ODBC is for various type of database which provides ODBC drives such as SQL Server, Oracle, MS-Access. Additional advantage is DAO is workspace-level support.

    Advantages of DAO: Easy to use. Workspace level support. Both MFC and API provides DAO support. Speed is relatively faster than ODBC for jet database engine databases.

    Advantages of ODBC : Easy to use specially when developer are not familiar with COM environment. SQL support. Both API and MFC support. Good for relational databases only.

    Advantages of ADO or OLE-DB : Workspace-level support. Fastest method to access various kind of databases. Easy to use fi you are familiar with COM environment. Provides access to relational, non relational and other types of data.
    Kind Regards, Will Riley

    Web Presence:
    LinkedIn: Will Riley

  4. #4
    Join Date
    10th November 2004
    Posts
    51

    Re: Difference between DAO, ADO, and ODBC

    WillR: Thank you very much for the exhaustive description, it really helped!

    Gollem: Thank you for the example... I still don't get though at what instance or when (and how) would you exactly use byVal as opposed to byRef? I know that byVal makes a copy of the variable instead of pointing directly to the value of the variable (byRef), but when and how would you use it?



    Rock on, guys

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    22nd January 2004
    Location
    Colorado Springs
    Posts
    3,992

    Re: Difference between DAO, ADO, and ODBC

    Passing by value:

    When you pass by value the function requests from memory space to store that value. So in effect when you pass by value there are 2 memory locations with the exact same value. You can use pass by value if you want to ensure that the function doesn't change the original incomming variable/value......
    Within the function ( as long as the value wasn't set to be a const ) that value can change. However, the original value doesn't change. The function must include the ByVal keyword when declaring the variable.


    Passing by reference:
    This is typically how values are passed ( at least in VB ) other languages have other requirements. In passing by reference essentially what is passed is the address to the existing memery spot. Hence, if you change value in the function the original value is changed. This is how you can send a value to a routine and have it return multiple values.
    Passing by reference is cheaper on overhead ( read memory ) as a second memory spot doesn't have to be retrieved. Also when the routine ends it doesn't have to be reclaimed.
    Regards,
    Barry

    My Favorite New Thing:
    Dynamic Named Ranges



    The alternative for
    "Press Any Key To Continue."

    and we all have one we'd like to use it on


    1. Cross Posting Etiquette
    2. Are You Here To Learn: What Have You Tried?
    3. Your Best Resource to begin learning VBA: RECORD A MACRO . . . Then Study It!

  6. #6
    Join Date
    10th November 2004
    Posts
    51

    Re: Difference between DAO, ADO, and ODBC

    Hi Barry,

    Thanks for the info! Yes, I understand the concept behind it and how it works on the back end, but I suppose what I need to see is an actual example of why you would use one versus the other. Gollem tried to explain it but I think I need a more specific and detailed VBA example on how and when you would use one against the other, because I really am having a difficult time understanding this

    Thanks!

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    22nd January 2004
    Location
    Colorado Springs
    Posts
    3,992

    Re: Difference between DAO, ADO, and ODBC

    gweasly,

    Ok here is a sample routine that I use on a constant basis.
    VB:
    Sub FileOperations_RetrieveAllAvailFileNames(strPath As String, strFileName As String, strRetValue As String) 
         
         '   Initialize all global variables
        If Not gblnGlobalVariablesInit Then Initialize_Global_Variables 
         
         '   Local Variables
        Dim objFileSys      As Object ' The Scripting.FileSystemObject ( main object )
        Dim objFolder       As Object ' The Scripting.FileSystemObject ( selected Folder )
        Dim objFileCol      As Object ' The Scripting.FileSystemObject ( File List in Folder )
        Dim objTheFile      As Object ' The Scripting.FileSystemObject ( single file in list )
        Dim strEtsrFolder   As String 
         
         '   Step 1 : Locate / Save string of indicated file names
        Set objFileSys = CreateObject("Scripting.FileSystemObject") 
        Set objFolder = objFileSys.GetFolder(strPath) 
        Set objFileCol = objFolder.Files 
        For Each objTheFile In objFileCol 
            If InStr(UCase(objTheFile.Name), UCase(strFileName)) > 0 Then 
                strRetValue = strRetValue & "," & UCase(objTheFile.Name) & "," 
                strRetValue = Replace(strRetValue, ",,", ",") 
            End If 
        Next 
         
    End Sub 
    
    
    1. Needs a folder or path
    2. Needs a filename
    3. A variable to return data


    Now because all variables DONT'T have the BYVAL prefix they are passed by reference. Which means the calling routine must declare the variables. Inside the routine it does it's thing and saves the data in the return variable ( yes I'm only returnning 1 thing and it could have been a function ). So when I assign the value in the routine and the routine ends the value is still available to the calling routine as it declared the variable in the first place.


    Essentially, you would use pass by reference if you need to return multiple values from a routine. As you know a function can return only 1 value per se. However, if you declare a function and it's arguments are passed by reference you can return more than one value.
    Regards,
    Barry

    My Favorite New Thing:
    Dynamic Named Ranges



    The alternative for
    "Press Any Key To Continue."

    and we all have one we'd like to use it on


    1. Cross Posting Etiquette
    2. Are You Here To Learn: What Have You Tried?
    3. Your Best Resource to begin learning VBA: RECORD A MACRO . . . Then Study It!

  8. #8
    Join Date
    22nd January 2004
    Location
    Colorado Springs
    Posts
    3,992

    Re: Difference between DAO, ADO, and ODBC

    Gollem,

    You need to be careful. Your post said that if ByVal or ByRef are omitted then the default is ByVal. That is erroneous ( at least for VBA ) and with the little that I've codded for VB I believe it also to be wrong.

    This is the quote from the VBA Help
    ByVal Optional. Indicates that the argument is passed by value.
    ByRef Indicates that the argument is passed by reference. ByRef is the default in Visual Basic.
    Regards,
    Barry

    My Favorite New Thing:
    Dynamic Named Ranges



    The alternative for
    "Press Any Key To Continue."

    and we all have one we'd like to use it on


    1. Cross Posting Etiquette
    2. Are You Here To Learn: What Have You Tried?
    3. Your Best Resource to begin learning VBA: RECORD A MACRO . . . Then Study It!

  9. #9
    Join Date
    8th July 2004
    Location
    Belgium
    Posts
    737

    Re: Difference between DAO, ADO, and ODBC

    You need to be careful. Your post said that if ByVal or ByRef are omitted then the default is ByVal. That is erroneous ( at least for VBA ) and with the little that I've codded for VB I believe it also to be wrong.
    Sorry, I switched the two. My mistake.

    Gollem

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Use Odbc With Hyperion
    By lineardialogue in forum Excel and/or Access Help
    Replies: 4
    Last Post: July 24th, 2007, 03:13
  2. Ado Versus Odbc Sql Syntax
    By pete_bristol in forum Excel and/or SQL Help
    Replies: 4
    Last Post: April 4th, 2007, 16:56
  3. Odbc
    By Raf in forum Excel and/or Access Help
    Replies: 6
    Last Post: November 30th, 2005, 01:34
  4. ODBC vs ADO
    By iwrk4dedpr in forum Excel and/or Access Help
    Replies: 1
    Last Post: August 19th, 2005, 16:42
  5. VBA : ODBC
    By abe6162 in forum EXCEL HELP
    Replies: 11
    Last Post: March 10th, 2004, 22:35

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno