Solved

Can a front end be shared by more than one user?

Posted on 2011-09-21
21
295 Views
Last Modified: 2012-05-12
I have an access front end with no local tables (other than a couple look ups).  
It uses a couple of linked SQL server tables and quite a bit SQL Server Pass through queries which have parameters passed to them at run time (i.e. order numbers, etc.)

I was planning on still giving every user their own copy of the front end, but then I read something (somewhere) that it may not be necessary, that something as simple as this could be shared?

I am thinking no, but wanted to ask the question here for more clarification.

The application detects the current windows username, and utilizes that to authenticate the user.  In some passthroughs, the user name is passed as a parameter to pull only specific data for that user.

Having said that, my understanding is that this could not be used as a multi-user front end.  Correct?

The reason for the question is that our IT group would rather have a single user link on our intranet that when clicked, runs the front end.  I am thinking this will not work, and each user needs a copy of the front end either locally on their computer, or residing in their own personal network share folder.

 

0
Comment
Question by:snyperj
  • 6
  • 4
  • 3
  • +5
21 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36573356
It is not recommended.  In a multi-user environment, each user should have their own front-end.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36573396
<IT group would rather have a single user link on our intranet that when clicked..>

If you are consistent in what folder you place your applications on user's machines, this can work.

eg: if all of your databases reside in a dbApplications folder on the Users' machines, the link would be:

C:\dbApplications\YourDB.accdb
0
 
LVL 3

Expert Comment

by:paradox_cla
ID: 36573423
I used Access as the database accessed by many users without problems.
A problem may occur with increasing number of users.
Access used together with a share works 100%.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36573447
Paradox,

If you are sharing a front-end you are asking for corruption and other problems.  Each user in a multi-user environment should have their own copy of the front-end.
0
 
LVL 57
ID: 36573460
<<I was planning on still giving every user their own copy of the front end, but then I read something (somewhere) that it may not be necessary, that something as simple as this could be shared?>>

  Yes it can be shared.  Most however feel it's better to give each user their own copy.  Reasons?

1. Less corruption - I've never seen any hard evidence that sharing a FE increases the chance of corruption, but that is the general consensus.

2. Better performance - this is becoming less of an issue with networks getting faster, but it does seem silly to be sending everything over the wire when nothing in a FE changes.

3. Temp tables - Often, especially with SQL BE's, data needs to be pulled into a temp table and reported on for performance reasons, but when users share a FE, this becomes a problem and most developers don't plan for it

Jim.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 36573825
>   Yes it can be shared.  Most however feel it's better to give each user their own copy.  Reasons?

They don't know how to share a FE.

But Jim's three points are true.
However:

2. On a modern Gigabit LAN the performance increase by having a local copy of a modest FE like yours is hard to measure.

1. and 3. The simple method to prevent this is to mark the FE file itself as Read-Only. This will, by definition, eliminate any corruption, and temp data controlled by Access are forced to the user's local storage. Of course, if your app itself does create temp tables, these have to be placed in a separate temp database file.

We have used this method - both in-house and at clients - for years (since Access 2.0!) with zero issues.

/gustav
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36574084
Gustav,

When you use this technique, I assume that you then use ADO or SQL queries that contain an IN clause (example below) to access the local temp tables?

SELECT * FROM tempTable IN 'C:\Windows\Temp\myAppTemp.mdb'



0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 36574206
No, though it would work.

But relinking is, of course, not an option of a R-O FE. The admin must set the linking, then apply the R-O flag. This is the method we use.

/gustav

0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 36574649
Possible, yes
Recommended, no

IF your network permits scripting, this is how I pull new copies of the front-end down
'declare an object to be a filesystem object
Public fs
'declare an object to be a MS Access application
Public acApp
 'declare a wscript shell
Public WshShell

Main()

'-------------------------------------------------------------------------------------------------------------------------------------------
Sub Main()
dim MyConn
dim MyConn1
Dim LocalSplit
Dim ServerSplit
Dim x

OpenCmdWindow()


Set fs = CreateObject("Scripting.FileSystemObject")
fs.copyfile "M:\Prod\PhotoResize700O.exe", "c:\Prod\PhotoResize700O.exe", -1
CheckDeployment()

If fs.FileExists("m:\deploy\ver.txt") then
    Set MyConn= fs.OpenTextFile("m:\deploy\ver.txt",1, true)
    ServerSplit = Split(MyConn.readall, ".")
    MyConn.Close
end if

If fs.FileExists("c:\prod\ver.txt") then
    Set MyConn1= fs.OpenTextFile("c:\prod\ver.txt",1, true)
    LocalSplit = Split(MyConn1.readall, ".")
    MyConn1.Close
else
    LocalSplit =Split("0.0.0.0", ".")
end if

set myconn = nothing
set myconn1 = nothing

