Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2008
    Posts
    58

    Unanswered: Another DAO vs. ADO thread

    Hi all,

    I know this topic has been covered many times before, and I apologize for bringing it up again, but I'd like to get your input. I'm designing a project management application in Access and would like help deciding which object model to use. Here's the skinny:

    • Both models are new to me, so I'll have to learn one either way
    • The front end MDE (installed locally on the user's machine) will connect to linked tables in an MDB on a file server, so this isn't a true C/S application
    • It would be nice, if possible, to keep the connection open as opposed to creating a new one for each form/operation
    • I need be able to connect to one of a few different MDB back ends, depending on user selection, but will never mix/match connections
    • I've got 20-some tables, none of which are likely to go much beyond 1000 records and ~60% which are union or lookup tables
    • I'm designing this application with a potential future move to MS SQL server in mind (not within a year, at least)
    • I don't plan to create tables or forms at runtime
    • Number of users is probably 10-15, 90% of whom will not do much data entry/editing
    • Most users are running Access 2007, but a few are still on Access 2003
    • I plan to batch process updates
    • I'm building in a moderate amount of automation--mostly with Word and Outlook, but some Excel as well


    So there are two questions here. 1) Is it more important to pick one and stick with it, or to use use whichever is most appropriate for a given operation? 2) Which model is most appropriate? Thanks,

    J

    Edit: I should add that I haven't yet decided how to implement security. I'll be distributing at least two different versions of the app to different users (engineers don't need the accounting functionality, for example), so I may not need user-level security. However, the back end file(s) must be encrypted. It would be a nice touch to add the user's name to captions and such, but the application does not require that information.
    Last edited by JManSF; 09-19-08 at 21:16.

  2. #2
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    I wonder whether the issue is really about ADO v. DAO ? DAO is the older technology based on OLE objects and .COM. DAO is newer and based on ActiveX objects. Microsoft would love to get rid of DAO, but users and developers love it for simple applications. ADO is more powerful, but the ActiveX environment poses some problems, such as security. If your network is only a LAN, so that the links are really only to a virtual hard disk drive, DAO is probably OK. I use it this way all the time. In a WAN situation, you will probably need ADO because of the breadth of its coverage. Also, if you are going the .NET route, I suspect that in that environment, M$ offers only ADO.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think there are other issues, beyond/behind ADO/DAO, but for what its worth I don't think it matters which your use, both will continue to be supported for a while yet.

    if the application is intended to migrate to SQL Server then it shold be designed for server from the off... use unbound forms & recordsets.
    design in security from day one.. that way round you only have one front end, and you only let appropriate users see the stuff they are entitled too. that mean you only have one front end, so your design and maintenance problems are halved in that area.
    If some users are still on Access 2003, then develop your application in Access 2003.

    if you are using ole automation you could well have problem in a mixed environment.. not least that Office 2003 & Office 207 use different libraries.. and potentially different object models, so yu need to be very very carefull about what level of use you make of these.

    I'd be tempted to try to get a common platform say Office 2007.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Aug 2008
    Posts
    58
    Thanks to both of you for the good info. It looks like my next step is to sit down with the IT guy and have a chat about the network situation.

    Healdem, you raise a very good point about automation. I've asked to upgrade the last two 03 users to 07, but to no avail.

    I was going to have to use a lot of unbound controls and recordsets already, I suppose it isn't much of a stretch to go all the way to a fully unbound app. That should even improve performance (if done correctly, of course).

    I'll have to do some research on building security into my application. Do you know of any good tutorials or guides on that sort of thing? Thanks!

    J

    Edit: Found a nice security tutorial over at Blue Claw. http://www.blueclaw-db.com/microsoft...s_security.htm
    Last edited by JManSF; 09-22-08 at 17:44.

  5. #5
    Join Date
    Aug 2008
    Posts
    58
    Update: I'm going to bypass the version conflict issue by having those users install the Access 2007 Runtime version.

    Edit: This obviously won't resolve the reference conflict between office 2003 and office 2007 for the purposes of automation. Pardon the brain lapse.
    Last edited by JManSF; 09-23-08 at 13:43.

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Let us know how that turns out. I'm curious on using the 2007 runtime.

    Just to add in how I develop for possible SQL Server Upgrade is:

    1. I design all my tables in MSAccess (using dbo_XXXXX) as a naming scheme with dbo_ in front of all the tables (so once I link in the SQL Server tables, I don't have to rename the tables or worry about code not working due to a table name.)
    2. Design all the forms/logic/reports/modules, etc...in the mdb.
    3. After I finish with this (qcing all code), I then use the upsizing wizard to SQL Server which works fairly well and very quickly (ie. less than a minute or two) (but double-check relationships/primary keys/int with increment (autonumber) information on SQL Server after upsizing. It is a little buggy depending on if you have older versions of MSAccess.
    3. After upsizing, I quickly take out the dbo_ from all the names on SQL Server that it was upsized to (which is the biggest pain of this whole procedure - but relationships are still maintained - new SQL Server versions!)
    4. Create a copy of the mdb. Delete all the MSAccess tables in that mdb. And then link all the tables from SQL Server (creating a new ODBC System DSN if I need to.)
    5. Go back through the forms and work on unbound forms (I usually don't have enough time to do this in the beginning and my recordsets are fairly small (ie. 10000-20000) so I may not be able to do this even at all depending on my workload.

    I started designing in ADO about 10 years ago. I really like how the code seems to make sense to me and the way I think. Personally though, I don't really have an opinion on one or the other since almost every mdb file I've gotten into developed by another developer is usually done in DAO so I work with that or add in some ADO code (along with the reference.) I prefer ADO because The Developer's Handbook by Getz utilizes ADO a lot and has some really good ADO examples which I'll utilize.
    Last edited by pkstormy; 09-22-08 at 22:56.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Aug 2008
    Posts
    58
    Thanks Paul. I'll definitely keep that in mind if/when we make the move to SQL server. At the moment it would be overkill, but business should ramp up significantly over the next year, so it might become necessary at that point.

    ADO seems simpler to me as well. I think I'll start there.

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I personally wrote in DAO from 1991 to 1998 before I switched to ADO (in 1998) and I've been writing in ADO ever since. To me, it is simplier and easier to learn.

    Keep in mind though that most of the online posts will mostly be in DAO where as most of the books (Getz) will be in ADO. But there are some DAO specific books.
    Last edited by pkstormy; 09-24-08 at 23:42.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    re: reference conflict.
    late-bind to office and you don't need a reference: there is no reference to conflict. theoretically, late-bind can be slower than early-bind but the difference is a millisecond or few and you will not notice it.

    i use only DAO. in the past thirteen years i didn't bump into anything that DAO wouldn't do.

    izy
    currently using SS 2008R2

  10. #10
    Join Date
    Aug 2008
    Posts
    58
    Quote Originally Posted by izyrider
    re: reference conflict.
    late-bind to office and you don't need a reference: there is no reference to conflict. theoretically, late-bind can be slower than early-bind but the difference is a millisecond or few and you will not notice it.
    izy
    Good call izy! That had not occurred to me.

    Looks like I'm set to go on this project. Thanks to all for the help. I'm sure I'll be chiming back in with issues as the development moves forward.

    J

Posting Permissions

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