Re: Backup Linked BE Tables

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hello Mike P ---

What are you doing it in A2003 that is different in A2007? ...

......

Anyway, you can do this with an open file, but you wont be useing the new
file that is created. The following code is merely used just to back up the
back end ...

Public Sub BackupBE(strDestination As String)

Dim oFSO As Object
Dim strSQL As String

'Flush the cache of the current database
DBEngine.Idle

'Create a file scripting object that will copy the db. I use the
scripting
'object because the VBA.FileCopy will not work on an open file
Set oFSO = CreateObject("Scripting.FileSystemObject")
oFSO.CopyFile _
Split(CurrentDb.TableDefs("ALinkedTableName").Connect,
";DATABASE")(1) _
, strDestination
Set oFSO = Nothing

'Compact the new file, ...
Name strDestination As strDestination & ".cpk"
DBEngine.CompactDatabase strDestination & ".cpk", strDestination
Kill strDestination & ".cpk"

End Sub

Hope that helps! ...

--
Brent Spaulding | datAdrenaline | Access MVP

"Mike P" <MikeP@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7349BE1C-1532-4FBC-940C-CE0458B1F7AE@xxxxxxxxxxxxxxxx
I have my database split into a FrontEnd (FE) containing all the modules,
forms, queries, etc., and a BackEnd (BE) containing all the tables. The
BE
is linked. I am trying to write some VB code that will automatically back
up
the BE when the user is running the application. This is working in
Access
2003 however, I am migrating to 2007 and when trying to backup the BE, I
get
an error that the "file is in use". which it is. This is a single user
system.

I am thinking I have to unlink the tables, backup the BE, then relink. but
have not figured out how to do this. So far the code I have is.
strTmpFileName = strBackUpDir & "\" & strFileName & ".accdb"
Application.CompactRepair strCurrentDB, strTmpFileName

It is failing on the Application.CompactRepair since the FE is connected
to
the BE I am trying to backup (and compactRepair while I am at it).

Any ideas?

Thanks,
Mike P.



.