If UBound(LocalSplit) <> UBound(ServerSplit) Then
        Call CopyUpdate()
        FireDB()
        Exit Sub
End If

For x = 0 To UBound(LocalSplit)
    If cint(ServerSplit(x)) > cint(LocalSplit(x)) Then
        Call CopyUpdate()
        FireDB()
        Exit Sub
    End If
Next

if fs.FileExists("c:\prod\TI_Prog_Backup.mdb") then
    Call KillBad()
    Call CopyUpdate()
    FireDB()
    Exit Sub
end if


FireDB()

end sub

'------------------------------------------------------------------------------------
private Sub CopyUpdate()
Dim BuiltPath
Dim userpath

userpath= "m:\deploy\TI_Prog.mdb"
BuiltPath = "c:\prod"
TempPath = "c:\prod\temp"

If fs.FolderExists(BuiltPath) = False Then
    fs.CreateFolder BuiltPath
    fs.copyfile "m:\prod\mousehook.dll", "c:\prod\mousehook.dll", -1
    fs.copyfile "m:\prod\rapi.dll", "c:\prod\rapi.dll", -1
End If

fs.copyfile userpath, "c:\prod\TI_Prog.mdb", -1
fs.copyfile "m:\deploy\ver.txt", "c:\prod\ver.txt", -1
fs.copyfile "m:\deploy\ver.txt", "c:\prod\RemoteCE.tlb", -1


If fs.FolderExists(TempPath) = False Then
    fs.CreateFolder TempPath
End if

End Sub
'-----------------------------------------------------------------------------------------

Function CheckDeployment() 
Dim BuiltPath
BuiltPath = "c:\prod\TI_Prog.mdb"

If fs.FileExists(BuiltPath) = False Then
    Call CopyUpdate()
End If



End Function
'---------------------------------------------------------------------------------------
sub FireDB

CloseCmdWindow()
dim PathToMDE
PathToMDE = "c:\prod\TI_Prog.mdb"

Set acApp=CreateObject("Access.Application.11")
acApp.usercontrol=true
acApp.DoCmd.RunCommand (10) 'accmdappmaximize
acApp.DoCmd.Hourglass True


acApp.AutomationSecurity=1  ' low
acApp.OpenCurrentDatabase PathToMDE
acApp.DoCmd.Hourglass false
Set acApp=Nothing
Set fs = nothing

end sub

'-------------------------------------------------------------
sub OpenCmdWindow()
set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.Run "cmd.exe"
WScript.Sleep 10
WshShell.AppActivate "c:\windows\system32\cmd.exe"
WScript.Sleep 100
WshShell.SendKeys "Starting....wait patiently...."


end sub

 '------------------------------------------------------------
sub CloseCmdWindow()
dim oshell
dim oWmg
dim strWndprs
dim objQResult

WScript.Sleep 100

Set oShell = CreateObject("WScript.Shell") 
Set oWmg = GetObject("winmgmts:") 

strWndprs = "select * from Win32_Process where name='cmd.exe'" 
Set objQResult = oWmg.Execquery(strWndprs) 

For Each objProcess In objQResult 

intReturn = objProcess.Terminate(1) 

Next



'WshShell.AppActivate "c:\windows\system32\cmd.exe"
'WScript.Sleep 100
'WshShell.SendKeys "{enter}"
' WshShell.SendKeys "exit"
' WshShell.SendKeys "{enter}"
end sub

'------------------------------------------------------------
Private sub KillBad()

If fs.FileExists("c:\prod\TI_Prog_Backup.mdb") Then
    fs.DeleteFile "c:\prod\TI_Prog_Backup.mdb", True
end if

If fs.FileExists("c:\prod\TI_Prog.mdb") Then
    fs.DeleteFile "c:\prod\TI_Prog.mdb", True
end if

If fs.FileExists("c:\prod\TI_Prog.ldb") Then
    fs.DeleteFile "c:\prod\TI_Prog.ldb", True
end if



end sub

Open in new window

and this is how I push new dev copies to deployment
 
'declare an object to be a filesystem object
Public fs
'declare an object to be a MS Access application
Public acApp

Main()



Sub Main()
call UpdateDBversion
Set acApp=Nothing
fs.CopyFile "m:\dev\TI_prog.mdb", "m:\deploy\"
Set fs = nothing
msgbox "Done"
end sub
'-------------------------------------------------------------------------------------------------------------------------------------------
sub UpdateDBversion

dim myconn
dim db
dim rs
Dim LocalSplit
Dim splitstring
dim PathToMDE
dim x
dim SQL
PathToMDE = "m:\dev\TI_Prog.mdb"

Set MyConn = CreateObject("ADODB.Connection")
MyConn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TI_Data;Data Source=REESRDP\SQLEXPRESS05" 
'MyConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=m:\dev\TI_Prog.mdb"
Set RS = MyConn.Execute("Select versionnumber from tblVersion;")


