Results 1 to 23 of 23

Thread: Really confuse....provider connection.

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Mar 2005
    Posts
    2,580

    Really confuse....provider connection.

    I work with VB 6 and access database.
    But wath Is the best provider to open a decent connection for Access db?
    Ole
    Dao
    Ado
    Jet
    ...
    Tks

    Note:
    The Access db Is saved on local HD

  2. #2
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Really confuse....provider connection.

    With Access either will work fine. If that is the only type of database you plan to work with then use which ever you know best. If you also want to be able to use SQL Server or other database then I would say don;t bother with DAO use ADO.

    btw OLE and Jet are not alternatives to DAO and ADO.

    Here is part of an ADO connection string used for MSAccess database PROVIDER=Microsoft.Jet.OLEDB.4.0 Notice that this is an ADO connection and notice that both Jet and OLE are on that line. There are other options with ADO of course, you can use one of the OLEDB providers such as Jet above, or for SQL Server, you can use native providers or ODBC providers depending on which DB you are using and what requirements you have.

    Also note that with Access 2007 and later you have the ACEDB provider.

    Personally I have not written anything that used DAO in many years so I would without fail use ADO.

  3. #3
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: Really confuse....provider connection.

    Name:  WDAC.png
Views: 612
Size:  31.1 KB

    Current MDAC/WDAC architecture

    The items marked in red are not really viable from VB6 programs. You can make use of them with significant effort but there is seldom any reason to try to do so.

    So where is DAO/RDO? Nowhere!

    Those are deprecated legacy technologies left over from 16-bit VB and Jet.

    MSDASQL is an OLE DB Provider designed as an adapter shim to help programs make use of data sources via antique ODBC Drivers.

  4. #4
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: Really confuse....provider connection.

    Quote Originally Posted by luca90 View Post
    But wath Is the best provider to open a decent connection for Access db?
    Just use ADO with Microsoft.Jet.OLEDB.4.0 provider.

    cheers,
    </wqw>

  5. #5
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,995

    Re: Really confuse....provider connection.

    DAO is as much deprecated as VB6 is.
    But for Access and local databases it is the best. In regard to speed, clarity of code, beight straightforward, intuitivity, ability to edit the DB structure, Etc.
    It compares with ADO very much the same as VB6 does with .Net.

    But, like I would not recommend to someone new to learn VB6, I woudn't recomend to learn DAO either, more or less for the same reasons.

  6. #6
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: Really confuse....provider connection.

    SQL Enhancements

    In Access 2000, many enhancements were made to the SQL implementation in the Microsoft Jet 4.0 data engine in order to support new features of Access, to conform more closely to the ANSI-92 standard, and to allow for greater compatibility between Access and Microsoft® SQL Server™. The Jet database engine now has two modes of SQL syntax: one that supports previously used SQL syntax, and one that supports the new SQL syntax. It is very important to note that some of the new SQL syntax is available in code only when you use ActiveX® Data Objects (ADO) and the Jet OLE DB provider, and is not currently available through the Access SQL View user interface or DAO. This article points out when a certain SQL command is available only through the Jet OLE DB provider and ADO.
    Note The DEFAULT statement can be executed only through the Jet OLE DB provider and ADO. It will return an error message if used through the Access SQL View user interface.
    Note The check constraint statement can only be executed through the Jet OLE DB provider and ADO; it will return an error message if used though the Access SQL View user interface. Also note that to drop a check constraint, you must issue the DROP CONSTRAINT statement through the Jet OLE DB provider and ADO. Also, if you do define a check constraint: (1) it won't show as a validation rule in the Access user interface (UI), (2) you can't define the ValidationText property so that a generic error message will display in the Access UI, and (3) you won't be able to delete the table through the Access UI or from code until you drop the constraint by using a DROP CONSTRAINT statement from ADO.
    Note The fast foreign key statement can only be executed through the Jet OLE DB provider and ADO. It will return an error message if used through the Access SQL View user interface. Also note that to drop a fast foreign key, you must issue the DROP CONSTRAINT statement through the Jet OLE DB provider and ADO.
    Note The data types listed in the previous SQL statement can be executed only through the Jet OLE DB provider and ADO. They will result in an error message if used through the Access SQL View user interface. Also note that if you create a field with the TEXT data type through the Jet OLE DB provider and ADO, it will result in a data type of MEMO when you view the table design through the Access user interface.
    Note The WITH COMPRESSION and WITH COMP keywords listed in the previous SQL statements can be executed only through the Jet OLE DB provider and ADO. They will result in an error message if used through the Access SQL View user interface.
    Note The CREATE VIEW and DROP VIEW statements can be executed only through the Jet OLE DB provider and ADO. They will return an error message if used through the Access SQL View user interface or DAO. Also note that views created with the CREATE VIEW statement are saved in the database, but are not exposed as saved queries in the Access user interface. You can work with them only in ADO and ADOX programming code.
    Note The CREATE PROCEDURE and DROP PROCEDURE statements can be executed only through the Jet OLE DB provider and ADO. They will return an error message if used through the Access SQL View user interface or DAO. Also note that procedures created with the CREATE PROCEDURE statement are saved in the database, but are not exposed as saved queries in the Access user interface. You can work with them only in ADO and ADOX programming code.
    Note The EXECUTE statement can be executed only through the Jet OLE DB provider and ADO. It will return an error message if used through the Access SQL View user interface or DAO.

    And the list goes on and on. There is no reason to hamstring yourself using legacy DAO.

  7. #7
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,995

    Re: Really confuse....provider connection.

    Quote Originally Posted by dilettante View Post
    And the list goes on and on. There is no reason to hamstring yourself using legacy DAO.
    Quote Originally Posted by dilettante View Post
    And the list goes on and on. There is no reason to hamstring yourself using legacy VB6.
    Did you know that only with DAO you have full access to the structure of the Access database and with the ability to make changes? With ADO you need very cumbersome SQL statements to do some of these things that are easy and clear with DAO.
    The only feature that I even needed from ADO are the disconected recordsets.
    Also, perhaps you don't know, but Access now uses DAO, not ADO in its technology (ACE).
    Did I say that DAO is faster? But I'm sure you don't care about that (I do).

  8. #8
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Really confuse....provider connection.

    Quote Originally Posted by Eduardo- View Post
    DAO is as much deprecated as VB6 is.
    Actually no it is not. DAO was deprecated when VB6 was brand new. ADO was introduced in VB6.

    It compares with ADO very much the same as VB6 does with .Net.
    That would be ADO.Net not ADO

    Once upon a time I used DAO a lot but then ADO was available and I needed to work with SQL Server as well as Access so it was a no brainer to switch. As for speed I think it depends on what you are doing which provides better speed.

    As for the working with the db structure you would use the ADOX features which not only allow you to modify an Access db but also sql server.

    IMO the ADO code is more straightforward that DAO code and in a few ways ADO.Net more so.

  9. #9
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Really confuse....provider connection.

    Quote Originally Posted by Eduardo- View Post
    Did you know that only with DAO you have full access to the structure of the Access database and with the ability to make changes?
    With ADO you need very cumbersome SQL statements...
    Well - there's the ADOX-reference one can check-in, which allows nearly the same comfort -
    and there's nothing wrong or cumbersome with DDL-statements (IMO) - which (after becoming "fluent" with DDL)
    might help with configuring- or enhancing DB-schema-structures, when you switch to other DBEngines...

    Quote Originally Posted by Eduardo- View Post
    Also, perhaps you don't know, but Access now uses DAO, not ADO in its technology (ACE).
    That's relevant info for the Office-SubForums IMO...
    The VB6-Users who frequent this forum here, will fare better with either "plain JET 4.0" (instead of ACE) -
    or alternatively SQLite, in case a modern and fast Desktop-DBEngine is to be used within ones VB6-App.

    Quote Originally Posted by Eduardo- View Post
    Did I say that DAO is faster? But I'm sure you don't care about that (I do).
    That remains to be seen (I've already asked you about specific code, which proves that, in another thread...)
    You wrote about "your own tests, done years ago" - but if we can't see the code you've used in those tests,
    we simply don't know, whether you did something wrong (setting-wise) whilst using the ADO-part in said comparisons...

    Olaf

  10. #10
    PowerPoster
    Join Date
    Jun 2015
    Posts
    2,224

    Re: Really confuse....provider connection.

    Although DAO has been updated with the newer access runtimes,
    If you ever want to move to SQL Server - ADO is your best bet.

  11. #11
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,995

    Re: Really confuse....provider connection.

    Quote Originally Posted by DataMiser View Post
    Actually no it is not. DAO was deprecated when VB6 was brand new. ADO was introduced in VB6.


    That would be ADO.Net not ADO
    My point is that both are deprecated. Is there any scale on deprecation?
    "As VB5 is" sounds good?

    Quote Originally Posted by DataMiser View Post
    ...and I needed to work with SQL Server as well as Access so it was a no brainer to switch.
    That may be the main advantage over DAO, the ability to migrate to another (and more robust) database environment painlessly.

    Quote Originally Posted by DataMiser View Post
    As for the working with the db structure you would use the ADOX features which not only allow you to modify an Access db but also sql server.
    And that is very cumbersome compared to DAO.

    Quote Originally Posted by DataMiser View Post
    IMO the ADO code is more straightforward that DAO code
    I don't think so, IMO.

    Quote Originally Posted by DataMiser View Post
    and in a few ways ADO.Net more so.
    I have no idea about ADO.Net so I can't say.

  12. #12
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Really confuse....provider connection.

    Quote Originally Posted by Eduardo- View Post
    And that is very cumbersome compared to DAO.
    Not in my opinion. DAO can be pretty cumbersome as well, in some cases more so than ADO or ADO.net


    I have no idea about ADO.Net so I can't say.
    There have been quite a few changes the one thing that I like most is the way they made it more simple to use parameterized queries.

    Of course what is easy vs hard depends largely on what you are used to using. For someone that uses DAO most of the time ADO or ADO.Net would be harder to work with. For those of us that have been using ADO for 15 years or more are more likely to struggle writing DAO code.

  13. #13
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,995

    Re: Really confuse....provider connection.

    Quote Originally Posted by Schmidt View Post
    Well - there's the ADOX-reference one can check-in, which allows nearly the same comfort -
    and there's nothing wrong or cumbersome with DDL-statements (IMO) - which (after becoming "fluent" with DDL)
    might help with configuring- or enhancing DB-schema-structures, when you switch to other DBEngines...
    Not even close to the comfort of DAO for doing the same.

    Quote Originally Posted by Schmidt View Post
    That's relevant info for the Office-SubForums IMO...
    The VB6-Users who frequent this forum here, will fare better with either "plain JET 4.0" (instead of ACE) -
    or alternatively SQLite, in case a modern and fast Desktop-DBEngine is to be used within ones VB6-App.
    My point is that the technology is good, and for Access it is better (at least that can be infered from them choosing it for the product)

    Quote Originally Posted by Schmidt View Post
    That remains to be seen (I've already asked you about specific code, which proves that, in another thread...)
    You wrote about "your own tests, done years ago" - but if we can't see the code you've used in those tests,
    we simply don't know, whether you did something wrong (setting-wise) whilst using the ADO-part in said comparisons...

    Olaf
    From my part, right now, I think it will remain to be seen still.
    It wouln't be difficult to make a couple of simple tests, but even if it would be interesting, I have other more immediately needed things to do now.

  14. #14
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: Really confuse....provider connection.

    Well, maybe I should stay out of this one, but hey ho.

    I've used both (DAO & ADO), but I started my primary project that I still maintain (started in 1999) with using the DAO. And personally, I find the DAO an extremely robust, fast, and straightforward technology. And the DAO v3.6 is pre-installed on all versions of Windows since XP.

    There's no SQL commands in the DAO (without jumping through some hoops), but that actually keeps it simple, rather than complicating things.

    Now, if you want to use SQL command and/or you want to possibly think about upgrading to a SQL Server someday, the ADO is clearly the way to go. However, if you know you'll always be using an MS-Access database, and you care nothing about using SQL commands, the DAO is a rock-solid technology. I only know of one bug and one "be careful" area of the DAO: 1) the .RecordCount property doesn't always work perfectly (but it does always work as a boolean test, just not the correct number of records), and 2) you need to be sure and turn off record caching if you intend to use it in a multi-user environment. And, I don't know if the ADO has either/both of those problems.

    And, I certainly know that the DAO has total control over the structure of an MS-Access database. I haven't fully explored those aspects of the ADO, so I can't speak to that.

    So luca90, if you know what your requirements are (including any future scaling requirements), it just becomes an educated choice or possibly just dealer's choice.

    Good Luck,
    Elroy
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  15. #15
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,995

    Re: Really confuse....provider connection.

    Quote Originally Posted by DataMiser View Post
    Not in my opinion. DAO can be pretty cumbersome as well, in some cases more so than ADO or ADO.net
    I used the word "cumbersome" when talking about handling the database structure. Not for data handling.

    For normal use (data handling) I just said that DAO code was more straightforward IMO.

    Quote Originally Posted by DataMiser View Post
    Of course what is easy vs hard depends largely on what you are used to using. For someone that uses DAO most of the time ADO or ADO.Net would be harder to work with. For those of us that have been using ADO for 15 years or more are more likely to struggle writing DAO code.
    Sure.
    I guess the same would happen for someone that learned VB.Net, it will be easier than VB6 for her/him.

    Quote Originally Posted by DataMiser View Post
    There have been quite a few changes the one thing that I like most is the way they made it more simple to use parameterized queries.
    One thing that i tried to do a couple of weeks ago and found that was impossible, was something like:

    Code:
    MySQLString = "SELECT Field1, CallExternalFunction(MyFunctionName(myTable.Field2, myTable.Field3)) As MyCustomField, Field4, Field5 FROM MyTable..."
    
    Private Funtion MyFunctionName(Param1 as Long, Param2 as Long) As String
        If...
    
    
        MyFunctionName =
    End Function
    I discovered that there is no way to do that, at least with DAO. Is it possible with ADOX?

  16. #16
    PowerPoster
    Join Date
    Jun 2015
    Posts
    2,224

    Re: Really confuse....provider connection.

    UDF's and even some built-in functions are not usable through ADO.

  17. #17
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,995

    Re: Really confuse....provider connection.

    Quote Originally Posted by DEXWERX View Post
    UDF's and even some built-in functions are not usable through ADO.
    I was trying to use a flexgrid with data binding to show the data.
    Then I needed to define what to show in the columns with the SQL statement.
    But if I can't call custom functions it is very limited what I can do.

    At the end I solved it by creating an ADO recordset disconected and using it as an intermediate step.

    It is very limited what can be done with data binding.

  18. #18
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: Really confuse....provider connection.

    And hey, just another thought. When declaring your variables for use with the DAO and/or ADO, it's a good idea to use the library names as prefixes. For instance, when declaring a recordset, say "Dim rs As DAO.RecordSet" rather than just "Dim rs As RecordSet".

    If you make a habit of doing that, you can actually mix the DAO and ADO in the same project. However, if you don't, you'll have problems, as the DAO and ADO have many many of the same class names.

    Good Luck,
    Elroy
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  19. #19
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Really confuse....provider connection.

    Quote Originally Posted by Eduardo- View Post
    My point is that the technology is good, and for Access it is better (at least that can be infered from them choosing it for the product)
    Perhaps yes perhaps no. Perhaps they used it because it was easier to build on the DAO that was already being used in older versions rather than rewrite everything including many of the VBA functions to work with the preferred ADO or perhaps they were just lazy.

  20. #20
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,995

    Re: Really confuse....provider connection.

    Quote Originally Posted by Elroy View Post
    And hey, just another thought. When declaring your variables for use with the DAO and/or ADO, it's a good idea to use the library names as prefixes. For instance, when declaring a recordset, say "Dim rs As DAO.RecordSet" rather than just "Dim rs As RecordSet".

    If you make a habit of doing that, you can actually mix the DAO and ADO in the same project. However, if you don't, you'll have problems, as the DAO and ADO have many many of the same class names.

    Good Luck,
    Elroy
    The only times I used ADO I did late bound, with CreateObject so I didn't need to add the reference.
    But it is a good advice, though.

    I hate having the reference to ADO because I'm used to type Dim MyStringVariable as str and to hit the ENTER key.
    And when I have the reference to ADO I get Stream
    Believe me or not that's another reason why I don't like ADO.

  21. #21
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Really confuse....provider connection.

    Quote Originally Posted by Eduardo- View Post
    One thing that i tried to do a couple of weeks ago and found that was impossible, was something like:

    Code:
    MySQLString = "SELECT Field1, MyFunctionName(myTable.Field2, myTable.Field3) As MyCustomField, Field4, Field5 FROM MyTable..."
    
    Private Funtion MyFunctionName(Param1 as Long, Param2 as Long) As String
        If...
    
    
        MyFunctionName =
    End Function
    I discovered that there is no way to do that, at least with DAO. Is it possible with ADOX?
    No, neither ADO(X) nor DAO support UDFs with JET ... but SQLite (as the more modern engine) does of course...

    Your own VB6-based Function-Implementation has to go into a little Class (e.g. named cMyAddFunc)
    Code:
    Option Explicit
    
    Implements IFunction '<- the class needs to implement a little callback-interface
     
    Private Property Get iFunction_DefinedNames() As String
      iFunction_DefinedNames = "AddFunc"
    End Property
    
    Private Sub iFunction_Callback(ByVal ZeroBasedNameIndex As Long, ByVal ParamCount As Long, UDF As cUDFMethods)
      If ParamCount = 2 Then
        UDF.SetResultInt64 UDF.GetInt64(1) + UDF.GetInt64(2) 'parameter-indices are one-based
      Else 'an example, how to raise an error, if something cannot be handled by our function
        UDF.SetResultError "AddFunc: we need two parameters!"
      End If
    End Sub
    Form-TestCode would then look like this:
    Code:
    Option Explicit
    
    Private Cnn As cConnection
    
    Private Sub Form_Load()
      Set Cnn = New_c.Connection(, DBCreateInMemory)
          Cnn.AddUserDefinedFunction New cMyAddFunc 'make your own Function known to the DB-Engine 
    End Sub
    
    Private Sub Form_Click()
      Caption = Cnn.OpenRecordset("Select AddFunc(1, 2)").Fields(0).Value
    End Sub
    Olaf

  22. #22

  23. #23
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,083

    Re: Really confuse....provider connection.

    Quote Originally Posted by Elroy View Post
    And hey, just another thought. When declaring your variables for use with the DAO and/or ADO, it's a good idea to use the library names as prefixes. For instance, when declaring a recordset, say "Dim rs As DAO.RecordSet" rather than just "Dim rs As RecordSet".

    If you make a habit of doing that, you can actually mix the DAO and ADO in the same project. However, if you don't, you'll have problems, as the DAO and ADO have many many of the same class names.

    Good Luck,
    Elroy

    Yes, I declare my variables like this:

    daoRs As DAO.RecordSet

    adoRs As ADODB.RecordSet

    rdoRs As RDO.rdoResultset

    dhRs As dhRichClient3.cRecordSet

    sqRs As vbRichClient5.cRecordSet
    Last edited by dreammanor; Dec 20th, 2018 at 08:40 PM.

Posting Permissions

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



Click Here to Expand Forum to Full Width