I am trying to run a stored procedure to RESTORE the database that the Access ADP is connected to. The database is running Locally on Microsoft SQL Server Desktop Engine v8.0. Running in Access XP.
I have created a SP in the master database to restore the database "CustomerDB"
When I run 'exec sp_who2' I get 2 connections to "CustomerDB" when the Access ADP file is open.
I have tried to close the connection in the Access ADP by using the VBA code
But there still remains one connection to "CustomerDB".
So when I run the RESTORE SP I get "Exclusive access could not be obtained because the database is in use"
There are no other users connecting to this database.
There are no forms, tables, reports, etc open that I can see.
I am aware of the Access functionality in Tools->Database Utilities->Restore SQL Database, but that is a route that I would rather not give the user.
Is there a way to make the Access ADP connect to a different database, do the restore, then switch back?
The user needs the ability to do restores, but without themselves manually changing the connection or manually doing the database restore. All must be done with the click of a button, so that is why I need it all in VBA.