splitstring = rs.fields("VersionNumber")
LocalSplit = split(splitstring,".")
x = UBound(LocalSplit)
LocalSplit(x) = LocalSplit(x) +1

MyConn.Execute("UPDATE tblVersion SET tblVersion.VersionNumber = " & chr(39) & join(localsplit,".") & chr(39))
MyConn.close

call UpdateVertxt(join(localsplit,"."))

end sub

'---------------------------------------------------------------------
public sub UpdateVertxt(newVer)
Set fs = CreateObject("Scripting.FileSystemObject")
dim Mytextfile

If fs.FileExists("m:\deploy\ver.txt") then
    fs.deleteFile("m:\deploy\ver.txt" )
    set mytextfile = fs.OpenTextFile("m:\deploy\ver.txt",8, true)
    mytextfile.writeline(newver)
    mytextfile.Close
end if

end sub

Open in new window


Benefits:
1) I can push changes to the front-end WITHOUT making the users exit, as they get change the next time they open
2) The script cleans up hung and corrupted FE's automatically
3) A working copy of the FE is on every desktop in case of disaster\
4) A gold copy is in the deploy folder
5) Double-click deployment

IF scripting is permitted, you distribute a single unchanging script file to each desktop.
Zero desktop maintenance after that.
ver.txt contains version numbers that are used in the deployment check

Tony Toews also has a front-end deployer packaged as an .exe for sale
0
 
LVL 57
ID: 36574794

 Batch files work to...as well as VB6 "Launcher" programs.

Jim.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 26

Expert Comment

by:Nick67
ID: 36574820
I used to use a shared FE--but I am awfully glad I got away from that.
Deployed local front ends are just a lot less hassle.
You've got an IT deparment--they may be able to publish the FE through AD/GPO/Intellimirror too
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 36574865
> Batch files work to ..

Indeed:
@echo off
echo Fetching Power Application ...
copy f:\applications\distribution\powerapp.mdb c:\applications\localapps

Open in new window


. And it is much simpler just to copy the FE anytime the user logs into Windows. No version check, no bloat, no nothing.

/gustav
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36574962
My users rarely if ever logoff.
And from time to time I can rev three, four, five times in a day
The front-end is also meaty--80 MB--so if EVERYBODY hits it when it's not needful there is lag.
We had Vista and SBS 2003.  The copy was 45 seconds or so on gigabit backbone.
Vista left as soon as possible :0 but not nearly soon enough :(
Even with Win7 and SBS 2008 it's 12 to 15 seconds to pull a new frontend.
So it happens when I rev, or when the script can see that the user crashed the frontend on the last go around.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 36575041
> My users rarely if ever logoff.

Well, power is so expensive here that workstations are closed down during off-hours.

> And from time to time I can rev three, four, five times in a day

?!

/gustav


0
 
LVL 26

Expert Comment

by:Nick67
ID: 36575093
<?!>
Lots of little changes.
'Can you add a phrase to a combo box and have it add some records'
'Can you change this report just a little'
0
 
LVL 9
ID: 36580811
I don't think anyone has mentioned another reason not to share the FE.  With SQL Server back-ends, there are advantages to modifying persistent passthrough queries.  This won't work for a shared FE.

I mention this technique near the end of "Best of Both Worlds", a PowerPoint presentation on techniques for using Access as a client-server front-end to SQL Server databases.

http://www.JStreetTech.com/downloads

Cheers,
Armen
0
 

Author Comment

by:snyperj
ID: 36582378
Nick your code is interesting and I like the concept, but what triggers it?    Where does this code reside?
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36582460
They are vbscript files
Notepad files renamed from .txt to .vbs
Typically, I create shortcuts to them, and tweak the icon picture to be Access.
Copy the first snippet.
Paste it into notepad.
Save as Deploy.vbs
Copy the second snippet
Paste it into notepad
Save as CopyAndDeploy.vbs

My dev folder is m:/dev
My deploy folder is m:/deploy
The local FE location is c:\prod
The FE is TI_Prog.mdb

Some of the script is concerned with ensuring that the ancillary files are also in place
mousehook.dll
rapi.dll
RemoteCE.tlb
PhotoResize700O.exe
But these probably don't apply to you
Ver.txt needs to be in the deploy folder, start with 1.0.0.0 on a single line in the file
Copy and deploy increments the last digit
ver.txt gets copied down to the local FE locations

Modify as necessary
0
 

Author Comment

by:snyperj
ID: 36583240
Will it work in mixed environments? (Access 2000, 2007,2010)
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36583657
Being small enough I cheated and had 3, one for each version
Then I learned that
Set acApp=CreateObject("Access.Application")
is version agnostic

I copy and pasted from the original
Set acApp=CreateObject("Access.Application.11")

Take the .11 off and it should play
0
 

Author Comment

by:snyperj
ID: 36583768
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now