Solved

access vba run-time error '70' permission

Posted on 2010-11-11
6
1,026 Views
Last Modified: 2012-05-10
I am getting the error "run-time error '70' permission denied" when performing
FileCopy file1, file2
This is due to the fact that file1, which is an Access database, is open.

Is there a way around this? This code is run overnight and obviously some users are leaving the db running. This is not something I can do much about so need to find a work-around.

Any ideas?
0
Comment
Question by:rick_danger
6 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
try this, place the whole code in a regular module



pass the path and name of currentdb and new path and db name
could be on the same path/folder but must be different db name, say add a datetime to the backup copy
Option Compare Database

Option Explicit



Private Declare Function CopyFile Lib "kernel32" Alias "CopyFileA" (ByVal lpExistingFileName As String, ByVal lpNewFileName As String, ByVal bFailIfExists As Long) As Long



Public Function fnc_CopyFile(ByVal strCurrentDB As String, ByVal strBackUpCopy As String) As Boolean



    Dim ret As Long



    On Error Resume Next

   

    DoCmd.Hourglass True

    ret = CopyFile(strCurrentDB, strBackUpCopy, True)

    If ret = 0 Then

        MsgBox "File Copy failed.", vbCritical, "File Copy"

        fnc_CopyFile = False

    Else

        fnc_CopyFile = True

    End If

    DoCmd.Hourglass False

End Function

Open in new window

0
 

Author Comment

by:rick_danger
Comment Utility
Thanks capricorn1
I think that will only tell me if it failed. I need to find a way to make it work, even if the database is open. I need to somehow find a way of copying a database file that is already open.
 
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
that code is suppose to copy the open database, so, try it first.
of course if there is an error when copying the file, it will tell you that the copy process failed.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:rick_danger
Comment Utility
I don't quite understand where to put and how to run this code. This is part of my code:
Public Sub DoRebuild()
    ' First off ... copy "R:\Stephen hill time ..." to Local C: Drive
   
    strSource = "R:\Databases\time and Expenses Database.mdb"
    FileCopy strSource, "R:\Databases\TimeExpensesBackup.mdb"
    FileCopy strSource, "C:\Databases\time and Expenses Database.mdb"
    ' then - copy the table "InvoiceSummary" - (within the 'un-copied' file)
    ' (so as to retain Notes and Invoice Preparer data)
    ' On the fly, get and use the copy as a look-up
    ' Finally, at the end, delete the copy (so as it can be copied again)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Where did you get the code from?
The author of that code should provide you with instructions.

Most Experts here don't like to comment on code that they did not create, because it is not known if the code is even valid for what you need to do.

Beside, there is no way to "Work around" the error or "make it work".
The fact of the matter is that you cannot (easily) copy a database file that is Open and/or In use.

Furthermore if you try to close the DB file that is in use, corruption may result.

You need to make sure the file is closed before trying to access it.
So your first step would be to figure out why the file is being help open.

You can also "BackUp" the DB by creating code that copies all of the Objects to another DB.
(But this will involve more complex code)

Or investigate this group of products:

This will perform almost any basic database maintenance task:
http://www.fmsinc.com/MicrosoftAccess/Scheduler.html

This will monitor who in in the DB and give you options for kicking them out.
http://www.fmsinc.com/MicrosoftAccess/monitor.asp

JeffCoachman


0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 500 total points
Comment Utility
if you use the File System object, you will not have this issue.  it will allow you to copy an open Access database,

mx
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now