Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Dec 2001
    Location
    Houston
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    AXP with ADO (AXP & SQL Server )

    I have just been handed an A2K that was ported directly to AXP, with SQL Server for the database on the backend. The application uses forms tied to complex SQL queries, and a lot of ADO recordset processing as well. The problem is, requery and refresh no longer work properly, and there are now signficant performance lags. What should I look at for solving these issues?

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

    Re: AXP with ADO (AXP & SQL Server )

    Is it an mdb in 2002 format or an mdb in 2000 format with linked SQL Server tables, or an ADP?
    Charlotte

  3. #3
    Star Lounger
    Join Date
    Dec 2001
    Location
    Houston
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AXP with ADO (AXP & SQL Server )

    It's an ADP -- which I've never seen before. What does that mean/imply?

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

    Re: AXP with ADO (AXP & SQL Server )

    And ADP is an Access Data Project, new to Access 2000 and later. It means that there are NO Access tables in either 2000 or XP format and No queries. The tables are SQL Server tables, the "queries" are SQL Server views and stored procedures, and you can't issue a requery on them like that. If someone imported forms and reports from an Access mdb, then there are probably a bunch of other things that are broken as well.
    Charlotte

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

    Re: AXP with ADO (AXP & SQL Server )

    Unless you want to learn lots about SQL Server in a hurry, and even more about ADPs and the constraints in working with them, I would go back and reconvert the database using an MDB format. You should have little or no trouble with it - in fact A2K mdb files will run cleanly with AXP in nearly all situations. My opinion FWIW.
    Wendell

  6. #6
    Star Lounger
    Join Date
    Dec 2001
    Location
    Houston
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AXP with ADO (AXP & SQL Server )

    Thanks for the information and ideas. I do know lots about SQL Server already, including views and stored procedures, so that is not a problem. The problems in the AXP database come from VBA code that implement ADO recordset logic. This does raise questions about the query logic, though, doesn't it? What's that all about? I must go back and examine it further.

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

    Re: AXP with ADO (AXP & SQL Server )

    Yep - ADO recordset logic is different, and that's one of the things that makes using ADPs a challenge. Unfortunately the upsizing wizard doesn't do even a decent job of sorting out those kind of problems, and it doesn't do well with anything other than basic SELECT queries. Action queries have to be restructured into stored procedures, and that doesn't seem to work well. In addition, if you are using bound forms, things get complicated too. I'm sure MS will get us there eventually, but the top of the hill isn't in sight yet. <img src=/S/heavy.gif border=0 alt=heavy width=40 height=34>
    Wendell

  8. #8
    Star Lounger
    Join Date
    Dec 2001
    Location
    Houston
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AXP with ADO (AXP & SQL Server )

    That is very enlightening. This problem comes from a prospective client who wants me to fix it on a fixed-price basis ... sounds like a very bad idea. Charlotte's idea of switching to an MDB sounds like a good one, but I have to look to see how viable it is in this situation. Thank you very much!

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

    Re: AXP with ADO (AXP & SQL Server )

    Just for the record, that was Wendell's suggestion. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    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
  •