Access97 and SQL efficiency

[Bruce Lawrence]

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

[Larry Linson]

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" <BL*****@gmail.comwrote in message
news:11*********************@75g2000cwc.googlegrou ps.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

[Tom van Stiphout]

On 1 Aug 2006 08:20:40 -0700, "Bruce Lawrence" <BL*****@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