I am continuing to update the front-end of a split DB. I update a local
copy of the FE which is tied to a standalone copy of the BE. I am
trying to automate the 'go-live' procedure for distributing the FE to
my users.
I want to:
Copy the FE from my desktop, where I work w/ the local copy, to C:\Data
(where shortcuts on all user PCs point to, as well as my live
shortcut). The new file should then refresh all linked tables so that
they point to the network back-end DB and not the local back-end DB.
Then the newly copied/linked FE should be copied to the network for
user download.
Access keeps getting locked up when I try to close the FE DB. Below is
probably more information than needed, but I'm hoping most of it will
be helpful. I am using Workgroup security.
This is the approach I have been taking:
From the FE: 1. Open a 'GoLive' DB that will handle code while FE is closed.
2. Call procedure in GoLive.mdb.
----code----
'This procedure seems to be working fine
Private Sub cmdGoLive_Click()
Static acc As Access.Application
Dim db As DAO.Database
Dim strDbName As String
strDbName = "C:\Data\GoLive.mdb"
Set acc = New Access.Application
acc.Visible = True
Set db = acc.DBEngine.OpenDatabase(strDbName, False, False)
acc.OpenCurrentDatabase strDbName
Set db = Nothing
acc.Run "CopyAndLink", CurrentProject.FullName, GetNewVersion
End Sub
----end code----
From GoLive.mdb:
3. Close the FE 'This is where Access locks up.
4. Copy the FE from my Desktop to C:\Data
5. Open the newly copied FE DB.
6. Run the Refresh Links procedure in the FE.
7. Close the FE.
8. Copy the FE to the network drive.
----code----
Public Sub CopyAndLink(strCurrentPath As String, strNewVersion As
String)
On Error GoTo GoLiveErr
Static acc As Access.Application
Dim strNewDBLoc As String
Dim db As DAO.Database
Dim strDbName As String
Set acc = New Access.Application
Set db = acc.DBEngine.OpenDatabase(strCurrentPath, False, False)
'This ^^^^ causes everything to freeze. I haven't debugged past
this code yet.
acc.CloseCurrentDatabase
Set acc = Nothing
Set db = Nothing
strNewDBLoc = "C:\Data\MYDB_" & strNewVersion & ".mdb"
FileCopy strCurrentPath, strNewDBLoc
Set acc = New Access.Application
Set db = acc.DBEngine.OpenDatabase(strNewDBLoc, False, False)
acc .Visible = True
acc.Run "RefreshLinks"
acc.CloseCurrentDatabase
FileCopy strNewDBLoc, "\\network\MYDB_" & strNewVersion & ".mdb"
ExitGoLive:
set db = nothing
Set acc= Nothing
Exit Sub
GoLiveErr:
MsgBox ("Error with CopyAndLink procedure.")
Resume ExitGoLive
End Sub
----end code----