When my Access database first opens it does a few things with an SQL database using VBA.

1. It drops the previous database
2. I delete the mdf file if it did not get removed by the drop database statement using fso or kill

the problem is that the file cannot be deleted. It requires admin permissions (Windows 7 Pro). I am the admin on this machine.

How can I deal with this problem?

Thanks!

if your only goal is to create a temporary database, load it with data, process it, and then drop the database... (phew!), I would just see if I couldn't create everything inside TempDB. Objects in TempDB only last the duration of your connection to the SQL Server DB anyway, so there's no need to clean up by explicitly deleting the objects inside it, because SQL Server does it for you.

You would just connect and then execute something like
USE tempDB
GO
...<your code here>

I will redefine my query. This is not a temp database.

I distribute and application that is pushed out to users silently via automation. The installer sets up an instance of SQL Server 2008 / .NET Framework etc silently. After the user launches the Access application for the first time, it creates a new SQL database on the instance that was installed, adds the tables, constraints, users and so forth. Then after the database is created, it automatically syncs with a server "master" SQL database. THis is all done automatically for the user.

If and when I send out an update, the installer again automatically drops the database and creates a new one with all the changes for the updated version of the Access application. It also syncs with the master database after install.

So, the problem is that I can't seems to get the mdf file to delete after I issue the DROP statement. The database drops and I can verify that it is removed from Mgmt Studio. The mdf and ldf files still exist in my directory at C:\my program.
I get an error that permission is denied when trying to delete it.

I have used the FSO delete method and also the Kill method after verifying that the files exist in VBA.

Is there some way I can delete the files by telling it I am the admin?

I am running Windows 7 Pro (I am the admin and only users on this computer)

Ryan,

thanks for clarifying. Now I get it. the one thing that would cause the DROP statement to fail is if you haven't switched to another database. In SQL Server Speak, you would need something like this:

USE MyDatabase
GO
-- do some stuff (great technical term!)
USE master
DROP MyDatabase
GO

the other thing that would cause it to fail is if other users (applications, people) are logged into the database you are trying to delete. I don't think trying to use KILL is a good idea. Normally, SQL Server manages its own instances/databases, and the default location for a database is inside the Program Files folder, so unless you're an Admin on the computer in question, the KILL statement will fail because you don't have rights.

Hope this helps.
Pieter

Regarding Piet's blurb on not using KILL - I agree. Here's a method you can use to kick everyone else off:



The "WITH ROLLBACK IMMEDIATE" means to terminate any active transaction pending on the database which has the effect of kicking off people. Note that if application is open and running against this database it may throw up errors or such but that is at least more cleaner than KILL (some application may not respond as expected to KILL since a new query may be a new SPID so KILL becomes meaningless) and once it's set in single-user mode, you know you won't have anybody coming in at last second and muck up the drop database command.

HTH.

Thank you both. I will try and let you know. The nice thing about this scenario is that the local express database that I am dropping will only be used by one user on one laptop. There will be 40 such users on 40 such laptops that are syncing with a master database using Linq to SQL.
So, If that user is not in the Access front end then this should work.