How to collaborate on an Access database?

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

Guest

I'm trying to decide whether Access is the best tool for the job here, and
have some questions.

How would a group collaborate on an Access database? A coworker and I would
be working on this together, with both of us adding and updating information,
probably at the same time. Is this possible with Access? How do people
usually do that kind of thing?
 
Would you both be working on the same object (e.g., table, form, report,
....) at the same time, or would you divide up who works on what?

Would you have a common understanding of the target, and know how to know
that you had hit it?

Would one of you be doing "prep", and the other one doing "finish"?

You've asked if Access is the best tool, but you haven't described for what
job, just how you would use the tool.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Would you both be working on the same object (e.g., table, form, report,
...) at the same time, or would you divide up who works on what?

Would you have a common understanding of the target, and know how to know
that you had hit it?

Would one of you be doing "prep", and the other one doing "finish"?

You've asked if Access is the best tool, but you haven't described for what
job, just how you would use the tool.

Regards

Jeff Boyce
Microsoft Office/Access MVP







- Show quoted text -
Click to expand...

Access can ceratinly be use in a multi-user environment. You and
several other could do data entry and or other tasks in the same
database. The design of the database (table structure) and the design
of the user interface (forms, queries, reports, etc. is very important
to the smooth operation of your database.

But, as I said, Access is a very good tool for a database, even in a
multi-user environment.

HTH

Mr B
 
Access no longer directly supports making
design changes to forms and reports at the
same time.

It used to be that those things were stored
in tables (a relation database system), and
as long as you weren't working on the same
object (technically, as long as you weren't
working on the same page), you could both
work at the same time.

Three other methods exist:
1) split the project up into multiple files.
We use a shell database with menu's, referencing
several other library databases. You have to
choose to use a menu system, and you will
need to have linked tables.

2) Use Visual Source Safe, the ms version
control system. Access exports each object
as a text file, which it stores in a VSS
database (a multi-user database). This is
not suitable for large projects because the
database automation is slow and flakey, but
it might work ok for small projects.

3) Do it all by hand. Work separately, and
periodically merge your changes.


(david)
 
Not sure whether you're asking about collaborating on the design of a
database (for which there have been useful replies), or simply on multi-user
access to a common database (since you say "both of us adding and updating
information").

In the latter case, the main point is to split your database so that each
user has their own copy of the front-end (containing all the queries, forms,
reports, code modules), with each front-end linked to the common back-end
database file which contains only the data tables.

Rob
 
Thanks for all the replies.

The way it would work is that one of us would probably be in charge of the
design (forms, reports, table design, etc.), and both of us would be adding
and editing records. These changes would be happening to the same tables at
the same time, but probably not to the same record.

Someone mentioned using multiple files. One file would just have the tables,
and the other would have the forms, etc. and could be given to the other
person as a copy? So the common database file (.mdb file or .accdb file)
would be the remote data source, on a shared drive or something? I'm an
Access neophyte, I'm not familiar with how this works. Is there a help file
on this somewhere?

I do have access to a central MySQL database server, but it has access
restrictions (on-site vs. off-site, etc.) that make it less useful for this
kind of thing.
 
I mentioned using a split database, for multiple concurrent users. It's
essential if you want to avoid corruption, and in later versions (after
A2000, IIRC), it's also necessary if you want concurrent design and data
entry/edit activities. It's also extremely advisable, in earlier versions,
not to perform concurrent entry/edit use and design activities in the same
file by different users.

Here's a page with some references you might find useful on the subject:
http://www.accessmvp.com/JConrad/accessjunkie/splitting.html

There's a few more comments in-line below.

Again, HTH

Rob


joeljkp said:
Thanks for all the replies.

The way it would work is that one of us would probably be in charge of the
design (forms, reports, table design, etc.), and both of us would be
adding
and editing records. These changes would be happening to the same tables
at
the same time, but probably not to the same record.
Click to expand...

Access will allow multiple user access to the same tables at the same time;
if, by some rare chance, two users edit the same record at the same time, a
warning will be displayed, with options to overwrite or drop changes.
Someone mentioned using multiple files. One file would just have the
tables,
and the other would have the forms, etc. and could be given to the other
person as a copy?
Click to expand...

Precisely. The front-end file (with the forms, etc) can be edited by one
user, and the new version then copied to other users. There are methods
available for auto-updating of front-end files, but I suspect you're not
going to be needing them at this stage, and simply distributing a copy of
the updated front-end file will suffice for you at the moment. The
front-end contains links to the back-end data tables; these appear in the
tables section of the database window of the front-end file, as linked
tables.
So the common database file (.mdb file or .accdb file)
would be the remote data source, on a shared drive or something?
Click to expand...

The back-end file is most commonly another .mdb or .accdb file, on either a
server or a shared drive. You can also use other database systems (eg. SQL
Server, or other ODBC databases) as the back-end, with an Access front-end.
I'm an Access neophyte, I'm not familiar with how this works. Is there a
help file
on this somewhere?
Click to expand...

See link near start of this post.
 
DAVID said:
Access no longer directly supports making
design changes to forms and reports at the
same time.
Click to expand...

I think you know what you're talking about, but are having a little trouble
expressing it. To change the design, the user must have exclusive use, so
two users cannot concurrently update the design of the database. You can,
of course, modify the design of both Forms and Reports is you have the
permissions and exclusive use.

Some of the other comments, too, could be misleading.
3) Do it all by hand. Work separately, and
periodically merge your changes.
Click to expand...

