468,765 Members | 1,157 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,765 developers. It's quick & easy.

Linking tables and memory growth

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

Oct 19 '06 #1
2 1644
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]"

Oct 19 '06 #2
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
Oct 19 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Dave Serrano | last post: by
10 posts views Thread by Generic Usenet Account | last post: by
7 posts views Thread by Salvador | last post: by
74 posts views Thread by ballpointpenthief | last post: by
81 posts views Thread by Peter Olcott | last post: by
4 posts views Thread by David C | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.