How do I implement this VBA code? ....

[peskywinnets]

I need to export everything in my database to a new database (my existing one is bloaty), I found this....

http://www.devhut.net/2012/09/27/ms-access-vba-export-database-objects-to-another-database/

but frankly I don't know how to implement.

Could some kind soul outline the steps (assuming it's not a long winded thing for anyone to help with?!)

 

[jdraw]

Have you done a compact and repair?
What bloat exactly?

Often easier to create a blank database, then import everything from current database. But Compact and repair is how you get rid of "space you have used within Access but can not use" until you C&R.

He shows sample usage in the code.

' Usage:
' ~~~~~~
' ExpObj2ExtDb "c:\databases\dbtest.accdb"

 

[peskywinnets]

Thanks I was looking at it the wrong way round ....i.e. I needed to create fresh database & import my old one(being sue to 'select all' when importing). This has seen a drop in DB size from 550MB to 47MB (compact was having little affect)

 

[jdraw]

What was causing the bloat??

 

[peskywinnets]

I know what's causing the bloat but I don't know how to solve it (well I sort of do, but don't have the time to address ....or I suspect the access knowledge)

Each day I add data to access ...I import a text file, anything 'new' in the text file is added to an access 'intermediatary' table, the data in this table gets modified/massaged in this intermediatary table & then finally adds to a final table, entries more than a month old in the intermediatary table then gets deleted (the bloat is coming from the data import into the intermediatary table)

Yes, I should split out the data/normalize & yes rather than bringing the data in I could likely just link to the text file & then add 'new' data to to the final table (& massage it in the final table...this elimiate the need for an intermediatary table)....but actually the time to bloat from 50MB to 500MB is about 5 months.....it only took me 15 mins to setup a new database & import all the data...so not a great time burden (it would probably take me a good while to set up a more efficient way of handling this data!)

 

[jdraw]

When you use Access and build "something" (table, query , form etc) and adjust/edit it or delete it, you are taking up space within Access. That space is not available to you until you run a Compact and repair. The more you develop and are constantly changing a design, the more unavailable space you are using. In the old days, this was termed "garbage" -space no longer available to you, but still within the application(Access). Then, a utility to perform "garbage collection" -recover the unavailable space (Compact and Repair)is run and makes all space available to you again.

Compact and repair actually does create a new database, copies content representing your latest material, then renames that database - thereby removing "the garbage".

If you have a process that works for you, then stick with it. If you are having speed or accuracy issues, then do a "redesign" separately. Import/load your new design with test data, test it rigorously, then move existing data to your new design.

Regular backups and running of Compact and Repair should be part of your operation.

Good luck with your project.

 

[shoji]

Just a thought.

I use a temporary database to deal with the kind of data you are talking about. I save this temp db in the same folder as the main data db and when the front end db is opened, the first thing it does is to copy this temp file onto the user's temp folder. Yes, when the user changes, it must re-link them programmatically. (Of course you can use Public folder if that is not a problem.) Because this temp db is on the user's PC, it is much faster than the one on the server. And each session starts with the empty temp db.

It is a pretty involved setup, but I think what you get justifies this somewhat complicated setup. Often times, instead of using complex queries, it is easier and faster to dump the selected data to the temp table and work from it.

My 2 pennies.

Shoji