Interactivity between2 instances of Access

[Anonymity25]

I think I posted this question in the wrong group... Sorry for the
re-post:

Hello,
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----

TIA

 

[Albert D.Kallal]

Well, it seems to me that you should pre-link the new mde?

The steps usually are

re-link the mdb to the production back end
create the mde. This mde is now pre-linked, and does not need to be linked
at startup

If you don't plan to have the mde pre-linked (or, cannot have the location
pre-linked), then your startup code in the application needs to test/check
for this situation anyway. I mean, your startup code has to test for this
anyway, and you likely need some re-linking code in the application. So, I
fail to see why you are trying to pre-link with your as it is now. Simply
use the linked table manger, and then create the mde..and you are done (at
least for the linking part).

I want to:
Copy the FE from my desktop, where I work w/ the local copy, to C:\Data

Well, if yoiu pre-link, then all you have to do is copy.

acc.Run "CopyAndLink", CurrentProject.FullName, GetNewVersion

Current project is now going to be you golive.mdb..not the actually mdb, so,
this don't make sense.. Should not the above be passing the name of the mdb
file you want to re-link? As I mentioned, this seems confusing anyway, and
why not just pre-link BEFORE you make the mde?

 

[Anonymity25]

Hi Albert,

Thanks for the response. Let me clear up a couple things that I
probably didn't explain well.

1. As of right now I have 5 users (including myself) each w/ a shortcut
on their desktop that opens a local copy of MYDB_FE on their local PC
(C:\Data.). This FE uses linked tables to connect to MYDB_BE on a
shared network drive. I have this same setup for myself so I can access
the live BE.
2. On my computer only, I have a copy of the FE on my desktop which
links to a local copy of the BE. This way I can tool around w/ the
interface etc w/o disturbing real data.
3. After my local copy, with the new changes, is fully debugged, I am
ready to distribute it to my users. In order to do that I need to do a
few things:
a: Copy my local-desktop FE copy somewhere else (b/c I am going to
now link the tables to the live BE and I still want my local FE linked
to my local BE to continue improving the interface.)
b: Link the linked-tables in the newly copied FE to the live BE
c: Copy the newly-linked file to the network drive for download.

Hopefully this is clear; although I think it might sound confusing...

re-link the mdb to the production back end
create the mde. This mde is now pre-linked, and does not need to be linked
at startup

I am not converting to an MDE.... Your idea of 'pre-linking' is
exactly what I do (if I understand you correctly) in step b above.

Current project is now going to be you golive.mdb..not the actually mdb, so,
this don't make sense..

Looking back, I def. wasn't clear here... That code is run within a
proc. in the FE so that code is fine (current project is the FE);

Essentially I want:
1. FE Procedure to open GoLive.mdb and pass the FE path and new version
#.
2. GoLive.mdb to close the FE, copy to a new location w/ new version #,
open newly-copied FE, run RefreshLink procedure, close newly-copied FE,
copy newly-copied FE to network.

Sorry for the book....

 

[Albert D.Kallal]

Looking back, I def. wasn't clear here... That code is run within a
proc. in the FE so that code is fine (current project is the FE);

Essentially I want:
1. FE Procedure to open GoLive.mdb and pass the FE path and new version
#.
2. GoLive.mdb to close the FE, copy to a new location w/ new version #,
open newly-copied FE, run RefreshLink procedure, close newly-copied FE,
copy newly-copied FE to network.

Sorry for the book....

Ok...sorry for not getting back...my newsreader missed this....

And, your explain was ok...I just miss understand it..(my fault).

Essentially I want:
1. FE Procedure to open GoLive.mdb and pass the FE path and new version

ok...above sounds good...

2. GoLive.mdb to close the FE, copy to a new location w/ new version #,
open newly-copied FE, run RefreshLink procedure, close newly-copied FE,
copy newly-copied FE to network.

No, don't have golive close the FE. You are MUCH better to have your front
end modify as per your current step one, and then shell to the golive, and
have the FE do a quit FROM THE FE.....

(it is more difficult to have one application shut down and close the
other...
so, we don't do this - code running, or any type of dialog prompt would
prevent this anyway.....)

To launch the golive from the front end...go

q = """"

strCurrentDir = CurrentProject.path & "\"

' path to msaccess is required here
strShellProg = q & SysCmd(acSysCmdAccessDir) & "msaccess.exe" & q & " "

' path to current dir...and upgrade...
strShellProg = strShellProg & q & strCurrentDir & "GoLive.mdb"

Shell strShellProg, vbNormalFocus

application.Quit


note that you need to have your golive wait a few seconds while the above
shuts down...

'lets wait for 4 seconds for other copy of ms-access to exit...

For i = 1 To 20
DoEvents
Call sapiSleep(100)
Next i

The above uses the api sleep as to not waste proessing, so, the api in a
standard module is:


Public Declare Sub sapiSleep Lib "kernel32" _
Alias "Sleep" _
(ByVal dwMilliseconds As Long)


You don't likey need 4 seconds...but at least 1, or 2 should be in the
loop....

Another way would be wait until the .ldb locking file disappears....

So, as far as I can tell, you

a) do not want to copy any file that is currently open....too risky
b) you need to be sure that the FE is exited fully before your
golive code starts running, or at least have the golive code wait until the
FE is fully shutdown....


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal