I am networking for the first time. I will have four people using the same database. I understand the importance of having primary keys in all tables, but is this enough to have a true multi-user database? Do I need to split the database in-order for them to use the table at the same time? I prefer not to deal with splitting, but I don't want to have problems later because I didn't. What do you think? Thank you!
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Shaun E on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Can two users enter the same table if DB is not split 1
- Thread starter MariaL
- Start date
-
1
- #2
LittleSmudge
Programmer
You don't NEED to split the database.
In fact you don't NEED to have primary keys in all tables either - but that's regressing too far.
If you have the one Database.mdb file on a server somehere that all of the users can see via the network then they can each open the same database.
If you look at the folder in which you put the database you'll see that a Database.ldb file is created as soon as the first person opens the database and it will persist until the last person exits from it. The xxx.ldb file is what Access uses to manage who is 'in' the database.
You'll get problems if users start trying to simultaniously edit records in the same table. ( This will NOT be improved by splitting ).
What you will find useful is that splitting that database means that you can keep on working on the enhancements of ( changes to ) the front end Forms, Reports etc. while users are actively working in the database - You can ONLY do this if the database is split.
You'll also find that having a copy of the Front end locally on each user's C:\ drive will dramatically reduce the amount of network traffic - providing system speed responce improvements to all users.
BTW Splitting is EASY
Take your working database and make two copies in the same folder
Call the first dbNameBE.mdb
call the seconf dbNameFE.mdb
( replace dbName with the name of your database )
Open the dbNameBE in design view and delete all Queries, Forms, Reports, Modules. Then Save and Close
Open the dbNameFE in design view and delete all of the tables.
Then use File, Get External Data, Link Tables
(CRITICAL BIT: In the Link Tables dialog box make sure that you fine the folder using "Network Neighborhood" so that you get the full \\Server\Path\File rather than the mapped Letter:\Path\File format.
This will ensure that you can copy the dbNameFE to any machine on the network and it will still work [/Critical Bit]
Select the dbName.BE and link in all of the tables in the Back end to the Front end.
Save & Close the front end.
Open dbNameFE and test
'ope-that-'elps.
G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.
In fact you don't NEED to have primary keys in all tables either - but that's regressing too far.
If you have the one Database.mdb file on a server somehere that all of the users can see via the network then they can each open the same database.
If you look at the folder in which you put the database you'll see that a Database.ldb file is created as soon as the first person opens the database and it will persist until the last person exits from it. The xxx.ldb file is what Access uses to manage who is 'in' the database.
You'll get problems if users start trying to simultaniously edit records in the same table. ( This will NOT be improved by splitting ).
What you will find useful is that splitting that database means that you can keep on working on the enhancements of ( changes to ) the front end Forms, Reports etc. while users are actively working in the database - You can ONLY do this if the database is split.
You'll also find that having a copy of the Front end locally on each user's C:\ drive will dramatically reduce the amount of network traffic - providing system speed responce improvements to all users.
BTW Splitting is EASY
Take your working database and make two copies in the same folder
Call the first dbNameBE.mdb
call the seconf dbNameFE.mdb
( replace dbName with the name of your database )
Open the dbNameBE in design view and delete all Queries, Forms, Reports, Modules. Then Save and Close
Open the dbNameFE in design view and delete all of the tables.
Then use File, Get External Data, Link Tables
(CRITICAL BIT: In the Link Tables dialog box make sure that you fine the folder using "Network Neighborhood" so that you get the full \\Server\Path\File rather than the mapped Letter:\Path\File format.
This will ensure that you can copy the dbNameFE to any machine on the network and it will still work [/Critical Bit]
Select the dbName.BE and link in all of the tables in the Back end to the Front end.
Save & Close the front end.
Open dbNameFE and test
'ope-that-'elps.
G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.
RacerGirl117
Technical User
Little Smudge,
When you say "copy of the Front end locally on each user's C:\ drive" do you mean to actually copy the database.mdb file from the network folder to the users C: drive?
Jessica Morgan
Fire Fighter Sales & Service Co.
Pittsburgh, PA
When you say "copy of the Front end locally on each user's C:\ drive" do you mean to actually copy the database.mdb file from the network folder to the users C: drive?
Jessica Morgan
Fire Fighter Sales & Service Co.
Pittsburgh, PA
If you have people trying to edit records in the same table at the same time, you could go to unbound forms where a person opens a connection to the tabel, grabs some records and drops the connection. The fields of the grabbed records are moved to the corresponding fields in a form. Changes are made and saved back to the recordset. When the save button is pushed, a connection is opened to the table and the modified records are sent back to update the records in the table (or add or delete records as the case may be). The only time the that the table is actually in use is when someone is either grabbing some records or sending them back to the table.
The problem is, coding it gets a little complex, although, once I was walked through it a few times, it really wasn't too bad. The January 2002 issue of Inside Microsoft Access had a great article on how to do it. Now I'm trying to figure out how to do it with SQL Server tables.
The problem is, coding it gets a little complex, although, once I was walked through it a few times, it really wasn't too bad. The January 2002 issue of Inside Microsoft Access had a great article on how to do it. Now I'm trying to figure out how to do it with SQL Server tables.
You don't say which version of Access you're using. From what I've read here on Tek-Tips, letting multiple users use an unsplit database on the network is opening the door for database corruption problems.
I always use split databases myself, so I don't have experience with this. I don't know the parameters such as whether it's less of a problem with later versions, or whether there are precautions you can take to make it less dangerous. If I were you, though, I'd consider doing a search of the Access forums for the words "network" and "corruption".
Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein
I always use split databases myself, so I don't have experience with this. I don't know the parameters such as whether it's less of a problem with later versions, or whether there are precautions you can take to make it less dangerous. If I were you, though, I'd consider doing a search of the Access forums for the words "network" and "corruption".
Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein
LittleSmudge
Programmer
When you say "copy of the Front end locally on each user's C:\ drive" do you mean to actually copy the database.mdb file from the network folder to the users C: drive?
What I'm talking about there is once you have gone to the point of splitting the db into Front and Back ends THEN you leave the dbNameBE on the Network drive and ( having link the dbNameFE to the back end using \\Server\path\file format ) you then put a copy of dbNameFE onto each user's machine ( C:\ drive. )
This means that when the user opens a Form the inforation about the form layout, colours, bitmap images etc travel from their C:\ drive to the processor rather than having to be fetched from the network drive along the network cabling.
The only info travelling on the network is then the DATA itself.
'ope-that-'elps.
G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.
What I'm talking about there is once you have gone to the point of splitting the db into Front and Back ends THEN you leave the dbNameBE on the Network drive and ( having link the dbNameFE to the back end using \\Server\path\file format ) you then put a copy of dbNameFE onto each user's machine ( C:\ drive. )
This means that when the user opens a Form the inforation about the form layout, colours, bitmap images etc travel from their C:\ drive to the processor rather than having to be fetched from the network drive along the network cabling.
The only info travelling on the network is then the DATA itself.
'ope-that-'elps.
G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.
RacerGirl117
Technical User
Little Smudge,
I mapped all the linked tables using your example above but it didn't help as far as the speed is concerned.
Jessica Morgan
Fire Fighter Sales & Service Co.
Pittsburgh, PA
I mapped all the linked tables using your example above but it didn't help as far as the speed is concerned.
Jessica Morgan
Fire Fighter Sales & Service Co.
Pittsburgh, PA
LittleSmudge
Programmer
Speed of what ?
Speed of the overall network ?
Speed that the forms open ?
Speed that the db processes data ?
Each will have many causes unto themselves and splitting the database FB & BE CAN affect some of them if other factors are affecting the situation as well.
Explain more.
G
Speed of the overall network ?
Speed that the forms open ?
Speed that the db processes data ?
Each will have many causes unto themselves and splitting the database FB & BE CAN affect some of them if other factors are affecting the situation as well.
Explain more.
G
RacerGirl117
Technical User
Speed of the database opening, speed of any given form opening. Data processing doesn't seem to have slowed much, if at all although I haven't had time to truly test it.
Thanks,
Jessica Morgan
Fire Fighter Sales & Service Co.
Pittsburgh, PA
Thanks,
Jessica Morgan
Fire Fighter Sales & Service Co.
Pittsburgh, PA
LittleSmudge
Programmer
Well it's nothing to do with splitting the database, but if you have trouble with Forms opening 'slowly' - especially if they are complex forms with a lot of controls on them then this can often help :-
Tools Menu, Options, General tab
Remove the ticks from the three options in the top right corner marked as 'Name AutoCorrect'
This gives Access a lot less work to do when it opens each form.
Replace the ticks when you want to do Development work.
'ope-that-'elps.
G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.
Tools Menu, Options, General tab
Remove the ticks from the three options in the top right corner marked as 'Name AutoCorrect'
This gives Access a lot less work to do when it opens each form.
Replace the ticks when you want to do Development work.
'ope-that-'elps.
G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.
- Status
Similar threads
- Locked
- Question
- Locked
- Question