I need to distribute my access database

[Dennis]

Hi,

I am on Access 2003 via MS Office Pro - 2003.

I am working on a club membership tracking in Access. Originally, the
software was built for the newsletter editor so they could maintain the
mailing list. However, the club wants distribute the software to four data
entry users and numerous lookup users. All of the data entry people have
internet access but not all of the inquiry user have access.

Due to club organization, there will be four different people enter data
into the database and all of them work from their home computers. I’ve
asked, but it not possible to have one person do all of the data entry.

The four people are:
The membership chairman will maintain basic membership info.
The ladies chairlady would maintain additional ladies specific member
info.
The sons chairman would maintain additional sons’ specified member info.
The newsletter person will maintain just the newsletter info.

Note: Each of the four users would be limited to what data field they can
enter and change. That way, no two users could change the same field thus
eliminate having to figure out which field has the “correct†value.

Currently, all membership information is maintained within a single table.

Does any have a suggestion how to approach this from challenge from a
database / data processing standpoint?

I can not connect everyone up to a central server because there is none.
I’ve read a little about the Access database replications and that might be
the answer, but that means that the users would have to have access to the
other user’s computer which means that one people would have to call the
other to see if their machine was up and it not, to bring it up. I can see
all sorts of coordination problems using this approach.

I have two alternate approaches this problem.

1. I can create a transaction file and record every change in a change
table on each computer. These changes tables would be sent to a central
computer which would read through the individual transaction tables and
update its database. The central computer would then send out the updated
master database to the other users who would replace their entire database
with updated master database.

2. Break up my single table into four tables; the main membership table,
ladies table, son’s table, and newsletters table. The four tables would be
joined via a single query.

At this point, the users could then e-mail their individual tables to each
other and each person would load the additional tables into their database.
Or I could use the central computer approach discussed above.

Also, be aware that some of the inquiry computer’s would have to be update
via sneaker net since they so not have internet access.

Both of the above solutions are very ugly. Does anyone have a better
approach or suggestion?

Thanks

 

[Dennis]

FYI - I cross posted this in microsoft.public.access.tabledesign.

 

[Larry Daugherty]

Hi,

Your club has ambitions. Are they willing to back up their ambitions
with investment?

In the first place, the background you have given and the approach
strategies you suggest indicate that you are not at all familiar with
Access. That's not a put-down. At some time, none of us here knew
anything about Access. The biggest single hurdle most people have to
overcome is that Access is not a flawed version of Excel. Excel is a
superb spreadsheet platform. Access is a whole cluster of RAD tools
and functions layered atop a Relational Database Management System.
While Access and Excel can interact, they are universes apart
regarding their purposes and their methods.

If you have all that many disparate users who need various levels of
access into the prospective application at various times, Access may
not be your best bet. The two facts: that you have a number of users
and that they are never on a single Local Area Network implies to me
that you might do well to consider a Web based solution. You can get
a domain hosted at $100 per year or less. You can hire someone from
Guru.com or Rentacoder.com or GetaFreelancer.com who will create the
database for you and provide the user interface to manage the users
and manage the data appropriately.

There are other ways to go about it but that's what came to my mind
first.

There are things you might do for your own edification:
1. Buy an Access 2003 for Dummies book or an earlier version would
do.

2. Download the Membership template in Access 2003 by
Open Access
File|New
In the search box to the right, just below "Templates", type
Membership
That will find the Membership template online and offer to
download.
Go for it. Give it a name you like.

Once you have the template loaded and open, play around with
it both as a live application and in Design mode to see what's going
on behind the scenes. The templates available from Microsoft are not
good examples in all respects and they are very light on VBA code.
Analyzing what's there will give you some good ideas of some of what
Access can do.

HTH

 

[M Skabialka]

I am not sure how you plan for everybody to e-mail their individual tables
to each other, but if you use linked tables you might be able to use this
line of thought:



Create 4 separate Excel files, one per person. Copy the table data for that
person into their spreadsheet (name the sheet the same as their table to
simplify linking), with no other formatting than the data type (dates,
numbers, etc) and column header. Place these Excel files on a folder on the
PC that will have the same path on all of the PCs, e.g. c:\DATA.

From Access, link to these four spreadsheets (you won't need the original
tables any more, but create a copy of the database before you delete them as
a backup), and they will be seen as tables in Access. Each of the four
people gets the Access database, plus all four spreadsheets, but each sends
only their own spreadsheet to the others after changes are made in the
database. Spreadsheets are easy to send, tables aren't. Also, if the data
is in the spreadsheets, and you change something about the database, e.g.
adding a report, you can send the database to everyone to replace their copy
and the data is not affected.

For other users you could either send the database and the spreadsheets,
with instructions on where to place them, or you could create a copy of the
database and IMPORT, instead of LINK the tables and send it out that way.
Their changes to data will not be recorded in the master copies.

This is not an ideal solution, probably breaks all kinds of rules, but for
you with organizational restraints it may work.

Mich.

 

[Dennis]

Mich,

The data is already in a linked table. I was planning on putting each
sub-set table in its own database and link it into the main database. On a
regular basis (once week or twice a month), everyone would e-mail their
sub-set table to a central person via an option on the main menu
(switchboard). The central person would receive the e-mail and replace their
sub-set database with the new one via an option on the main menu. After
that, they main person would send out a complete database to everyone which
would replace everyone’s database and the cycle would start over.

But the more I think of this and knowing the level of computer knowledge
these people have or don’t have, I am becoming more and more convinced that
this is a really bad idea. So I’m going to have to get them to do things
differently.

Thank your for your advice.

Dennis

 

[Dennis]

Larry,

Thank you for taking the time to respond. However, your response indicates
you did not understand the issue. I clearly state that some of the users DO
NOT have internet access, so proposing a web based solution is not
appropriate.

 

[Larry Daugherty]

I understood the problems but lost sight of that one detail amongst
the plethora of other issues buzzing in my head. How do you intend
that those other "lookup" users gain entry into the data or update it:
phone calls, snail mail, hand carried notes, serial communication
between users, other, ?? Your answer is material to any possible
solution.

These Access newsgroups are serviced by unpaid volunteers to help
Access developers at all technical levels over technical hurdles. One
issue per thread, please. The issue you introduced is not an Access
issue. It's about your hoping for someone to give you a strategy for
solving a problem you've pretty well defined to be unsolvable.

If you and your users aren't going to change anything about what you
do and how you go about it then you can't get there from here.


HTH