Installation Backup and delete Tables

[jgarry]

Thanks in advance,
I am working on an update install program. Prior to begining the Installation/upgrade I want to backup the clients mdb, this backup will be kicked off from another mdb running the upgrade proceedurs. After the backup I want to remove certain tables in the clients mdb I will then replace the tabels later.

I have seen a few post that get me going in one direction but then I seem to get lost.

for the backup of a remote mdb I have no clue other then asking the user to make a copy prior to doing the update. and how many users will do that?


Here is what I have for the delete remote mdb tables.

Function testdel()
Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim dbLink As Database
Dim tdf As TableDef
Dim strClientDataPath As String

Set db = CurrentDb()
Set rsexport = db.OpenRecordset("07_ClientTableList")
strClientDataPath = rs.rsexport!("ClientDataPath")
Set dbLink = DBEngine.OpenDatabase(strExportdataPath)
For Each tdf In dbLink.TableDefs
If (tdf.Attributes And dbSystemObject) = 0 Then
strname = tdf.Name
' For the Delete this is where Im having a problem
' I have used
DoCmd.DeleteObject acTable, strname ' this delete the tabel if it is local

End If
Next

exit_testdel:
Set rs = Nothing
Set db = Nothing
Set dbLink = Nothing
Set tdf = Nothing

Exit Function

err_testdel:
MsgBox (Err.Number & " " & Err.Description)
Resume Next
End Function


thanks again

Jim Garry

 

[jgarry]

Ok I figured out the delete here is what i had to do, 1st fix some of my code then use the drop table statement in brackets. Brackest becase the person who put this mdb together use names like 90 day Review for table names.

Function testdel()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rsexport As Recordset

Dim dbLink As Database
Dim tdf As TableDef
Dim strExportdataPath As String
Dim strClientDataPath As String
Dim strname As String
Dim strExeCode As String


Set db = CurrentDb()
Set rsexport = db.OpenRecordset("03_ConfigData")
strClientDataPath = rsexport!ClientDataPath
Set dbLink = DBEngine.OpenDatabase(strClientDataPath)
For Each tdf In dbLink.TableDefs
If (tdf.Attributes And dbSystemObject) = 0 Then
strname = tdf.Name
strExeCode = "DROP TABLE [" & strname & "];"
dbLink.Execute strExeCode

End If
Next

exit_testdel:
Set rs = Nothing
Set db = Nothing
Set dbLink = Nothing
Set tdf = Nothing

Exit Function

err_testdel:
MsgBox (Err.Number & " " & Err.Description)
Resume Next
End Function

Now Im working on the Backup function. I would like to do the backup prior to dropping all the tables. After I drop the tabels a backup isn't needed any more

 

[Zion7]

Creating a Back-up, is very easy.
There a several File copy methods.

Access VBA usses it's own , you guessed it, FileCopy()

Takes 2 arguments, File to be copied, and new file

the FileSystemObject, has the CopyFile() (no typo)
I prefer this because, you can copy an OPEN file.

Dim fso As FileSystemObject
Set fso = New FileSystemObject

strBE = CurrentProject.FullName

strBU = CurrentProject.Path & "\BackUp.mdb"


If Dir(strBU) <> "" Then
Kill(strBU)
End If


fso.CopyFile strBE, strBU, True

 

[Zion7]

...forgot to mention,
make reference to

Microsoft Scripting Runtime

and set your fso Object to nothing
Set fso = Nothing