By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,847 Members | 2,261 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,847 IT Pros & Developers. It's quick & easy.

Interactivity between 2 instances of Access

P: n/a
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.
'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.

Public Sub CopyAndLink(strCurrentPath As String, strNewVersion As
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.
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"
FileCopy strNewDBLoc, "\\network\MYDB_" & strNewVersion & ".mdb"

set db = nothing
Set acc= Nothing
Exit Sub

MsgBox ("Error with CopyAndLink procedure.")
Resume ExitGoLive
End Sub
----end code----

May 2 '06 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.