Database grows when SQL tables are linked

[DanJ]

I am desperately searching for a solution to a problem I have with a database
that has an Access FE and SQL server BE. The user has the option of
'relinking' the SQL tables from a login form. The problem is that if they
choose that option, the database grows from 63mb to over 300mb. If they skip
the relinking process, there is no problem. I have followed all the code
that occurs up to that process and there doesn't seem to be anything that
should cause any problems. The growth occurs when the 'tmpTABLE.Refreshlink'
line runs. What does this property do that could cause such growth? (This
size makes the database unusable on some computers.) The main part of the
code is below. I would greatly appreciate it if someone could lead me in the
right direction to solve this problem.

For Each tmpTABLE In mydb.TableDefs
tmptablename = tmpTABLE.Name
tmpconnect = tmpTABLE.Connect
If Len(tmpTABLE.Connect) > 0 Then

tmpTABLE.Connect = ""
tmpTABLE.Connect = "ODBC;DSN=" & tmpDSN & ";APP=" & tmpAPP &
";WSID=" & tmpWSID & ";DATABASE=" & tmpDB & ";Trusted_Connection=" & tmpTC &
""
tmpTABLE.RefreshLink
End If
Next tmpTABLE

 

[david epsom dot com dot au]

I don't have that problem. Check the jet version on that
PC and update if required. Check that only one copy of the
FE is open. Check that the FE is open in Exclusive mode.
Check that Name AutoCorrect is turned off.

(david)

 

[DanJ]

I rechecked and I have the latest Jet Version. The AutoCorrect was turned
on, so I turned it off. The database defaults to 'Shared' mode, but I
explicitly opened it 'Exclusively'. I then ran the 'Relink' process and it
still grows as before.

There are 142 SQL tables that are relinked during the process. The problem
occurs on my PC where I am running a local copy of SQL server and also occurs
on a remote server where I ultimately send the database after I work on it
locally. The remote server is running Access 2002 (XP) and I am running
Access 2003. The database has been converted to '2002 - 2003'.

I also created a new database and copied all the objects to it, to see it
that would fix the problem. The new one also grew when the relinking was
run, so it seems to be related to something in the relinking process.

This is a 'shared' application so it needs to run in the shared mode.
However, when I'm working on it, I'm the only one who has it open but I tried
the exclusive mode anyway and it didn't seem to matter.

Thanks for your help! If you have any other ideas I'd sure appreciate it.

Dan

 

[Roger Carlson]

I'm not sure anything is wrong here. For a test, I created a new, empty
Access 2000 database. The size was 92K. I linked 18 tables from a small
SQL Server database stored locally. The DB jumped to 352K. I relinked the
tables with the Linked Table Manager and it jumped to 580K Relinked again
and it went up to 696K. To me this indicates that simply re-linking adds
space to the database. However a Compact immediately following the last
relink, reduced the database to the original 352K.

So this may just be normal behavior. You've got 142 SQL Server tables and
if they are very large, this may account for it. (I'm not in a position to
test this.) It may be that if you are going to relink, you'll also have to
compact.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Ron Weiner]

We have a Access 2K database that gets linked to a Sql 2K back end (with
something close to 100 tables) and have never had a problem with bloat when
(re)linking.

However our re-linking routine first drops all of the linked Sql Tables
before linking them back again using DoCmd.TransferDatabase acLink, "ODBC",
strCon, acTable, rs!Name, rs!Name

I might also add this re-linking cycle runs in just a couple of seconds
(<10) for the whole deal. While we do find it necessary to compact the
database OFTEN during our development cycles, we typically do not have to
compact it once it is deployed as an MDE. Our deployed compiled, compacted
MDE is just a tad over 9Mb these days. You might want to give this method a
try to see if it helps in your project. Good Luck!

Ron W

 

[david epsom dot com dot au]

I would suggest converting back to A97 and trying again, except
that I'm not sure that your database would fit into an A97 db:
You have 63 MB of local code/forms/reports? We develop using
A97/A2K, so I can't easily test 2002/2003, but I have no reports
of *5 bloating at relink from internal or external clients on
A2002/A2003 in A2K format. We only use A2K format. Although we
have refresh code, I can't guarantee that it has been tested in
A2002/A2003, because our default re-link does

Delete tmp link if it exists
Create New link with tmp name
Delete Old link
Rename New link to correct name

(because of the incompetence of management, we also have extensive
code to automatically catch certain kinds of table name changes)

We normally use DSN'less connections, but haven't noticed any
difference using DSN's. You should check the value of your
connection strings after connection (normally the text changes
slightly after .refreshlink)

(david)

 

[DanJ]

This database belongs to a client of mine and I know that much of it was
originally designed in Access 97 before I was involved. The compacted size
is mostly code/forms/reports with a couple small local tables which store
some local preferences. I no longer have Access 97 but you might be on to
something when you first delete the old connection and then relink. I will
experiment with that. Thanks for your suggestions.

Dan

 

[DanJ]

This database belongs to a client of mine and I am doing some modifications
for them. They claim that the relink logic is used in other databases that
they have without the major growth. I may have to see if I can get my hands
on some of those. Compacting a 300+ mb database can be problematic for those
with limited hard drive space and when the database is shared. As long as I
am in development, I can keep compacting or avoid relinking. Hopefully, I
can find a solution to this before this is rolled out to users.

Thanks for your help! At least I know I'm not crazy when I see the database
growing after I relink since it happened to you too.

Dan

 

[DanJ]

I think that I need to experiment with dropping the link before relinking. I
will see if I can use your method and see what happens. Thanks for your help.

Dan