DAO

Anyone [still] using DAO?

I’m importing a text file into a jet database (without using MS Access). I have had a few adventures for sure but I have finally managed to get it work. I had to add a querydef to the .mdb, and then .execute it from C#. That means ditching ADO and going back to DAO, unless I’m missing something?

Its proven to be a bit of a PITA as my SQL worked fine from the QBE, but would not run from my exe. Hence adding the querydef, which has fixed it. I read somewhere that the QBE actually uses DAO rather than ADO, does anyone know if thats true of Access 2003? I also read that DAO was faster than ADO for jet databases, anyone like to confirm (or not)?

Anyone found a better way to work with text files and .mdbs (without using Access directly)?

(I’m writing a server component (in C#) so want to avoid automating Access if poss)

Cheers

Simon

Advertisements

8 Responses to “DAO”

  1. XL-Dennis Says:

    Simon,

    Since You develop in C# ADO.NET should be the the way to go. Reading textfiles is easy and using a DataSet to add/update the MDB should be possible. Check out my latest blogpost.

    It’s too common that people associate MDBs (Microsoft Databases) with Access. When it’s necessary I create smaller MDBs in Visual Studio and the good part is that MS Access cannot open them.

    The funny thing with DAO/ADO is that DAO is back when it comes to Access 2007!

    Back to the development stuff :-)

    Kind regards,
    Dennis

  2. Marcus Says:

    Hi Simon – I’ve tried posting a few times but it doesn’t appear?

  3. Simon Says:

    Marcus
    One of my comments has gone AWOL too.
    No sign of them anywhere I can see.
    I’ll copy your post in when I get proper internet access at the w/e.
    cheers
    Simon

  4. Rob Bruce Says:

    You can add a querydef to an MDB using ADOX (Microsoft ActiveX Data Objects Extensions For DDL and Security is its full name, I think) if you’d like to use a more lightweight object library.

  5. Simon Says:

    Cheers Rob
    Thats what I started with, and thats how I create my tables. Then there was something that I couldn’t do with DDL, then something that seemed to need DAO. Now I think I’m using almost every MS data access library somewhere. Of course deadlines are looming so it may end up staying that way!
    cheers
    Simon

  6. Rob Bruce Says:

    Yeah. I’ve done that before. I once had to add DAO to a project just so that I could do a Compact and Repair. Everything else was done using ADODB and ADOX. This bumped the size of the installer up from about 2mb to about 5!

  7. Simon Says:

    Marcus posted this yesterday but wordpress ate it for some reason

    First up, point from Dennis considering you’re using .Net

    “Anyone still using DAO?”

    Yes, quite regularly. Anytime I’m developing a solution which uses Access
    directly I use DAO. DAO was originally designed for Access and has a tighter
    integration with the Access object model.

    All the functionality is embedded into one library. With ADO, if you want to
    call an object like a QueryDef then you also have to reference ADOX. There are
    also some functions that don’t exist in ADO (don’t get me wrong, ADO has its
    place).

    Simon, you don’t need to instantiate Access to execute an Access query (you
    don’t even need Access installed on the PC you’re running the code from).
    However, if you want to use something like the TransferText method to import the
    text file, then you’ll need to use Automation.

    “Its proven to be a bit of a PITA as my SQL worked fine from the QBE, but would
    not run from my exe”
    Have a look at you SQL statement. If you use any native Access (i.e. not vanilla
    ANSI SQL) statements or functions, you’ll get an error executing the SQL going
    directly to JET. These include NZ and TRANSFORM. Gotta remember, Jet and Access
    are not the same thing.

    “I also read that DAO was faster than ADO for jet databases”

    I have found this for some functionality, like returning the parameters
    collection of a QueryDef. I had one solution in Melbourne query an Access
    database in Perth (not too far) in which the code seemed to hang. I found that
    the code “paused” when ADOX tried to retrieve the Catalog information of the
    remote database when trying to return the parameters collection of a query.
    Changing this code to DAO reduced execution from 20 seconds to 2.

    I don’t know whether this specific performance hit is representative across the
    board. Anyone?

    “server component… automating Access”
    Microsoft themselves warn against automating MSO apps on a server environment.
    http://support.microsoft.com/kb/257757

    Importing text files into mdbs
    http://support.microsoft.com/kb/254002

  8. Simon Says:

    Marcus
    That post went straight to the spam bin, even when I posted it, I’ve just rescued it from there.

    Maybe it wasn’t clear from my post, but I’m not using Access at all I am going straight to the mdb. Although I was tempted with all the messing around with ADO/ADOX/DAO etc.
    cheers
    Simon

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: