multisuer Database

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
Wahab said:
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
Click to expand...

I will add a little to the good advice from Arvin.

Remember that both user's computers will require a comparable version of
Access. There is no "network" version of Access.

You can put your copy of the database on the network drive without
splitting it and with just two users you may not have any problems, but
splitting it is a much better idea and avoids possible user and speed
problems.
 
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
 
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
 
Tom said:
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
Click to expand...

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,

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
 
Tom said:
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".
Click to expand...

I carefully stated my statement with "My personal experience is .. " I
believe that provides a clear limit on how I expected someone to view my
statement to follow. I also suggested a good solid backup plan. What more
would you expect.

I understand that some people have had problems with corrupted databases
in shared environments. I also understand that some people have problems
with corrupted single use databases.

I don't believe the situation calls for total fear that a shared
database is sure death, nor do I believe that blissful ignorance of the
potential problems should be ignored in any mission critical situation. I
still suggest my approach strikes a good balance of the two.
 
The fact that you haven't AS YET corrupted your data is pure luck. It may
never happen, and you will count yourself as quite the lucky person. Not
splitting a database is an invitation to corruption. Since you choose to
remain anonymous, I can't really guage the level of your experience, but I
have about 12 years of Access experience now, almost 10 of which have been
in professional Access development. Knowing my name, you can easily research
that on Google.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
It is very good that I learn how to split the Access database.

After the split of the database, if I need to create a query, can I just
save the front end database from my workstation and then copy it to other
workstations or do I need to do the same process in every workstation that
have the front end database.

Actually, I have my mdb stored in the server now. I have the problem that
only one user can access the mdb at the same time. I am going to do the
splitting as advised by this group members. Can I just copy the mdb from the
server to all workstation just after the split of database or any additional
task is required.

Your valuable advice is appreciated.
 
Stephen

Re-read Tom's response -- he included a reference to an Auto-Update function
that would make it easier to keep the individual copies of the front-end
installed on each user PC in sync with a "master" copy.
 
Back
Top