Can DAO be set to use ANSI-92 ex- Access? RRS feed

  • Question

  • Context: I'm working on what eventually becomes an Excel Add-In. I'm using DAO to query stuff from an read-only MDB. Some of these queries involve bitwise logical operations. When these queries were developed/tested in Access, I just wrote an Access VBA function and all was well. But in the end-state, these queries get executed using DAO from Excel VBA (or maybe from VSTO), no Access present. It's not obvious how I can get DAO to use functions in its host's VBA--in this case Excel not Access. (VSTO will only makes matters worse for using user-defined functions in the queries.) The BAND and BOR operators from ANSI-92 seem like an answer. I understand an Access database, from Access, can be optioned to use the ANSI-92 -like query syntax and then BAND and BOR would be usable. But I'm not using Access in the end state, just DAO.

    Anybody know if there is a way to set a DAO instance to allow the ANSI-92 -like syntax?

    Perhaps I should just be using ADO, but DAO has always seemed a lot simpler to me...

    Monday, November 5, 2018 2:10 AM

All replies

  • Why using bit-wise operations at all in a relational database? This sounds like your making it more complex than necessary.

    What is your concrete use-case here?

    Monday, November 12, 2018 4:31 PM
  • Concrete?

    I'm looking in somebody else's MDB and they have many fields that are chock full of bitmapped state information. Why did they do this in a relational database? We'd have to ask them what their concrete use case was. Unfortunately, "they" are long gone.

    I need to select records from this MDB if certain combinations of those bits are, typically, not set. So a

    "magicnumber BAND (2^14 BOR 2^17 BOR 2^18 BOR 2^21)=0"

    expression works but a

    "magicnumber <> 2506752"

    expression does not. My present solution, barring a better answer to the question in this thread, is using expressions like

    "[magicnumber]\2^14 Mod 2+[magicnumber]\2^17 Mod 2+[magicnumber]\2^18 Mod 2+[magicnumber]\2^21 Mod 2=0"

    So, beyond pedantry about how they shouldn't have used all this btimapped stuff in the first place, is there a way to use DAO in ANSI-92 mode?

    Monday, November 12, 2018 6:45 PM
  • Well, it should be obvious, that the available syntax is a problem of the back-end, not the data transport layers. So you cannot do this in ODBC, DAO or ADO. You can change it in the database itself.

    BUT: This will change the database fundamentally. It will change what data types are available. It may change the how the results of existing queries are evaluated, thus queries can than correctly return different sets.

    Thus, when doing this, you need to test the entire database again on correct behavior (and not data-loss due to non-existing data types).

    Just set the compatibility level under Options in the backstage under Object Designers.

    CAVEAT: Make a backup before changing this setting.

    p.s. You're devolping a new VSTO plugin, but you're saying you don't care about technical debt. This is an attitude I don't understand. It's like serving a Wagyu as a Chef well-done.

    Tuesday, November 13, 2018 9:01 AM
  • As noted in the OP, Access is not involved in the solution, so playing with Access backstage options is irrelevant. The MDB in question is just a short-lived vessel for tables of data being converted from a living RDBMS database, that isn't quite close enough to Ace/Jet to use DAO/ADO/ against, and isn't supported by an ODBC driver, to a de-normalized Excel Range/ListObject that also isn't Ace/Jet. The schema in the MDB is what it is. My code calls a tool I do not control to create it. I run a handful of queries against it, at most, and then discard it. Rinse and repeat. Improving its schema in pursuit of retiring technical debt would be pointless. For a few seconds I just have to live with it the way it is.
    Tuesday, November 13, 2018 2:21 PM
  • I've looked, pursuant to this question, but I haven't found a way to specify ANSI-92 syntax using DAO.  There may well be a way, but it's well hidden.  I think you may have to use ADO instead, which (IIRC) uses ANSI-92 syntax by default.

    Dirk Goldgar, MS Access MVP
    Access tips:

    Tuesday, November 13, 2018 6:48 PM
  • Thanks. Yeah, ADO / is in the tradespace. The DAO stuff is already integrated and working, at least for the tall pole use cases, in spite of the ugly bitmap stuff. When I got the DAO stuff all working in VB, and found that the first step toward the "best practices" development paradigm was downloading and compiling an "Entity Framework Provider" from Github, DAO looked even better.
    Wednesday, November 14, 2018 5:06 AM