Joseph,
I know you've been a regular in this newsgroup for quite a while. Can you cite any Access MVP's,
who are also regulars in this newsgroup, as agreeing that sharing a database is an advisable
strategy? In my opinion, it's kind of like playing Russian roulette (no offense intended to our
Russian friends who might be reading this message). You may be lucky for quite a while, but
sooner or later your luck is bound to run out.
I always tell those who insist on sharing an entire database from a fileserver "Go ahead, but
don't come crying to me when your database becomes corrupted".
Tom
_____________________________________
My personal experience is that it does work well shared over a good, but
not exceptional network. However I do suggest that a good solid backup plan
be in place. I had the good luck to work with a LAN that mirrored, and
backed up nightly.
--
Joseph Meehan
26 + 6 = 1 It's Irish Math
_____________________________________
RSC,
Sharing a database over a network has been identified by the Access Team at Microsoft as the #1
cause of database corruption! This was stated by a Microsoft Access Program Manager at a seminar
that I attended approx. two years ago. You apparently have a very stable network and have been
very lucky. While doing so makes it much easier for a DBA to update the front-end, you are
bogging down your network by transferring all the bytes of information to define each query, form
and report. I'm guessing that you don't use temporary querydefs in your VBA code either.
There are techniques for minimizing the PITA associated with updating a front-end. One of the
most popular techniques mentioned in this newsgroup is Tony Toews AutoFE updater utility:
http://www.granite.ab.ca/access/autofe/details.htm
Another technique, completely self-contained in the .MDE front-end database code, is to use call
the procedure shown below. This particular version is called after you determine, using other
code, that the FE is not the current version. The beauty of this procedure is that the
apiCopyFile function allows you to overwrite a currently open file!
Note: This version currently includes a hard-coded path to the new FE database (ie. strSourceFile
= "\\server\share\NewFE.mde"). It would be fairly easy to store this value in a local table
instead. When time permits, I will be updating my procedure to include this change.
Tom
PS. Note to Wahab: I have a very good friend of 25 years who shares your name. Not a very
common name, at least in the USA.
'**************************Begin Code********************
Option Compare Database
Option Explicit
Declare Function apiCopyFile Lib "kernel32" Alias "CopyFileA" _
(ByVal lpExistingFileName As String, _
ByVal lpNewFileName As String, _
ByVal bFailIfExists As Long) As Long
Public Function UpdateFEVersion()
On Error GoTo ProcError
Dim strSourceFile As String
Dim strDestFile As String
Dim strAccessExePath As String
Dim lngResult As Long
'Create the source's path and file name.
strSourceFile = "\\server\share\NewFE.mde"
strDestFile = CurrentProject.FullName
'Determine path of current Access executable
strAccessExePath = SysCmd(acSysCmdAccessDir) & "MSAccess.exe "
'Verify that updated copy of FE database is available. If so, copy over existing file.
If Len(Dir(strSourceFile)) = 0 Then
MsgBox "The file:" & vbCrLf & Chr(34) & strSourceFile & Chr(34) & vbCrLf & vbCrLf & _
"is not valid file name. Please see your Administrator.", _
vbCritical, "Error Updating To New Version..."
GoTo ExitProc
Else 'copy the new version of app over the existing one.
lngResult = apiCopyFile(strSourceFile, strDestFile, False)
End If
'Modify strDestFile slightly so that it can be used with the Shell function
strDestFile = """" & strDestFile & """"
MsgBox "Please wait, after clicking OK, for the application to restart.", _
vbInformation, "Application Update Successful..."
'Load new version, then close old one.
Shell strAccessExePath & strDestFile & "", vbMaximizedFocus
DoCmd.Quit
ExitProc:
Exit Function
ProcError:
Select Case Err.Number
Case 52 ' Bad file name or number
MsgBox "The file:" & vbCrLf & Chr(34) & strSourceFile & Chr(34) & vbCrLf & vbCrLf & _
"is not valid file name. Please see your Administrator.", _
vbCritical, "Error Updating To New Version..."
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, , _
"Error in UpdateFEVersion procedure..."
End Select
Resume ExitProc
End Function
'**************************End Code**********************
_____________________________________
Words of advice thru experience.
You DONT have to put the "front end" on each persons PC.
Doing so makes it a real PITA when trying to update or
upgrade the front end. Put the front end (the split mdb
or mde preferred with the forms, queries, macros,) on the
network as well. Ive had 30-40 users on the same MDE on
the network accessing it simultaneously and have never
had a problem. The only thing that needs to reside on
the individuals PC is the MS Access program itself.
Make note that users will need to have read/write/create
access rights to the network folders which contain the
front end and back end(s)(MDB or MDE with the table
structures).
I would also invoke user level security and create a
workgroup file.
good luck
_____________________________________
For multi-user use, the first thing you need to do is to split your database
into 2 parts. The first part (front-end) will contain everything except the
tables and reside on each workstation. The second part (back-end) will
contain only the data tables and reside on a server. The front-end has
linked tables to the back-end. There is a wizard to help you do this. Look
in Tools ... Database Utilities ... Database Splitter.
Make sure you use quality network components, as most database failures come
from poor and dropped connections. If you only have a few users, and
security is of little concern, you are basically ready to go forth and
conquer the world.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
_____________________________________
Hi
I have created small database for sales, its working fine. Now I want to
upload it on my network for sharing the same so 2 users can use same time
and work on that. Do I required Microsoft Access network version? if not
where I will save my file in server on in one of the user's pc?
Any site where I will get more idea for multiuser?
Thanks and regards
Wahab
