|
Subject:
|
Renaming remote mdb file
|
|
Posted By:
|
elansolutionsltd
|
Post Date:
|
11/4/2005 7:08:57 AM
|
I have a database which controls the operation of 15 others. At times the remote databases grow too large and need compacting. Rather than doing this manually, I want to programme the master database to firstly compact the remote databases and then repopulate them. One can use Application.CompactRepair to do the first stage, but this demands a new name for the compacted database. I can then use KILL to delete the original database, but I cannot find a way of renaming the compacted database with the original name. Would a Shell command work here? Can anyone suggest another way to do this?
Thanks
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
11/4/2005 8:32:32 AM
|
I know you can do this in two steps from Access code. The first is to copy the file with the name you want, and the second is to delete the old file:
'-----
Set WshShell = WScript.CreateObject("WScript.Shell")
Set objFSO = CreateObject("Scripting.FileSystemObject")
objFSO.CopyFile "C:\YourOldFileName.mdb", "C:\YourNewFileName.mdb", True
objFSO.DeleteFile("C:\YourOldFileName.mdb")
'-----
The "True" boolean is to overwrite an existing file with the same name.
HTH
mmcdonal
|
|
Reply By:
|
crapanz
|
Reply Date:
|
11/4/2005 3:42:25 PM
|
Do you need a reference to some Shell Object or Scripting command in MS Access?
Database Agreements
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
11/7/2005 10:13:14 AM
|
Sorry for the delay:
You will need to declare some items if you have option explicit. I generally do the following and end up with Variants:
Dim WshShell
Dim objFSO
Notice I don't "type" the variables (e.g. "As String," "As Long" etc.)
Otherwise it works with MDAC and WMI. Are you having any problems using this?
mmcdonal
|
|
Reply By:
|
elansolutionsltd
|
Reply Date:
|
11/8/2005 4:30:43 AM
|
Sorry, but I cannot get this to work. I have tried 'Dim'-ing the variables as either nothing ( = a Variant), directly as a Variant, and even as an Object, but the programme fails to recognise the commands. I have also tried adding the 'Microsoft Script Control 1.0' to my references [the only one that looked even remotely relevant] with no effect. There must be something else I'm missing. Any thoughts?
Thanks for trying
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
11/8/2005 7:38:20 AM
|
Sorry, slight modification. I got this to work on a button click:
'-----
Private Sub Command0_Click()
Dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")
objFSO.CopyFile "C:\YourOldFileName.mdb", "C:\YourNewFileName.mdb", True
objFSO.DeleteFile ("C:\YourOldFileName.mdb")
End Sub
'-----
HTH
mmcdonal
|
|
Reply By:
|
elansolutionsltd
|
Reply Date:
|
11/11/2005 5:25:56 AM
|
Works perfectly. Thanks very much, you have no idea how much this helps.
Spikey
|
|
Reply By:
|
crapanz
|
Reply Date:
|
11/11/2005 6:00:04 AM
|
Hmm.
I wonder what happens if the .mdb is locked.
Do you get an Error and would you deal with it gracefully?
Database Agreements
|
