Backend database bloating

[GeorgeMar]

My application's backend mdb has been steadily growing
during development. There has been no new data added to
the backend.

During the development there have obviously been a lot of
crashes. Compacting the database doesn't seem to help.
It has grown from 9Mb to 18Mb in about 2 weeeks for no
apparent reasons.

How can I bring it back? Your help will be much
appreciated.

many thanks
george

 

[Gerald Stanley]

Have you considered exporting all the objects to a brand
new database.

Hope This Helps
Gerald Stanley MCSD

 

[GeorgeMar]

Thanks Gerald

Seeing that I have nearly 60 tables, I decided to export
then with the TransferDatabase and I am getting an error
that it can't find the object or it may be misspelt (Run
Time error 3011).

The command I am using is :
docmd.TransferDatabase acExport,"Microsoft
Access", "C:\data\db1.mdb", acTable

This I assume will export all the tables. Even if I put
one table, as in:

docmd.TransferDatabase acExport,"Microsoft
Access", "C:\data\db1.mdb", acTable,"tblEmployees"

and I get the same error.

Any Clues?

George

 

[Gerald Stanley]

Unfortunately the shorthand idea of transferring all the
tables in one statement does not work. The other problem
you were encountering is that both source and destination
names have to be specified.

You will have to transfer them one at a time which should
be possible in a loop if you have prefixed them all with
tbl. Something along the lines of

Dim tbl As TableDef
For Each tbl In TableDefs
If Left(tbl.Name, 3) = "tbl" Then
DoCmd.TransferDatabase acExport, "Microsoft
Access", "C:\data\db1.mdb", acTable, tbl.Name, tbl.Name
End If
Next

Hope This Helps
Gerald Stanley MCSD

 

[GeorgeMar]

Thank you again

That will help. I didn't put the destination because help
said it is optional.

Thanks again
george

 

[John Spencer (MVP)]

An alternative that may be easier.

Create the new database and open it. Then use the Import menu where you can
select all the tables at one time and import them.

Of course, you are not doing this in code, but it is a lot simpler.

Using code to do it you have to make sure you are not importing any system
tables (hidden, but there). So you need to test in the loop whether the table
name starts with MSYS or ...

 

[GeorgeMar]

Thank you John

I did do that. However, the size remained the same.

I read briefly about the issues with indexes accumulating
in the background. Could that be a possibility for the
bloating? If so, is there a way to remove the index
tables in the background?

Thanks
george

 

[TC]

Thank you John

I did do that. However, the size remained the same.

I read briefly about the issues with indexes accumulating
in the background. Could that be a possibility for the
bloating?

Not IMO. It is commonly assumed that "duplicate" indexes waste space. But
that is simply not true, with MS Jet. With Jet, "duplicate" indexes >do not<
take any extra space, beyond the few bytes required for their index names.

HTH,
TC


If so, is there a way to remove the index