Generally, with good project planning, there are few times when multiple
developers are working on the same piece of the project. This has worked
reasonably well on the projects that I have done with Access.

There were at least two software tools, back in Access 2.0 days, for
controlling the source. Neither of them survived, AFAIK. It wasn't long
ago, though, that you did not need exclusive use of the DB too be able to
update a Form/Report. And, as far as I know, we never experience corruption
due to this "feature" with multiple developers working. But, our planning
was such that we were developing in different functional areas and not on
the same database objects, except in rare instances.

Larry Linson
Microsoft Access MVP
 
As mentioned before, nothing else really works until you've split your
application into a FrontEnd and BackEnd. Look in Access Help for
"Split". There is a splitter tool. Use it and post back if you have
problems.

The BackEnd must be placed in a folder somewhere on the LAN where the
same shared folder can be made available to both of you with the same
pathname. That saves hassles as both data entry and development
continue.

For data entry all users should have a copy of the working version of
the FrontEnd on their PC. Do any "live" data entry using that same
FrontEnd as the other user(s).

You, as the developer, should have both the FrontEnd and BackEnd in a
separate folder on your machine. Do your development on that setup.
Be sure to use the Linked Table Manager to break the link to the
tables in the live BackEnd and reattach them to the BackEnd in your
development folder. You should be the only one who has to be
concerned with properly breaking and reattaching linkages. If you
don't take care of business it will cause mysterious difficulties
during the course of your development and updating. A huge clue is
that data that someone knows they've entered doesn't seem to be there
anymore.


