MS Access 2016 scripting interface (COM)

I'm working on an Access app with primitive version control. How can I use COM (or anything else) to tell Access 2016 to compact & repair after launching? I'm assuming the answer will be in C# or powershell, hopefully the latter, as I'm looking to integrate it into commit hooks.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Your shortcut would need to open a dummy access database and that database would run the C&R which would open the real database.

However, in normal split applications, there is no need to CR the FE.  No design changes are ever made and temp tables don't exist in the FE so there is no bloat.  For my users, the FE runs a batch file stored on the server.  The .bat file copies the master copy of the FE to the user's local C: drive and then opens the app.  This means that every time the user opens the app, he gets a clean, fresh copy so if it had been updated since the last time he opened, he also gets the new version.  This method eliminates bloat and distributes updates smoothly.  There are more sophisticated and complex tools to help with this that track versions.  I don't bother.  Whatever is in the master folder is current and that is what I want the users to open.
Jim Dettman (EE MVE)President / OwnerCommented:
<<How can I use COM (or anything else) to tell Access 2016 to compact & repair after launching?>>

 Access has a command line switch /Compact, which can be used to tell it to compact and repair a target DB.   The DAO object lib also has a compact command built-in, but it's better to use Access itself.   There is also a compact on close setting that you can use if your actually executing the DB.

 You do not need to open a dummy database to compact another.

 Layout a little more detail on what the task is (ie. will you run the DB afterwards, etc) and I can fill in some more detail.

TrystenProgrammerAuthor Commented:
I should have mentioned, no work-around is acceptable. I'm using to track changes (required) and MUST C&R often. I do have a system like you described, though, so the users get a "fresh" FE each time they launch my app.
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

TrystenProgrammerAuthor Commented:
The ideal solution looks like this: some powershell runs that finds the running instance of Access2016 that meets some simple name condition and instructs it to compact and repair while running.
TrystenProgrammerAuthor Commented:
The VCS code offers me Import and Export functionality. I'd like to be able to automate the functions. Right now, I have to open access and type the Import/Export commands into the immediate window. I want to automate this, preferably from powershell.

This question is about automating the ExportAllSource step, which needs C&R for consistency.
To export I:
1: Open DB, C&R
2: run ExportAllSource
3. Wait/Done

This may not seem like much but it's getting tedious and I've even made a mistake now.
instructs it to compact and repair while running.

Not possible.  

The CR function copies all objects from one database into another.  It sorts the table data into PK sequence as part of the process.  Once all the objects have been transferred into the temp db, the original db is renamed to a temp name.  the temp db is renamed to the original db name and opened and the original db is deleted.

You could of course make your own C&R code by using the Export to Text feature so you can copy all the form, report, query,macro, and modules to text files and import them in to a new copy of the FE.  Then copy the linked table names and for the embedded tables and the table definitions for the local table.  Then, use a query that sorts them into PK order and exports them to .csv files and then using append queries, adds them back.
Gustav BrockCIOCommented:
tell Access 2016 to compact & repair after launching?

You can't. The closest you can get is to set it to compact & repair when closing, thus:

1: Open DB
2: run ExportAllSource
3. Wait/Done
4. Close DB - with automatic C&R

Jim Dettman (EE MVE)President / OwnerCommented:
<<The ideal solution looks like this: some powershell runs that finds the running instance of Access2016 that meets some simple name condition and instructs it to compact and repair while running.>>

  As Pat has already mentioned, this is not possible.   You can't compact and repair a DB that is currently open.   When you do this through Access, it actually closes the DB, compacts it, then re-opens it.

  As I said in my first comment, you have a few choices:

 1. Open MSACCESS.EXE with the /compact switch and the required parameters.   Access will open, compact the DB, and then quit.  

 2. Use the automatic "Compact on Close" setting for the DB.  Every time the DB is closed in Access, if your the last user, a C&R will be done.

3. Use DAO with C#, VBA, or whatever.  DAO has a compact method built-in.   Note however this is not the same as a Compact through Access.   It's a JET/ACE call which is un-aware of the Access objects themselves.   Works great for a "backend" DB with just data, but not great on a DB with Access objects (forms, reports, etc).

4. There is one other compact and repair option and that is through a DSN, but you can't automate that, so it's out of the running (and it's the same compact as #3, which is at the engine level).

  From what you've said, #1 is probably the best choice.  Your just going to open Access 2x.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
You do not need to open a dummy database to compact another.
 The dummy runs the yourdb.accdb /compact and then opens the db and closes itself.
Gustav BrockCIOCommented:
Methods provided.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Version Control

From novice to tech pro — start learning today.