neilmorris
asked on
Access ADP, VBA to run DB RESTORE Stored Procedure
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
CurrentProject.CloseConnec
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.
Thanks!
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
CurrentProject.CloseConnec
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.
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for your suggestion. I can see how that could work.
If there is a way to do it all in one file I would prefer to seek that solution.
Any other suggestions?
-neilmorris