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

  • Re: Difference between DAO, ADO, and ODBC


    To answer this question:


    Quote

    byVal instead of ByRef


    If we have following example:


    Code
    1. Public sub main()
    2. dbNumber as double
    3. dbNumber = 100
    4. ChangeNumber dbNumber
    5. msgbox dbNumber
    6. end sub


    ByVal is the standard option, if you don't add Byval or Byref => byval is taken


    Code
    1. public sub ChangeNumber(byVal dbTest as double)
    2. dbTest = 0
    3. end sub


    In this example the result of dbNumber = 100, the variable doesn't change.


    If we use ByRef:

    Code
    1. public sub ChangeNumber(byRef dbTest as double)
    2. dbTest = 0
    3. 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

  • 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


  • 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? :?


    :thanx:


    Rock on, guys :rock:

  • 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.

  • 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!

  • Re: Difference between DAO, ADO, and ODBC


    gweasly,


    Ok here is a sample routine that I use on a constant basis.

    • Needs a folder or path
    • Needs a filename
    • 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.

  • 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

    Quote

    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.

  • Re: Difference between DAO, ADO, and ODBC


    Quote

    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

  • Re: Difference between DAO, ADO, and ODBC


    Quote from gweasley;143774

    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!


    What I do is use ByVal as my default method because it means that I can change (either deliberately or accidentally) the contents of a variable passed to a function or subroutine WITHOUT there being a possibility that I can change the original value. Normally when you pass a variable to a routine you do NOT want that routine to be able to change (ie corrupt) the original variable.
    If you want to be able to change the original value then use a function and set the calling variable to the results of that function eg


    Function RoundWages (ByVal Amount as currency, ByVal Method as String) as Currency
    .....
    End Function


    Sub Use_it
    Dim Wages as Currency
    Wages=1567.78
    Wages = RoundWages(Wages,"Weekly")
    End Sub


    Doing it this way, it is obvious that the variable Wages, which is a parameter to the function, is also being changed by calling the function


    Sometimes you require a function which returns MORE than the one variable so then and ONLY then I use the ByRef call and I usually do it using a subroutine and not a function call and HIGHLIGHT the fact in my code to remind me that it's an exception.


    Sub RoundWages (ByRef Amount as currency, ByVal Method as String, ByRef WeeklyWages as Currency, ByRef MonthlyWages as Currency, ByRef YearlyWages as Currency)
    ' Note that RoundWages changes the values used to call it in order to return multiple values
    .....
    End Function


    Sub Use_it
    Dim Wages as Currency, WeeklyWages as Currency, MonthlyWages as Currency, YearlyWages as Currency
    Wages=1567.78
    RoundWages Wages,"Weekly", WeeklyWages, MonthlyWages, YearlyWages ' RoundWages Routine returns multiple values
    End Sub


    In my case I don't care whether one method is faster or uses less memory - I only care about my sanity one purpose of object orientation is to encapsulate the code inside a routine so that it is not affected by, nor can it affect, anything outside itself EXCEPT where it is specifically set up to occur.