The normal, simple version of enhancing the design and updating your
users - your own 'live' FrontEnd as well as the other user(s) will go
something like the following: You make changes to the design in the
FrontEnd of your development application. When you're happy with your
current changes (which you've been testing all along), you save your
current live FrontEnd by simply giving prefixing the date to its name.
You then copy your updated FrontEnd into your 'live' folder. Open the
Linked Table Manager, break the linkage and reattach to the 'live'
BackEnd. Enter some date and verify that your new entries are
correct. You should also be able to see all of the data that other
users and you have entered into the live database. If all is well,
give a copy of the new FrontEnd to everyone who needs it.

Inevitably, the time will come when some changes to the table design
are required. Life gets more interesting, maybe complicated is a
better word. Make sure that everyone is out of the database.
Rename the live BackEnd as SomeOtherName.mdb in its current folder.
To make your life simpler I recommend that you open your development
FrontEnd to the Database |Tables window and delete each linked table
(gasp!). Then using Database Utilities | Get External Data | Import
Tables point at the *live* BackEnd you just named SomeOtherName.mdb
and select all tables and click OK. You have just turned your
application back into a monolithic thing. (Gaaack!). One by one,
open the necessary tables and make the necessary changes; no more, no
less. Be sure to put good description text in your field
descriptions. That can be very helpful in as little as a couple of
years. Now the real fun begins: the changes to your tables have to
ripple up through every Query, Form and Report that uses the affected
tables. That can be pretty daunting.

There is help:
Speed Ferret, a commercial product. Only good things heard about
it.

FindAndReplace from Rick Fisher at Rickworld.com. A shareware
product that I've used for years through several versions of Access.
Rick's download files are all "repl"SomeNumber.

FindAndReplace again, a freebie, but not from Rick Fisher. I
guess Rick didn't patent the trademark or copyright it or something.
I haven't tried it.

Using either of the first two tools you have some very powerful
capabilities. Essentially a huge Find and Replace but with the
ability to include or exclude any and every class of relevant object
in the database. You can also log every hit. I've always stepped
through and decided on a case by case basis whether to make a change.
Be careful and thorough and all will be well. Test your application
thoroughly while still in its monolithic state.

When you're satisfied that nothing is broken then,once again , split
your application. Yes, it *is* necessary. Put a copy of the BackEnd
over in the shared folder. Place a copy of the FrontEnd in your own
'live' folder. Using the linked table manager in your 'live'
FrontEnd, break the links and attach to the BackEnd in the shared
folder. Give a copy of your new FrontEnd to each user.

All of that can be a bit overwhelming. You could print the thread and
keep it as a handy reference. I've answered questions you haven't yet
asked. You would have gotten into every issue and then had to dig to
find the path and the next step.

HTH
 
Thank you Larry and Rob for the very informative posts. I'll keep them as
reference while I try this out.
 
Excellent description.
You, as the developer, should have both the FrontEnd and BackEnd in a
separate folder on your machine. Do your development on that setup.
Be sure to use the Linked Table Manager to break the link to the
tables in the live BackEnd and reattach them to the BackEnd in your
development folder. You should be the only one who has to be
concerned with properly breaking and reattaching linkages. If you
don't take care of business it will cause mysterious difficulties
during the course of your development and updating. A huge clue is
that data that someone knows they've entered doesn't seem to be there
anymore.
Click to expand...

Or for example, when the app is tracking the user who does the data
entry. And you discover the developer, meaning you, over the past
year or two, has somehow managed to key in enough test transactions to
overpay employees by about $1400. Of course those employees never
bothered telling the payroll department that they were overpaid. Not
that I'm mentioning any names other than to say his initials are TT.

So what I, errr, TT, did was to check to see if the userid was me,
err, TT, and the linking path was not my, err, the local hard drive.
If so the main menu form background was highlighted red.

In addition I would also display the path of the BE MDB if the FE MDB
was an MDB. Thus I didn't display the path if the FE was an MDE.
The normal, simple version of enhancing the design and updating your
users - your own 'live' FrontEnd as well as the other user(s) will go
something like the following: You make changes to the design in the
FrontEnd of your development application. When you're happy with your
current changes (which you've been testing all along), you save your
current live FrontEnd by simply giving prefixing the date to its name.
You then copy your updated FrontEnd into your 'live' folder. Open the
Linked Table Manager, break the linkage and reattach to the 'live'
BackEnd. Enter some date and verify that your new entries are
correct. You should also be able to see all of the data that other
users and you have entered into the live database. If all is well,
give a copy of the new FrontEnd to everyone who needs it.
Click to expand...

You can distribute the FE MDE with a date appended to the name but I
chose not to do so. This works better with the Auto FE Updater if you
use the same name.
Inevitably, the time will come when some changes to the table design
are required. Life gets more interesting, maybe complicated is a
better word.
Click to expand...

I chose to manually track the changes I made to my own backend. Then
once I was ready to publish the new FE I duplicated those changes to
the live backend. Your process seems a bit too much work for me. In
addition without some care and attention you'd lose the relationships
window layout.

When I'm working on a shrink wrap app then I use the procedure and
sample code as at
Updating an Access Backend MDBs structure using VBA code
http://www.granite.ab.ca/access/backendupdate.htm

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
Back
Top