Update/Refresh of linked SQL ODBC .Connect string bloats Database

[MarkB_@_Franklin-TN]

My Access 2007 SP1 database has mysterious growth issues after I update SQL
SERVER ODBC .Connect strings for roughly 10 linked tables and 10 pass-through
queries (stored procedures). Code/connection string work but growth in size
of Access database is unmanageable (starts at 700K, then 3.5 MB, then 7 MB,
etc.) Compact on Close and Compact/Repair don't shrink file but using Backup
tool does shrink the new file back to original ~700K size.

Code sample below:

Public Sub RefreshODBCLinks(newConnectionString As String)
Dim db As Database, tdf As TableDef, qdf As QueryDef

Set db = CurrentDb

For Each tdf In db.TableDefs
If Left(tdf.Connect, 4) = "ODBC" Then
tdf.Connect = newConnectionString
tdf.RefreshLink
End If
Next tdf

For Each qdf In db.QueryDefs
If Left(qdf.Connect, 4) = "ODBC" Then
qdf.Connect = newConnectionString
qdf.Close
End If
Next qdf

db.TableDefs.Refresh
db.QueryDefs.Refresh


Set tdf = Nothing
Set qdf = Nothing
Set db = Nothing

End Sub

Can you help me solve this mysterious Access 2007 growth/bloating issue?
Your help appreciated!

 

[Alex Dybenko]

Hi,
never saw such behavior, but try to delete and create tables again, like
here:
http://www.mvps.org/access/tables/tbl0010.htm

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com