Solved

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

Posted on 2011-09-21
21
304 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
  • +5
21 Comments
 
LVL 48

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 48

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 58
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 50

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 48

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 50

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 58
ID: 36574794

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

Jim.
0
 
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 50

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 50

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

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

724 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