Results 1 to 14 of 14
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access Data Project (A2002)

    Hello All,
    I am getting ready to convert our back end data to SQL server (a project I have done before). I would also like to convert the existing FE to ADP. Most of everything written in the FE is done in DAO. I have done some reading about ADO and have the general idea that it should work better. Does anyone have any comments on this?

    Thanks,
    Mark

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access Data Project (A2002)

    Allow plenty of time for a conversion from DAO to ADO because they are just similar enough to lead you down the primrose path thinking you are doing it correctly in ADO only to find that it doesn't give you an error but it also doesn't work! Get yourself some books on ADO and get the basics firmly in mind before you start. In some cases, ADO is easier and faster, and in others it's longer and slower. You'll have to find out which you're dealing with as you go along. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> I like ADO but I still get tangled in it occasionally.
    Charlotte

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Data Project (A2002)

    Cool!,
    Thanks. I have a book but have not had the time to read up until now. I will start.

    You said that it is faster, does that mean faster to code, or faster to run?

    Thanks,
    Mark

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access Data Project (A2002)

    Faster to run. It usually is NOT faster to code. <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>
    Charlotte

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,613
    Thanks
    3
    Thanked 58 Times in 58 Posts

    Re: Access Data Project (A2002)

    I'm not sure what you expect to gain by going to an ADP front-end - the concensus among most developers is that unless you have a compelling reason to use an ADP such as a complex performance issue, an MDB front-end linked via ODBC to SQL Server tables, or using ADO for programatic manipulation of tables, is a much easier development path. Also note that the party line from Microsoft these days is that no further enhancements are planned for ADPs. An important note here is that you can use ADO independent of ADPs - it's a common misconception that you cannot.
    Wendell

  6. #6
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Data Project (A2002)

    Thanks Wendell,
    I did this on a similar sized DB a few years ago. After changing over to SQL for the back end, the performance of the database decreased considerably. Does ADO relieve you from having to use ODBC?

    Thanks,
    Mark

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,613
    Thanks
    3
    Thanked 58 Times in 58 Posts

    Re: Access Data Project (A2002)

    Yes, ADO does relieve you from using ODBC, but you won't see any significant performance changes. If your move from an Access back-end to a SQL Server back-end caused the performance to decrease significantly, chances are very good that you have an issue with the SQL Server performance. Unfortunately, performance tuning a SQL Server database is not a trivial task - the biggest issue is to make sure you have indexes on fields that you are querying by or joining on - but there are many other issues that need to be looked at. It seems like a good topic for one of our website tutorials if I ever get the time to do another one. The bottom line in my experience is that with proper tuning on anything other than trivial and small Access databases, a SQL Server back-end will always out-perform an Access one running on the same server.
    Wendell

  8. #8
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Data Project (A2002)

    Hi Wendell,
    I was talking about another DB I was developing at another company a few years back. Would you suggest that I just stick with the MDB file and get a SQL server running the BE for the project I am working on now?

    On the previous project I probably had some performance issues due to not having many queries converted to views and stored procedures.

    Thanks,
    Mark

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,613
    Thanks
    3
    Thanked 58 Times in 58 Posts

    Re: Access Data Project (A2002)

    That would be my suggestion - we do virtually all of our projects that way these days. Switching to a SQL Server back-end is a fairly easy migration using ODBC linked tables. Then we look for the performance bottlenecks and fix those using views or stored procedures, sometime using pass-through queries if we are querying large recordsets - say 100K records or more with complex joins. ODBC will usually handle simple queries with no more than one join and actually pass the query to SQL Server for processing. When you get multiple outer joins on tables, then Access (or Jet) decides it needs to handle things, and performance goes south in a hurry.
    Wendell

  10. #10
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Data Project (A2002)

    I see, can you recommend any literature that would help in this exact scanario?

    Thanks,
    Mark

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,613
    Thanks
    3
    Thanked 58 Times in 58 Posts

    Re: Access Data Project (A2002)

    A couple of books talk about the different scenarios and when to use them. Microsoft Access Developer's Guide to SQL Server by Chipman & Baron is considered one of the best, though a bit old since it was published in 2000. It contains lots of detail about how to make a SQL Server database work well in addition to the various aspects of the Access front-end. A somewhat more recent book is Alison Balter's Guide to Access 2002 Enterprise Development which focuses mostly on using Access as a front-end to SQL Server (it also talks some about Access advanced topics). It happens to be my personal favorite, as it contains considerable pragmatic advice in addition to the how-to aspects of things. A third book that I'm not familiar with is SQL: Access to SQL Server that has gotten good reviews. Hope this helps.
    Wendell

  12. #12
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Data Project (A2002)

    Thanks! I don't know where I would be without all of the help I get from the VERY gracious helpers on this site.

    Thanks again,
    Mark

  13. #13
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Data Project (A2002)

    Speaking of Jet, do you know how to tell what version of the Jet DB you are running? I have done some looking on MS but it can be difficult to find some info....


    Thanks,
    Mark

  14. #14
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access Data Project (A2002)

    If you want to test the version you're currently running, you can do it with <code>SysCmd(acSysCmdAccessVer)</code>. If you want to know a database file version, you can use something like this:
    <pre>Function TestVersion(strFile As String) As String
    Dim wrkJet As Workspace
    Dim db As DAO.Database
    Set wrkJet = CreateWorkspace("", "admin", "")
    Set db = wrkJet.OpenDatabase(strFile)

    Select Case db.Version
    Case "3.0"
    TestVersion = "Enabled"
    Case "4.0"
    TestVersion = "Converted"
    Case Else
    TestVersion = "Unknown"
    End Select
    Set db = Nothing
    Set wrkJet = Nothing

    End Function</pre>

    Charlotte

Posting Permissions

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