Why can’t I copy the current database? (Access 97 SR2)

Topic

New Reply

Here is what I’m trying to do, basically just save the current db to the a: drive so that it can be carried around to different pcs:

Sub saveUpdateFile()
On Error GoTo HandleError
Dim strSaveLocation As String
Dim strTemp As String
Dim db As Database

strSaveLocation = InputBox(“Please enter the letter of the drive where you wish to save the update file:”, _
“Save Update File”, “A:”)
If Right(strSaveLocation, 1) “” Then
strSaveLocation = strSaveLocation & “”
End If

If Mid$(strSaveLocation, 2, 1) “:” Then
strTemp = Left$(strSaveLocation, 1) & “:”
strSaveLocation = Right$(strSaveLocation, Len(strSaveLocation) – 1)
strSaveLocation = strTemp & strSaveLocation
End If

Set db = CurrentDb()

FileCopy db.Name, strSaveLocation & “UserUpdate.mdb”

Exit Sub
HandleError:
Select Case Err.Number
Case Else
MsgBox Err.Number & ” ” & Err.Description, , “Save File Error”
End Select

End Sub

The problem with this code is that it generates Error 70, permission denied, when trying to copy the current database to a floppy.

Normally, I would just move on & try something else, but what confuses me is that the following code DOES work properly. And if this code works, why won’t the above?

With objOutlookMsg
Set objOutlookAttach = .Attachments.Add(db.Name)
.Subject = strSubject
.Body = strBody
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
Next
.Display
End With

Note that to attach the current db to an email, all I have to do is reference it by it’s full name (path & name). So if I can attach it to an email, does anyone know why I’m unable to copy the exact same db to a disk? Does it have something to do with the file being open, and if so, why would it work to copy it as an attachment but not copy it as a file?

Thanks in advance for your help,

Cecilia 🙂

Reply | Quote

Replies

You can copy a different database from within Access, but you can’t copy the current database like that any more than you can backup the current database from itself. The code attaching the database to an email is doing something entirely different, which I don’t pretend to understand.

If you want to save the database to the A: drive, why are you trying to do it from within code anyhow? What’s wrong with simply copying it from Windows Explorer?

Reply | Quote

Hi Charlotte–

Why am I trying to do it? Because my boss told me to 😉

Basically, this db updates a db for another program. That’s the user side. Then there’s an admin side, which has options for distributing the database, one of which is to email it to the users. My boss decided that the other option would be that an admin would want to put the db onto a floppy and run around to everyone’s pc to update it. Therefore, we would need a save button that copies the program to a floppy.

Sometime yesterday I told him that the cost/benefit of such a button wasn’t there. So I included instructions on how to open Win Explorer and copy it to a floppy. Yes, he does require that level of detail 😉

But overall, it just doesn’t make sense to me that I can attach the current db but I can’t copy it. Oh, well, I guess not everything has to make sense 😉

Thanks!

Cecilia 🙂

Reply | Quote

Why don’t you use a batch file with a shortcut on the desktop ?

Reply | Quote

Thanks, but that defeats the object of doing everything from within the admin panel. He wanted them to click a button and have the file copied, done.

Reply | Quote

Bosses frequently want things that look simple to them but aren’t or are not within the allowed functionality of the application. Sometimes we have to find diplomatic ways to tell them they’ll have to accept a different solution.

Reply | Quote

Mix the two ideas, write a batch file and use the shell command from the panel to trigger the batch file.

HTH

Peter

Reply | Quote

Thanks, but this db was supposed to simplify a multi-file process (which included batch files) into a single-file distribution. If I could write the batch command _within_ the db, then it would work, but as long as I need a separate batch file, I’m better off without it.

Cecilia 🙂

Reply | Quote

You could aways create the batch file on the fly and destroy it behind you I suppose

Reply | Quote

Hey, now THAT’s a cool idea!

Here’s what I did:

I created a batch file in a temp location. The batch file copies my db to the temp location with another name. Then using FileCopy, I am able to copy the copy of hte db from the temp location to the actual location.

The reason I did it this way is that the batch file runs on its own, so there’s no error handling and such. I error handled the temp location as much as I could, so I hope there are no errors. But for the user save location, I needed the error handling on the FileCopy command, as well as its ability to use long file names (and names like “My Documents” which aren’t read properly in a batch file.

You get some awesome cool points for that creative solution!

Thanks again,

Cecilia 🙂

Reply | Quote

Wise words. But unfortunately, it was my fault: I thought, if I can attach it, why can’t I save it? Still doesn’t make sense to me, but he _does_ understand the meaning of “It’s a Microsoft Feature.” I’ve taught him well enough 🙂

Thanks for all of your help!!!

Cecilia 🙂

Reply | Quote