backup tables in access (1 Viewer)

[CoachAfrane]

I have this code that backs up all my tables in the same folder.

I want to however back up only specified table with names; “operational areas”, “Communities”, “Zones” and “Projects”.

Can I please get help on how to integrate that into my existing code or any other way to achieve that


Sub backup()
Dim dTime As Date
On Error Resume Next
dTime = InputBox("Create a backup at", , Time + TimeValue("00:00:05"))
If Err.Number <> 0 Then Exit Sub
Do Until Time = dTime
DoEvents
Loop
Dim sfile As String, oDB As DAO.Database
'IF DAO.dll does not load, then find
'ACEDAO.dll in Program Files (64-bit machine) or MS Office AC DB Engine Object
sfile = CurrentProject.Path & "\" & "Staff Data" & ".accdb"
If Dir(sfile) <> "" Then Kill sfile
Set oDB = DBEngine.Workspaces(0).CreateDatabase(sfile, dbLangGeneral)
oDB.Close
DoCmd.Hourglass True

Dim oTD As TableDef
For Each oTD In CurrentDb.TableDefs
If Left(oTD.NAME, 4) <> "MSys" Then
DoCmd.CopyObject sfile, , acTable, oTD.NAME
'OR: DoCmd.TransferDatabase acExport,"Microsoft Access", sFile, acTable,oTD
End If
Next oTD

DoCmd.Hourglass False
MsgBox "Backup is stored in the same folder"
End Sub

 

[CJ_London]

modify this bit of the code

If Left(oTD.NAME, 4) <> "MSys" Then


to something like

if instr(otd.name,“operational areas”) or instr(otd.name,“Communities”) or instr(otd.name,“Zones”) or instr(otd.name,“Projects" Then

 

[CoachAfrane]

modify this bit of the code

If Left(oTD.NAME, 4) <> "MSys" Then


to something like

if instr(otd.name,“operational areas”) or instr(otd.name,“Communities”) or instr(otd.name,“Zones”) or instr(otd.name,“Projects" Then

Perfect
Thanks so much

 

[CoachAfrane]

The code i posted saves the back up copy in the same folder as the original db.
I would like to add another location ( say c:\) so that a copy of the back up is also saved there.

Help please

Thanks

 

[CJ_London]

To hardcode an alternative location, just replace currentproject.path with the path required or if you want the user to browse and select a folder use filedialog - see post#2 on this link

http://www.access-programmers.co.uk/forums/showthread.php?p=1343655#post1343655

 

[CoachAfrane]

I think i am getting something wrong

I placed another line of code after the sfile=...
..because i want a copy of the backup in the c: drive.

The code looks like this now

sfile = CurrentProject.Path & "\" & "Staff Data" & ".accdb"
sfile = "c:" & "\" & "Staff Data" & ".accdb"

It does not creat a back up in either location at all when i run it.

 

[CJ_London]

the second line replaces the first line so you wont be saving to the currentpath location.

So it should have created a file directly to your C drive - assuming you have permission to write to this location