Compacting a linked mdb through VBA code

[TheQuestioner]

Does anyone know how to compact a linked/attached MDB from the front-end MDB by code? I've looked at all the threads on this site that mention compacting, but they all refer to the current database.

Other than creating a standalone VB exe that invokes the ADO Compact MDB method, does anyone have any ideas?

 

[RoyVidar]

Hi!

Have you had a look at the CompactRepair method of the application object? Or the CompactDatabase method of the DAO dbengine object? I think both of them should be able to do the trick.

Only thing, you'll have to kick the users out of the database first, but there should be some snippets available in the faq area for those purposes.

Roy-Vidar

 

[hymn]

have a look at
thread705-635180

Hope this helps
Hymn

 

[TheQuestioner]

Thanks RoyVidar and Hymn,

After some investigation, I discovered that you can reference both the ADO and DAO libraries at the same time. This is fundamentally what was stopping me from using the DAO CompactDatabase menthod initially – as I was using ADO throughout my app.

I used the following code

Const dbSecUserRosterGuid = "{947bb102-5d43-11d1-bdbf-00c04fb92675}"
objConn.OpenSchema(adSchemaProviderSpecific, , dbSecUserRosterGuid)

to detect if there are any other users logged in. I then used the

objDB.TableDefs("<linked tablename>").Connect

method to extract the file path of the backend. This left me with just having to execute the CompactDatabase and then kill/rename the old/new mdb's.

I hope this helps others.

 

[wendyp]

I'm sorry, I'm lost.

What is objConn dimmed as? New Connection?
What is objDB dimmed as? ADO database?

When I tried it that way, I get an error that it's expecting an "=" on the objConn line.

Thanks,
/Wendy