Acc03 FE - SQL2000 BE

[JuriSim]

I have written several app's.
Front-End is always Access, Back-End SQL-server (2000 or 2005).

So, I make use of linked-tables to the sql-db.
Each time my application starts, I relink the tables.

code looks like:
For each tbl in Currentdb.Tabledefs
tbl.refreshlink
Next tbl

After re-linking; my access-application grows extreme!!
For example:

Without relinking (and fully repaired) my application.mdb is 27Mb.
After relinking it grows to over the 140Mb.

Any suggestion is welcome !!

 

[SQL_Hell]

Hi,

Why do you bother to re-link the tables when the application starts?

 

[JuriSim]

I design the app at home - linked to my sql-db
when I install this app at customer's side, all tables should be relinked to their SQL-db.
Also: when sql-server crashes (never happened before) i can simply reconnect to another sql-server.
Relinking of my tables makes uses of "link-files" where my connection is stored.

 

[MSAccessRookie]

I design the app at home - linked to my sql-db
when I install this app at customer's side, all tables should be relinked to their SQL-db.
Also: when sql-server crashes (never happened before) i can simply reconnect to another sql-server.
Relinking of my tables makes uses of "link-files" where my connection is stored.

OK, Explanation accepted. Unfortunately, creating a new link means that the existing one is discarded and replaced. It remains behind unused in the database until the next time that you do a Compact/Repair to remove it and other discarded items. Doing this over and over each time you restart, only makes the database grow unncessarily.

Another way to think about the same thing would be to create a function to change the links that is not part of the normal process, and is executed on demand as required. As long as it has a parameter with the database name you should be able to use it to connect to any available database.

 

[JuriSim]

Thanks for your replies.
I don't create new links each time I restart my application.

It's simply a Refreshlink-command.

Like MsAccessRookie suggests, i make use of a parameter. I don't refresh each time. Only the first time when client starts up his application.

For example:
My app is about 29Mb. When client starts it grows immediately to 140Mb. It stays this big till I compair en compact the App-Db.

Now I have made use of the "Auto compact on close".

My post was only to find out what the reason of this big grow is...

Kind regards.