Creating a template database

[Dani]

Ideally I would like to create a database which can then
be used as a template that is filled in and the data saved
under different filenames for different individuals. I am
sure this must be possible but I am finding it hard to
find exactly how.

Can anyone help?

 

[Arvin Meyer]

Create the database file and save it under different filenames. But why
would you want to do that? It violates every rule of relational database
design. It is certainly possible to create a single database and have people
be able to see/create/edit only their own recordset.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Guest]

Basically I am not using Access in it's truest form. I am
trying to design a system where data can be entered and
stored in Access, transferred to Excel to perform some
calculations and then returned to Access to put the
results in a suitable report format. I find it easier to
create the look I'm after in Access. However, the system
will be used by several people to perform the same
calculations on different sets of data and they need to be
able to save it as their own work and not have it wiped
out or added to by others.

I guess you're saying it isn't really possible because it
goes against everything Access is about? I can't see a
way of getting what I want in Excel can you? If so I'd
appreciate any pointers.

 

[Arvin Meyer]

Sure, have the user login with either your login or Access security, or by
capturing the Windows login:

http://www.mvps.org/access/api/api0008.htm

Store that name in a field with each record. Write your queries so that they
only call data that belongs to that user. Then export that to Excel, do your
magic and import it back into Access for reporting.

My point is that you don't need a separate file for each user.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Dani]

Ok, you'll have to bear with me because I am a relatively
new user of Access.

The situation I have (and I didn't choose it) is that the
finished program will be uploaded onto several, un-
networked computers and the users need to be able to save
their data on floppy and come back to it potentially on
different machines. Correct me if I am wrong but I would
have to define the individual users, they would not be
able to create their own user login?

 

[Brendan Reynolds]

a) Do you really need Access on the individual users' PCs at all? It seems
you're using Access only for reporting. Do individual users produce the
Access report from their own data, or is the report run only on one PC,
using the consolidated data? If the latter, just get the Excel workbooks
from the users and import or link it in Access on the one PC that does the
reporting.

b) An Access MDB is likely to soon outgrow the capacity of a diskette.

c) I wouldn't trust a diskette half as far as I can throw it these days -
they corrupt at the drop of a hat. (Please forgive the mixed metaphors!

d) This is what they used to call 'sneaker net', and it really should have
died a quiet death back in the eighties. Heck, my new notebook doesn't even
*have* a diskette drive!

 

[Dani]

Let me explain. I am doing this as a university project
for a fieldcourse my department runs every year. Every
Easter they traipse all the second years out to the Lake
District (North England) where they map the surrounding
area using various surveying instruments. They need to
take the data they collect throughout the day and perform
some calculations on it and then they can use the results
to produce the map in the correct grid co-ordinates.

The computers are borrowed from the university and set up
where they are staying so cannot be networked and they may
not be able to get back onto the computer they were using
previously, hence it needs to be portable.

I am trying to design a suite of programs to perform the
calculations and decided the best way would be to link all
the data input, calculations and output through Access as
it has a very good format for menu systems and easy
manipulation of the data to produce the outputs I am
after. I could do it all in Excel but it's forms are
pretty basic looking and I have less control over the look
of the output. I am trying to produce a more professional
appearing system than they have previously had.

The idea is that the user will never see the calculation
steps that Excel does. They will simply input the data
into Access, hit 'go' and a few minutes later be able to
view the results in a familiar format, back in Access.

So, fundamentallhy I am using Access as a data entry and
reporting system. Hence I can't really split it up. If I
do I might as well use Excel.

I hadn't thought about file size. That could be a problem.
I just wish they'd get CD-writers on all the computers,
it'd make life easier!

I am going to have to rethink this now! If you have any
suggestions I'd love to hear them!

 

[Larry Linson]

If I understand, there will be no communication between users, and no
network, so a multiuser environment is neither needed nor possible. I am not
sure what "template database" has to do with it -- you are simply creating a
single-user database and giving copies to as many users wish to use it
individually.

I agree that diskettes are not a very reliable medium, comparatively
speaking, but having said that, I've used diskettes over the years with very
few failures. But, aside from regular compacting of the Access database,
using a compression program (e.g., ZIP), and splitting the application and
data, there's little to be done about the 1.44 MB file size limitation.

Larry Linson
Microsoft Access MVP

 

[Brendan Reynolds]

I agree entirely with the first paragraph below - just create one copy of
the app with empty tables and give each user a copy.

Re the second point, about the diskettes, I *used* to use diskettes, years
ago, with few problems, but in my experience they have become significantly
less reliable in recent years. A Zip drive might be a better choice - the
USB models can be very quickly and easily transferred from one PC to
another. The disks cost significantly more than CD-R disks, but can be
written to more quickly.

Another alternative would be wireless networking. I would not recommend
linking JET tables across a wireless network - the risk of data corruption
due to a dropped network connection is too high - but it could be used to
copy users' data MDBs from a central storage point to the local hard disk,
with the application linking to the local copy.

With either of these scenarios, some careful planning is going to be needed
to ensure that one user does not overwrite another user's data.

 

[Larry Linson]

. . . A Zip drive might be a better choice -
the USB models can be very quickly and
easily transferred from one PC to another.

I've used ZIP drives with success, most often because the database outgrew
diskettes (I did tend to make and take two or more copies via diskette). I
even have a built-in, SCSI, ZIP drive in a vintage-2000 desktop unit and, to
my surprise, my original parallel port ZIP drive still works as well as
ever, after heavy use over the years, but I am finding fewer and fewer
others who still use ZIP.

Because I have a couple of USB "multi-card readers" because of my digicam,
I have used MMC and SD cards more lately for my own transfers, and I've
recently gotten a USB "thumb drive" (flash memory) that works well -- and is
recognized by Win 2K and later without even loading any drivers.

But, if my data was sufficiently small, I wouldn't have any fears about
transporting it on diskette (with a backup copy or two). I would, of course,
want to have some confidence that the machines to which I was transporting
it had a working diskette drive (as some do not, these days).

Perhaps the difference is that I haven't purchased any new diskette media in
several years. For some time, large quantities of diskettes with 100%
mail-in rebates were used as come-ons by the computer superstores, and I
acquired what appears to be a lifetime supply. <G> Now, CD-Rs seem to be
used for that purpose.

Larry Linson
Microsoft Access MVP

 

[Brendan Reynolds]

For the particular situation that Dani describes, an ideal solution might be
to give each student one of those key-fob sized USB storage devices, like
the 'disgo'. It would probably be possible to work with the data directly on
the device, without having to copy it to the hard disk, thereby reducing the
danger of overwriting anyone else's data. A little more expensive than one
Zip drive and a Zip disk per student, but a lot more convenient than having
to shunt the Zip drive around from one PC to the next.

 

[Dani]

These all seem like good ideas, I might have difficulty persuading my lecturers to buy new storage media though but I will offer up the suggestions. I never expected to start up such a debate. Thank you both for your help