• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 85
  • Last Modified:

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.
0
Trysten
Asked:
Trysten
  • 3
  • 3
  • 2
  • +1
3 Solutions
 
PatHartmanCommented:
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.
1
 
Jim Dettman (Microsoft MVP/ 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.

Jim.
1
 
TrystenProgrammerAuthor Commented:
I should have mentioned, no work-around is acceptable. I'm using https://github.com/timabell/msaccess-vcs-integration 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.
0
Worried about phishing attacks?

90% of attacks start with a phish. It’s critical that IT admins and MSSPs have the right security in place to protect their end users from these phishing attacks. Check out our latest feature brief for tips and tricks to keep your employees off a hackers line!

 
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.
0
 
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.
0
 
PatHartmanCommented:
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.
0
 
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

/gustav
0
 
Jim Dettman (Microsoft MVP/ 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.

Jim.
0
 
PatHartmanCommented:
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.
0
 
Gustav BrockCIOCommented:
Methods provided.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improved Protection from Phishing Attacks

WatchGuard DNSWatch reduces malware infections by detecting and blocking malicious DNS requests, improving your ability to protect employees from phishing attacks. Learn more about our newest service included in Total Security Suite today!

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now