Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2003
    Location
    Bogota
    Posts
    67

    Unanswered: sql property: DAO vs ADO

    I'm trying to update my code, and want to move everything from DAO to ADO. I searched extensively, but couldn't find a way in ADO to set/reset the SQL of stored queries (i.e. based on user-defined selections in a form).

    Is there really not a way? I don't want to use DAO references in my projects any more. Is this some ruse by Microsoft? I can't imagine why they would discontinue such functionality.

  2. #2
    Join Date
    Jul 2003
    Posts
    48

    Re: sql property: DAO vs ADO

    What do you mean by set/reset the SQL of stored queries?

  3. #3
    Join Date
    Mar 2003
    Location
    Bogota
    Posts
    67

    Re: sql property: DAO vs ADO

    What do you mean by set/reset the SQL of stored queries?

    by way of example:

    dim sSQL as string
    dim qd as querydef

    sSQL = "<SQL>"
    set qd = currentdb.querydefs("<query_name>")
    qd.sql = sSQL

    I find this method very useful for building dynamic reporting tools (i.e. a form that the user selects criteria with, while the code under the form builds a SQL string) that return a set of records in query form, which can then be exported easily to xls or whatever. The query is always there in Access, but it's SQL is changed by the end user through the form. Microsoft is supposedly going to phase out DAO. The querydef object is DAO, and I haven't been able to find anything comparable in ADO.

  4. #4
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: sql property: DAO vs ADO

    Nope, there isn't anything like the QueryDefs collection. You will have to work with DDL statements: <ADO_Connection>.EXECUTE "DROP VIEW <YourName>"
    and

    "CREATE VIEW <YourName> AS <Your SELECT Statement>"
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  5. #5
    Join Date
    Mar 2003
    Location
    Bogota
    Posts
    67

    Re: sql property: DAO vs ADO

    I can drop the view, as you suggested, but when I run the CREATE VIEW statement I get: 'Syntax error in CREATE TABLE statement'. I tried running CREATE VIEW from the SQL view of a saved query, with the same result. It seems to expect 'TABLE' after 'CREATE', and not 'VIEW'. I searched for info on this in help and on the net - nothing. It doesn't seem logical that creating a view should be so hard.

  6. #6
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    2
    DAO is, unfortunately, not going away for data retrieval using native Access tables. If you need to use data that's actually stored in Access tables within Access (not linked tables or tables on a SQL server), you still have to use DAO.

    There are no ADO methods which will let you do anything with Access tables. (NO Access table creation here) ADO is replacing DAO for data methods where the data is not stored in Access itself. You can still use DAO (ODBC) to get this data, but DAO is no longer being updated. It's not going to improve. ADO is constantly being updated - at least until MS decides to use another db access method.

    Access reports, which is what most people use Access for, still can not fully use ADO. The report's recordsource needs a DAO recordset. The ADO method for this is hopelessly complicated. Forms have no problem with ADO.

  7. #7
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Use MDAC 2.7!

    Originally posted by jiqjaq
    There are no ADO methods which will let you do anything with Access tables. (NO Access table creation here)
    I'm not sure what do you mean. The TableDefs functionality is replaced by AdoX, and you can issue any DDL command like creating a view. I managed to replace all my DAO functionality by corresponding ADO functionality.

    pd9n: I remember I had that problem, too. Access didn't accept a CREATE VIEW, but I'm running now MDAC 2.7 SP1a, and this problem is gone.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  8. #8
    Join Date
    Mar 2003
    Location
    Bogota
    Posts
    67

    Re: Use MDAC 2.7!

    ADOX sounds like the right choice, but having to download a service pack gives me cause to hesitate. Would the CREATE VIEW statement work once the Access database is distributed to users who haven't installed the update, or would each user need the update for the code to work on their desktops? I'm imagining all the red tape (submitting request for permission to update, justifying reason for update...), so I wonder if it's worth it.

  9. #9
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Use MDAC 2.7!

    I'm working with DAO and ADO for the last 5 years, and installing a new SP never gave compatibiliteitsproblems. So, you don't have to worry about this.

    However, each PC has to get this update. And you need administrator privileges. That's the bad news. Red tapes all over your company. However, see what you gain. I advice to see it working, and I'm sure you can implement andeploy procedure.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

Posting Permissions

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