Application deployment with Access 2007

[Jac Tremblay]

Hi,
I use Access 2007 and my client does not have Access at all.
I want to install the application I developped on his computer with the
Access 2007 runtime.
My application is split in two parts: be and fe. The fe part is linked to
the tables in the be part on my pc.
If I create an ACCDE on my station, the fe will be linked to the be part on
my station. If I install this application (.accde) on the client computer,
the fe will not be connected to the right be.
And since the client does not have Access, I cannot perform these tasks on
his computer.
What should I do?

Thanks.

 

[Klatuu]

You will need to provide the user with the ability to relink to the back end.
Here is a routine I have used in 2000 and 2003 to do that. There is no
reason it should not also work in 2007.

Private Function ReLink(strNewPath As String) As Boolean
Dim dbs As Database
Dim tdf As TableDef
Dim intCount As Integer
Dim frmCurrentForm As Form

DoCmd.Hourglass True
On Error GoTo ErrLinkUpExit
Me.lblMsg.Visible = True
Me.cmdOK.Enabled = False

Set dbs = CurrentDb

For intCount = 0 To dbs.TableDefs.Count - 1
Set tdf = dbs.TableDefs(intCount)
If tdf.Connect <> "" Then
Me.lblMsg.Caption = "Refreshing " & tdf.NAME
DoEvents
tdf.Connect = ";DATABASE=" & strNewPath
tdf.RefreshLink
End If ' tdf.Connect <> ""
Next intCount

Set dbs = Nothing
Set tdf = Nothing

DoCmd.Hourglass False
Me.lblMsg.Caption = "All Links were refreshed!"
ReLink = True
Me.cmdOK.Enabled = True
Exit Function

ErrLinkUpExit:
DoCmd.Hourglass False

Select Case Err
Case 3031 ' Password Protected
Me.lblMsg.Caption = "Back End '" & strNewPath & "'" & " is
password protected"
Case 3011 ' Table missing
DoCmd.Hourglass False
Me.lblMsg.Caption = "Back End does not contain required table '"
& tdf.SourceTableName & "'"
Case 3024 ' Back End not found
Me.lblMsg.Caption = "Back End Database '" & strNewPath & "'" & "
Not Found"
Case 3051 ' Access Denied
Me.lblMsg.Caption = "Access to '" & strNewPath & "' Denied" &
vbCrLf & _
"May be Network Security or Read Only Database"
Case 3027 ' Read Only
Me.lblMsg.Caption = "Back End '" & strNewPath & "'" & " is Read
Only"
Case 3044 ' Invalid Path
Me.lblMsg.Caption = strNewPath & " Is Not a Valid Path"
Case 3265
Me.lblMsg.Caption = "Table '" & tdf.NAME & "'" & _
" Not Found in ' " & strNewPath & "'"
Case 3321 ' Nothing Entered
Me.lblMsg.Caption = "No Database Name Entered"
Case Else
Me.lblMsg.Caption = "Uncaptured Error " & str(Err) &
Err.DESCRIPTION
End Select

Set tdf = Nothing
ReLink = False

End Function

Here is a link to an API that will allow the user to use the file open
dialog to navigate to the back end:

http://www.mvps.org/access/api/api0001.htm

You can use that to get the file name and path and you pass that to the
relink code.

 

[Jac Tremblay]

Hi Dave,
Thank you for this swift response. I will have to try and test this solution
which sounds good at first glance.
Later, I will post the results of my tests.
Thanks again.

 

[Klatuu]

Okay, let me know how it works out.

 

[dch3]

We're about to deploy a new app to users that aren't neccessarily that tech
savy. Since we know that we'll have frequent updates, we've written a
vbscript that will check a custom database property on the machines local
version and compare it to the custom property on the server copy. If they
don't match, the script will automatically copy the new front end version to
the local machine and then launch the app. The idea is to have the users
*ALWAYS* use the script to open the front end thereby ensuring that they have
the most recent version. (One of my more brilliant solutions.)

If you're not aware of custom database properties, they all you to create
and retreive a user defined property for a database. In this case, we've
named the property "Current Front End Version". Previously, I've used a
hidden table to store the value, however as soon as I learned about the
custom DB properties jumped over to that bandwagon as the table is much
easily accessible to the user. I can send you the code if you'd like.

Just call me Gort.

 

[Klatuu]

Thanks, but I am aware of user defined application properties and have used
them.

 

[Klitsgras]

Hi Klatuu,
This sounds like the solution I have been looking for, but unfortunately I
use MS Access wizard/menu functionality all the time and do not know where to
go to add the script below. Could you please assist me?