Sharde Databases

[Tina]

I am trying to have a particular database be a shared database. Within this database, I have a switchboard, from this switchboard the user is able to choose to look at tables or a report. The problem that I am running into is when one person is already accessing one or the other, then no one else is able to access the table or reports, why is this??? PLEASE HELP!!!!!!

[HiTechCoach]

I am trying to have a particular database be a shared database. Within this database, I have a switchboard, from this switchboard the user is able to choose to look at tables or a report. The problem that I am running into is when one person is already accessing one or the other, then no one else is able to access the table or reports, why is this??? PLEASE HELP!!!!!!

That is because to be multiple user, you must sp-lit the databases into a front end and back end. Each user must have there own (not shared) copy of the front end.

See:
Splitting your Access database into application and data

NOTE: It is generally not good to allow users into the tables. You should use a form to edit/view the tables. If the tables are related together, this will help to insure data integrity.

[Tina]

Okay, this was a huge help, i found other information about splitting the database. This is what it says...
But if you have already built a system with all your objects (including the tables) in one database file, it's a little more difficult to split your tables. One method is to created a duplicate copy of your database. In one version, your delete all objects, leaving only the tables. In the other version, you delete only the tables. Then you use the database file without the tables as a starting point and then link to all the tables in the table database. How is this to be done, i am not able to use the Linked Table Manager because it has no tables, how else do I connect or link the two databases together.

[HiTechCoach]

Okay, this was a huge help, i found other information about splitting the database. This is what it says...
But if you have already built a system with all your objects (including the tables) in one database file, it's a little more difficult to split your tables. One method is to created a duplicate copy of your database. In one version, your delete all objects, leaving only the tables. In the other version, you delete only the tables. Then you use the database file without the tables as a starting point and then link to all the tables in the table database. How is this to be done, i am not able to use the Linked Table Manager because it has no tables, how else do I connect or link the two databases together.

You can run the database splitter wizard and it will do it all for you.

If you manually split the database, in the front end (without any tables) you will need to create the lined tables using:

Access 2003 and prior:
File > Get External Date > Link Tables

You will browser for the back end database. Once selected just follow the steps of the wizard.

Access 2007/2010:
On the Ribbon click the External Data tab > Access > browse for the back end
Note: be sure to select the lower radio button for Link to ...

[HiTechCoach]

Additionally:

Tip: when linking to the back end, it is usually a good idea to use the UNC path and not a mapped drive letter.

\\servername\sharename\mybackend.accdb

or

\\servername\sharename\mybackend.mdb

This may you will not have to work about the workstation havind the correct drive letter mapped.

Also note that the back end database in the exanmple above is in at the root level of the share name. Avoid sub folder if possible.

[weekend00]

HiTechCoach, Would you please explain why we should Avoid sub folder if possible?

[Tina]

Okay, i have also tried using the database splitter, but it is not bringing over all the tables that are being used in the database. Some tables are already being used in a database that are being linked, and some of the other tables that are being used are excel sheets with data that we want to be able to change quickly and easliy to change the front end information. What other way can i split this database and then link the two together.

[HiTechCoach]

HiTechCoach, Would you please explain why we should Avoid sub folder if possible?

It is a Access performance thing. I find that it can have an effect on connecting to the back end. If the database is opened and closed often, then it can help.

[HiTechCoach]

Okay, i have also tried using the database splitter, but it is not bringing over all the tables that are being used in the database. Some tables are already being used in a database that are being linked, and some of the other tables that are being used are excel sheets with data that we want to be able to change quickly and easliy to change the front end information. What other way can i split this database and then link the two together.

The database splitter will only move local tables to the new back end. If you already have existing linked tables or excel files, the will still remain as linked in the front end.

Note: A single front end can link to multiple back ends.

This might help:
Splitting a access database, or how to run ms-access in a multi-user mode.

[Tina]

So would i still delete these tables and excel links from the front end and place them in the back end, or how will that work

[HiTechCoach]

So would i still delete these tables and excel links from the front end and place them in the back end, or how will that work

They belong in the front end.

Applications (Front end) have linked tables.

Normally your Data (back end) does not have any linked tables since it is the data.

In my opinion calling the front end the Application and the back end the Data makes their roles clearer.

[thekruser]

Additionally:

Tip: when linking to the back end, it is usually a good idea to use the UNC path and not a mapped drive letter.

\\servername\sharename\mybackend.accdb

or

\\servername\sharename\mybackend.mdb

This may you will not have to work about the workstation havind the correct drive letter mapped.

Also note that the back end database in the exanmple above is in at the root level of the share name. Avoid sub folder if possible.

How do I go about doing this? When I try to link the tables, I am not able to type in the physical address. The only option I have is selecting through drive mappings.

Thank you for your time and help. Have a nice day!

[thekruser]

I solved my own question. For those who are wondering the same, when linking the tables, physically type in the location in the "File name:" box instead of clicking through the drives above.