Help please????

[AdamRSL]

Hello and please help!!
I work in an office with a server and 5 workstations. All have their own access 2007.
I created a database for us all to use and it is located on the shared drive of the server. Everyone can access the DB at the same time and amend different records at the same time no problem there!!
However, on each record there is an attachment (our job files) now if user 1 has a record open and the attachement open (a copy of a word template held on the server) and user 2 opens a different record and opens the attachment on that one, then they cant save their work.
Sorry if that sounds confusing Im finding the problem hard to explain, I will try to claify further if needed.
Please please please help!
Cheers Adam

 

[Rabbie]

You need to split your DB or you risk getting data corruption. You should keep the Back End DB with the tables on the server and each user should have their own copy of the Front End DB on their own PC. By having their own copy of the Word template they will also avoid the problems with saving.

 

[AdamRSL]

Ok, the sum total of my knowledge of Databases is what Ive managed to teach myself. You completly lost me with the frond end and back end .

In regards to the own copies of the template point, I think we would still get the same problem as 2 users generally create all the files and upto 5 people could be accessing different files made by the same user so I think the problem would still exist. (I think anyway)

 

[boblarson]

As far as splitting your database, read this:
http://www.members.shaw.ca/AlbertKallal/Articles/split/

As for the template, what SHOULD happen is that your program should make a copy of the template when it runs to fill out and save. It should not open the master copy.

 

[PaulO]

As Rabbie says you need to use the Database Splitter tool to break the database into two ... the 'front-end' will retain any Queries, Forms, Reports, and Macros that you've created. The 'back-end' will retain your data tables. The 'back-end' file created will have a '_be' extension added to the existing file name so that if you started out with a file called Data.mdb you would dervice TWO files called 1) Data.mdb and 2) Data_be.mdb.
Data.mdb will have direct links (full path and file mapping) to each of the tables in Data_be.mdb
The Data_be.mdb file must be stored on the server i.e. one copy
The Data.mdb must be copied onto all five local hard drives
All five users are now accessing JUST ONE set of data tables ... the only sharing conflict should be when two or more users wish to access the same record.
Just remember that from this point forwards ANY changes to Queries, Forms, reports, and Macros must be distributed to all five workstations!

 

[AdamRSL]

Thanks for the info, Ill give it a go this afternoon!

 

[AdamRSL]

Ive split the database and have the back end on the server and everyone has copies of the front end. It all works fine and everyones databases get updated ok.

However the problem of amending the attachment is still there, even with a copy of it on everyones workstation.

Every record has a word document attached when it is created. Information, copies of emails, pdf's, etc are attached to this document by different users. (See jobfile attachments). If USERA is in record no 1 and USERB is in record no 2 and they both have the attachments open only 1 of them can save their changes.

 

[Rabbie]

I am afraid your attached image told me nothing. We need a little more information about this word document. In your example are both UserA and UserB saving the documents to the same place or to a different place. Where are these word documents stored and what are they named?

 

[AdamRSL]

The attachment is a word file (.doc)

User A and User B are saving work to different word files attached to different records on the same database.

The original word document is stored in a shared file on the server. It is copied and attached to a new record on creation. It is called "RSL Cover.doc", I have however tried attaching the doc using unique names for different records and this still doesnt work.

I hope this clarifies.?!?

 

[AdamRSL]

bump 5678910

 

[AdamRSL]

After further experimentation...

It has nothing to do with the attached file names/location/types.

Its just the fact the 2 users are opening 2 different attachments at the same type. Access seems to be using the old token system..

(I used a word document and a excel document with different names on different records and only 1 person (the first to save their changes) could save there changes)

Anyone know a way of changing it??

 

[AxGryndr]

You can't have two users saving two changes to the same document at the same time. How would you handle if UserA makes a change and UserB makes a change to the same record, who's save get's priority?

I can't tell from the example what you are trying to do but it sounds like you have UserA make an addition/change and then UserB makes their addition/change. If this is the case you may want to rethink the way the form is created.

 

[AdamRSL]

Different documents on different attachments on different records at the same time

 

[AxGryndr]

If no two people are working on the same document I don't see the issue unless you are saving something in the table and all your users are using the same table.