Database keeps growing

[Carol Grismore]

I have an Access 2002 database that just keeps growing, and no amount of
repairing and compacting will fix it.

The trouble started when I put in some code. It's pretty simple; all it
does is reconnect all the tables linked to our Sybase databases. We have
development, test and production versions of the same Sybase database, and
it's handy to be able to switch back and forth between these using this code:

----------------------------
Sub AutoReconnect()

Dim db As Object
Dim tdf As Object
Dim cnt As String
Dim X As Integer
Dim rtn As Integer

rtn = SysCmd(acSysCmdSetStatus, "Reconnecting database objects...")

Set db = CurrentDb

For X = 0 To db.TableDefs.Count - 1

Set tdf = db.TableDefs(X)
cnt = tdf.Connect

' Already connected to the requested Sybase database.
strCurrent = Mid(cnt, 10, 6)
If strCurrent = strDSN Or strCurrent = (strDSN & ";") Then Exit Sub

' Refresh all connections.
If tdf.name Like "dbo_*" Then
cnt = "ODBC;DSN=" & UCase(strDSN) & ";SRVR=GRANT;DB=" &
LCase(strDSN) & ";"
tdf.Connect = cnt
tdf.RefreshLink
End If

Next X

End Sub

----------------------------

I am wondering if I have forgotten to do something that is causing Access to
grow so alarmingly. The particular database I'm referring to started out at
21 MB and is now 51 MB. The only thing we've really done in it is exercise
the code above.

Thanks in advance for your help.

Warmest regards,
Carol

 

[Tom Wickerath]

Hi Carol,

Check out these Microsoft KB articles:
http://tinyurl.com/2dmpw

For one thing, you should set your tabledef and database objects = Nothing
at the end of your procedure. Unless you have a real need to use the Access
2002 file format, you will likely have better luck using the Access 2000 file
format.

Tom
________________________________________

:

I have an Access 2002 database that just keeps growing, and no amount of
repairing and compacting will fix it.

The trouble started when I put in some code. It's pretty simple; all it
does is reconnect all the tables linked to our Sybase databases. We have
development, test and production versions of the same Sybase database, and
it's handy to be able to switch back and forth between these using this code:

----------------------------
Sub AutoReconnect()

Dim db As Object
Dim tdf As Object
Dim cnt As String
Dim X As Integer
Dim rtn As Integer

rtn = SysCmd(acSysCmdSetStatus, "Reconnecting database objects...")

Set db = CurrentDb

For X = 0 To db.TableDefs.Count - 1

Set tdf = db.TableDefs(X)
cnt = tdf.Connect

' Already connected to the requested Sybase database.
strCurrent = Mid(cnt, 10, 6)
If strCurrent = strDSN Or strCurrent = (strDSN & ";") Then Exit Sub

' Refresh all connections.
If tdf.name Like "dbo_*" Then
cnt = "ODBC;DSN=" & UCase(strDSN) & ";SRVR=GRANT;DB=" &
LCase(strDSN) & ";"
tdf.Connect = cnt
tdf.RefreshLink
End If

Next X

End Sub

----------------------------

I am wondering if I have forgotten to do something that is causing Access to
grow so alarmingly. The particular database I'm referring to started out at
21 MB and is now 51 MB. The only thing we've really done in it is exercise
the code above.

Thanks in advance for your help.

Warmest regards,
Carol
l

 

[Carol Grismore]

I figured out that "Nothing" was missing (heehee) as I was posting. I went
into the database and added it to the existing code. The outcome is -- the
database still grows alarmingly, but can now be compacted back to its
original size.

Thanks for your response. I will go to our management with the 2002 vs 2000
opinion. I've been pushing returning to 2000 based on input from this forum.

Thanks again!

 

[Tom Wickerath]

Thank You for marking my reply as an answer. This is appreciated.

Good Luck with your project.

Tom
_______________________________________

 

[Ken Snell [MVP]]

I'm curious, Tom... what is the attraction for the 2000 format?

 

[Tom Wickerath]

Hi Ken,

http://support.microsoft.com/?id=810415


Tom
_____________________________________

 

[Ken Snell [MVP]]

Thanks. I hadn't seen this article before, although I've seen discussion of
the symptom.