Access 97 MSDE Links

[benamar]

I am currently maintaining a 8 year old program.
It's a VB6 front-end to multiple Access 97 MDB files.

Some of our earliest clients have gathered enough data by now, that the
MDB files reached hefty sizes - 200-300 MB in some cases, with millions
of records in each table.

All this creates a lot of network traffic and slows the program on a
peer-to-peer network.

I'm thinking of moving the main and largest tables to an MSDE database,
and putting links in the Access 97 files, so no code changes will be
needed.

My questions are:
1. Did anyone try this before ? If so, can you give me any tips. Did
you see noticable performance changes ?
2. I plan to link to an ODBC DSN. Once I deploy the application, I plan
to create the DSN and point it to the local MSDE server. Will I have to
change anything in the MDB file ?
3. Am I current in my assumption that no code changes will be needed ?

Thanks,

Dan.

[Steve Jorgensen]

On 26 Apr 2005 08:00:53 -0700, "benamar" <da*@rapid-image.com> wrote:

I am currently maintaining a 8 year old program.
It's a VB6 front-end to multiple Access 97 MDB files.

Some of our earliest clients have gathered enough data by now, that the
MDB files reached hefty sizes - 200-300 MB in some cases, with millions
of records in each table.

All this creates a lot of network traffic and slows the program on a
peer-to-peer network.

I'm thinking of moving the main and largest tables to an MSDE database,
and putting links in the Access 97 files, so no code changes will be
needed.

My questions are:
1. Did anyone try this before ? If so, can you give me any tips. Did
you see noticable performance changes ?
Yes, I've tried it before (with PostgreSQL back-end which should be harder
than your case). Yes, it works. In my case, the performance and reliability
improvement were terrific. Of course, we had 100+ users, so the MSDE would
not have been adequate, but for 10 users it would be greate.

As for tips, the same as for any C/S application - keep result record sets
small by doing aggregaring and/or filtering in the SQL queries.
2. I plan to link to an ODBC DSN. Once I deploy the application, I plan
to create the DSN and point it to the local MSDE server. Will I have to
change anything in the MDB file ?
Probably not.
3. Am I current in my assumption that no code changes will be needed ?

You are correct. Still, I would use that as an interim solution until you can
modify the code to work directly with the back-end. It's best to keep the
number of redundant components down to ease maintenance and debugging.

[benamar]

Thanks for the input Steve.
Much appreciated !