We help IT Professionals succeed at work.

Access ADP, VBA to run DB RESTORE Stored Procedure

neilmorris
neilmorris asked
on
712 Views
Last Modified: 2008-03-17
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.CloseConnection
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!
Comment
Watch Question

Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Kelly,

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
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.