Open, Compact & Repair, then close Access Database via VBA

Hi,

I have an excel file which, via power query, imports thousands of rows of data from an access database. The sheet has multiple users but is password protected meaning only I, have write access.

I am relatively new to Access so forgive me for the basic questions:

1. Having only created the sheet recently, I am constantly having to "compact and repair" the database - does that sound right or could there be something wrong with the database?
2. I have amended the settings of the database so that on close, the compact and repair function is run. With that in mind is it possible to:
i) Write some code (in the excel file) to open and close the database (and therefore I assume the compact and repair function will be run)
ii) If the above is not possible then, write the code to open the database, run the repair, then close?

From my research I have written the following:

Please Login or Register  to view this content.

Can someone verify whether this looks correct and what I need to add, for the code to run the compact and repair function?

Any help would be great.

Many Thanks

Hi,

and what the appropriate Access forum says ?!

Another question : why using Excel, what could be the benefit instead of just using Access ?

Try also to use a local path rather than a network path to see if you still have the same repair issue …

Hi,

I have yet to submit the questions in the Access forum but can do so today.

The data originates from a CRM system which then goes into Access. From their power query is used to manipulate the data into Excel. Excel is used as most colleagues (including myself have only ever used Excel). Further, the data used to go from the CRM system into Excel. It I experienced lots of issues with power query locking the source file.

Lastly, I am unable to change the path due to the setup at work.

Thanks

In Access VBA help see CloseCurrentDatabase and its sample.
For more question about Access ask on an Access forum …