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


M

[email protected]_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!
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top