ShawnGray
asked on
Copy .accdb in use
Access o365, VBA, copying a ".accdb" file to same location with a new file name.
The routine works unless someone is using that particular file.
Is it possible to get around that?
If the file is in use I get a runtime error 70 "permission denied".
If the file is not in use, it copies fine.
The routine works unless someone is using that particular file.
Is it possible to get around that?
If the file is in use I get a runtime error 70 "permission denied".
If the file is not in use, it copies fine.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
...one other thought; why can manually copy an .accdb file in use?
Only vba seems to have an issue with it.
Only vba seems to have an issue with it.
Manual copy uses Windows, not Access/VBA. Windows doesn't care about the lock file.
You might try using another technology such as the Win32 API or script to copy the file.
You might try using another technology such as the Win32 API or script to copy the file.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
The solution is what?.....
ASKER
Mark,
That's awesome. Perfect.
Really appreciate the help.
Shawn
That's awesome. Perfect.
Really appreciate the help.
Shawn
Keep in mind that copying an open Access file might give you a corrupted result. Internal pointers may point to the wrong place,
Also, I am too late with for the following to be a solution, but it works.
Sub copy()
Dim str As String, pid As Long
Path = "C:\EFG Front Ends"
srcfile = "Front End Address Book.mdb"
tgtfile = "NewName.mdb"
str = "cmd.exe /s /k copy 'path\src' 'path\tgt'"
str = Replace(str, "path", Path)
str = Replace(str, "src", srcfile)
str = Replace(str, "tgt", tgtfile)
str = Replace(str, "'", Chr(34))
On Error Resume Next
Kill Path & "\" & tgtfile
On Error GoTo 0
pid = Shell(str, 0)
End Sub
Also, I am too late with for the following to be a solution, but it works.
Sub copy()
Dim str As String, pid As Long
Path = "C:\EFG Front Ends"
srcfile = "Front End Address Book.mdb"
tgtfile = "NewName.mdb"
str = "cmd.exe /s /k copy 'path\src' 'path\tgt'"
str = Replace(str, "path", Path)
str = Replace(str, "src", srcfile)
str = Replace(str, "tgt", tgtfile)
str = Replace(str, "'", Chr(34))
On Error Resume Next
Kill Path & "\" & tgtfile
On Error GoTo 0
pid = Shell(str, 0)
End Sub
ASKER
Mark, I used your public function.
Rberke, thank you as well. Haven't tried it but it looks helpful.
Rberke, thank you as well. Haven't tried it but it looks helpful.
Mark's solution is better than mine for copying a file. Mine is more general and can be used for most Command Line functions.
You're welcome rberke.
As always, I learn a little bit from everyone who participates is these discussions.
As always, I learn a little bit from everyone who participates is these discussions.
Sorry but it makes no sense to replace a copy of the BE while the file is open. If the BE is never updated, at least open it as read-only.
I'm not sure why you accepted all these comments as the answer. The whole point of accepting an answer is to help others zero in on the solutions. Rewarding experts for participating does not serve any purpose.
ASKER
Pat,
originally those answers were accepted because I believed it was a final result. Only after that did a solution to accomplish my objective present itself.
Regarding the 'makes no sense to replace a copy...', the logic behind this need was not disclosed. Although it wasn't relevant to the objective, it does serve a purpose.
originally those answers were accepted because I believed it was a final result. Only after that did a solution to accomplish my objective present itself.
Regarding the 'makes no sense to replace a copy...', the logic behind this need was not disclosed. Although it wasn't relevant to the objective, it does serve a purpose.
ASKER
So this must have changed from mdb to accdb.
If so, I'll find a workaround.
Thank you again