Linking tables and memory growth

[watto]

I have a large database application with user data in a backend and
forms, code etc in a frontend. It includes a facility for migrating
data from an earlier backend to the current backend by linking to the
second backend and moving data between corresponding tables. Problem is
that Access invariably crashes during this process.

I have taken the migrate forms and code into a separate database and
observed that the memory used by MSACCESS.EXE grows from about 28M to
235M after one migrate operation. There are a little over 200 tables in
the backend database so during the migrate, an additional 200 links are
created in the frontend. Access usually crashes on the second attempt.

I have narrowed the memory growth to the TransferDatabase operation,
but it doesn't matter if I create the link, copy the data and then
delete it, or create all the links, use them and delete them as a
block. Generally it is in deleting the links that Access crashes.

Does anyone have any experience with this problem or any suggestions?

Watto

[Lyle Fairfield]

Does anyone have any ... suggestions?

Don't use links. Just use SQL and explicit designations of the
databases.

eg.

CurrentDb.Execute "INSERT INTO " _
& "[C:\Documents and Settings\Lyle\My
Documents\Access\db1.mdb].[Table1] " _
& "SELECT * FROM " _
& "[C:\Documents and Settings\Lyle\My
Documents\Access\db2.mdb].[Table1]"

[Mat N]

Are all 200 tables really different? ie would it be possible to put
some of the tables into one table with an extra field indicating the
table it was originally in.

Although this may require reworking some of the queries you have
developed based on these tables you may find that this is not such a
difficult task and if a large number of these table are similar it
maybe one query where you previously had many.

watto wrote:

I have a large database application with user data in a backend and
forms, code etc in a frontend. It includes a facility for migrating
data from an earlier backend to the current backend by linking to the
second backend and moving data between corresponding tables. Problem is
that Access invariably crashes during this process.

I have taken the migrate forms and code into a separate database and
observed that the memory used by MSACCESS.EXE grows from about 28M to
235M after one migrate operation. There are a little over 200 tables in
the backend database so during the migrate, an additional 200 links are
created in the frontend. Access usually crashes on the second attempt.

I have narrowed the memory growth to the TransferDatabase operation,
but it doesn't matter if I create the link, copy the data and then
delete it, or create all the links, use them and delete them as a
block. Generally it is in deleting the links that Access crashes.

Does anyone have any experience with this problem or any suggestions?

Watto