Access97 and SQL efficiency

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Bruce Lawrence
    #1

    Access97 and SQL efficiency

    I have a few Access 97 DB's and I'm linking SQL tables via ODBC
    connection in vba code.

    I've found that the performance is slowing down. I have a few macros
    that import TXT files using import specs and from there it begins a
    series of queries to manipulate the table and eventually put it into
    the linked SQL tables.

    When I started it was rather quick however now I'm finding that the
    process is taking longer.

    Is it quicker to execute this type of thing through modules and VBA or
    am I already doing it the most efficient way by using multiple queries
    and calling them with macros.

    Thanks for any advice

    Tags: None
    • Larry Linson
      #2
      Re: Access97 and SQL efficiency

      There may be a few isolated cases where using ADO or DAO code in VBA might
      prove faster, but the database engine is usually very, very efficient in
      executing SQL, compared to our code. You might speed things up, if you
      aren't already, by using local-to-your machine temporary linked tables
      created in a temporary datase for any intermediate manipulations that
      require writing to tables. There's a good article on this technique at MVP
      Tony Toews' site http://www.granite.ab.ca/accsmstr.htm, but it uses VBA
      code.

      You could also investigate whether you might gain some performance by using
      passthrough queries or stored procedures on the server DB -- but if you are
      still using macros, not yet using VBA code, then that might be a daunting
      learning curve.

      Larry Linson
      Microsoft Access MVP


      "Bruce Lawrence" <BL32375@gmail. comwrote in message
      news:1154445640 .788278.13450@7 5g2000cwc.googl egroups.com...
      >I have a few Access 97 DB's and I'm linking SQL tables via ODBC
      connection in vba code.
      >
      I've found that the performance is slowing down. I have a few macros
      that import TXT files using import specs and from there it begins a
      series of queries to manipulate the table and eventually put it into
      the linked SQL tables.
      >
      When I started it was rather quick however now I'm finding that the
      process is taking longer.
      >
      Is it quicker to execute this type of thing through modules and VBA or
      am I already doing it the most efficient way by using multiple queries
      and calling them with macros.
      >
      Thanks for any advice
      >

        Comment

        • Tom van Stiphout
          #3
          Re: Access97 and SQL efficiency

          On 1 Aug 2006 08:20:40 -0700, "Bruce Lawrence" <BL32375@gmail. com>
          wrote:

          If you want the best performance, you probably need to let SQL Server
          do most of the work: use DTS to import the text file, and use a stored
          procedure to manipulate the data.

          -Tom.

          >I have a few Access 97 DB's and I'm linking SQL tables via ODBC
          >connection in vba code.
          >
          >I've found that the performance is slowing down. I have a few macros
          >that import TXT files using import specs and from there it begins a
          >series of queries to manipulate the table and eventually put it into
          >the linked SQL tables.
          >
          >When I started it was rather quick however now I'm finding that the
          >process is taking longer.
          >
          >Is it quicker to execute this type of thing through modules and VBA or
          >am I already doing it the most efficient way by using multiple queries
          >and calling them with macros.
          >
          >Thanks for any advice

            Comment

            Previous template Next
            Working...