share a database

J

jwb

Ok I'm new at this, be kind.

I have a database that we are using, me and a partner for a project. This
is a volunteer project and so we are not in an office network envirornment.

I am working on populating the database while she works on forms, queries
etc. How do we transfer the updated "data" between users and the same with
her forms etc.

I have read but barely understand the concept of front end and back end
splits. Do I need to get smarter on this, or is there an easy way to
"sycronize" our efforts.

I really appreciate anyone's expert help.

jwb
 
S

Scott McDaniel

Do you mean you are not networked to your partner's machine? If no, there
really is no "easy" way to make sure your data and her's are synchronized.
Access does provide replication services, but that's normally a big job and,
as you admit to being new to this, you really should bone up on the
ins-and-outs of Replication before you make a decision in that regards.

Regardless, you should still split your database. The "frontend" would
contain everything EXCEPT your tables. The "backend" would contain only
tables. Access includes the Splitter Wizard, which will do this for you and
recreate links. If nothing else updates to forms and such are easier, since
your partner would simply send you the latest version of the frontend file
and you would merely copy over your existing frontend. If you and her mimic
directory structure (i.e. both of you have datatables in C:\DataFiles\) then
you won't even have to relink.

However, if BOTH of you are entering data - or if both WILL be entereing
data down the road - then you will have to explore something like
replication.
 
J

jwb

I'm getting there.

Two questions then:

1. Is there a way to via the internet share the files?

2. What would you suggest for our dilema. As a minimum I need to be able
to populate the database and then be able to use it with the reports, etc
that she has created at her machine.

It is not obvious to me in Access how I save or backup data / files and move
it to another computer.

If I copy the entire "access file" it will over write all the changes the
other person has been working on.

I don't understand how to just move say some reports or the "table" itself.

I know you think I am probably in over my head, can you throw me a line?

Jwb
 
J

jwb

Ok, I am getting there. I will try to front end, back end thing for our
solution. That way I can have my partner work on the reports etc. while I
maintain control of the database and populating it.

I still am not sure how you share just parts of the Access file. It is not
obvious to me how to save / copy / transfer etc. only part of the file. That
is when my partner updates reports or forms etc. how do I get them into my
working copy of the file?

thanks JWB
 
J

John Vinson

Ok, I am getting there. I will try to front end, back end thing for our
solution. That way I can have my partner work on the reports etc. while I
maintain control of the database and populating it.
Click to expand...

If by "populating" you mean adding data to the Tables, then it's
pretty easy: you would both have frontends, linked to the same shared
backend. The backend database would contain all the tables, and only
the tables. It's no problem to have two different frontends linked to
the same backend.
I still am not sure how you share just parts of the Access file. It is not
obvious to me how to save / copy / transfer etc. only part of the file. That
is when my partner updates reports or forms etc. how do I get them into my
working copy of the file?
Click to expand...

It's simplest if only one person is doing design changes on
forms/reports/queries etc. That person would be in charge of the
frontend; when they have the new improved version, everybody else
simply deletes their own frontend database and gets a fresh copy.
Since there's no data in the frontend this is safe.

If two people are concurrently updating the user interface, things get
much dicier. I have not used it but I understand that A2003 "plays
nice" with SourceSafe (older versions had some REAL problems).
Alternatively, you can use File... Get External Data... Import to
import objects. If you already have a frmMyForm in your copy of the
database, delete it or rename it first - Access will keep both copies,
naming the new one frmMyForm1 if they clash. This works but gets
tricky if the form calls custom queries or if you change a subform
(and forget to import the new subform).


John W. Vinson[MVP]
 
L

Larry Daugherty

You and your partner both have to do the FrontEnd - BackEnd thing. I
have the impression that you're entering data into the tables directly
so, if the only thing in your copy of the database is the tables
you've been using then it's already a BE. I don't know your version
of Access but look in Help for Split and it will guide you to the
proper set of directions for splitting your database. You both have
to agree on the names of your FE & BE components so you can
conveniently talk about what you're doing.

The gist of it is that you'll each have a FE and a BE. She'll have
the data from you, current to the time you send it to her. You'll
receive the FE from here. You will each have to go into
Tools|Add-ins|Linked table Manager from your front end and find the
applicable back end. If you both have your configuration HD set the
same and use the same FE & BE names you'll be able to skip linking the
BE after the first go-'round. As of the moment you each get things
hooked up, you'll have your partner's neat forms for data entry and
the creation of real reports and can give her feedback. She will know
that she's using real data and be able to test her assumptions about
the data.

As you are shifting things around and sending copies of things to each
other be sure to store a copy of everything in a safe place on its
originating system. A convention I use in such cases it to prefix the
educedd date onto what ever it is. That tells me it's a backup of
that named thing as of that date.

HTH
 
J

jwb

OK, I've learned a lot today... thanks everyone. But I have more to go, so....

I understand and HAVE created a BE database. It is neatly labeled YMCA
Gala_be and as I expected and you already know, it contains only the data, or
tables. PERFECT.

NOW... how do I create the FE? I have looked in the help menu and I can't
find it or I don't understand it. I was expecting to be able to created a
YMCA Gala_FE and then use some kind of "Link manager" to tell the two to talk
to each other when necessary.

Am I on the right track?

My next question is going to be after I figure this out.... the best way to
share these BE and FE with my partner who is not on my network and in fact is
at another location. I am figuring out that there is no "export" or
"syncronize" feature, rather you do it the old fashion way..... copy the
entire file either BE or FE and paste it over top the outdated BE or FE file.

I am running Access 2002, and I am afraid to say I am not sure what version
my partner is running. IS THAT GOING TO BE A PROBLEM?

This has been a great help. thanks.
 
L

Larry Daugherty

You don't need to create a Front End. Your partner has the copy going
right now.

I use the same root name for the Front End and the Back End. You may
want to include the letters BE or FE as you have done. Good Job!

Your system and your partner's system aren't going to talk to each
other directly unless you're using Pro level OS or are using a 3rd
party VPN. It can be done, I just don't think you're doing it or that
you'd even want to at this stage of the game. For her to validate her
design she needs your current BE. For your life to get easier and
more productive you need her current FE. You will also be validating
things.

Be prepared for your lives to become kind of complicated the first
time you go through the hand-off. One or the other of you may have
changed the design of some of the tables in the back end and that has
to be brought into synch before you add a bunch more data. You guys
should talk and it may be necessary for you to get your heads together
at the same computer screen to resolve issues. Make sure that all
changes to resolve differences are made with complete understanding by
both parties. Once well begun, ship the other party their part of the
changed application.

Email copies of the current stuff to each other as attachments to an
email.. Best to include an actual email detailing what and when it is
and any other things that are nice to know. I've already suggested
that you mark and store the current stuff before putting the new stuff
into play. Both of you should be making copies before you try to hook
up the other person's piece to your own. Overwriting wont work from
Windows Explorer, it will just crate My_Be(Copyn). Your desktop icon
would still be pointed at the OLD copy.

No problem unless she is using Access 97 or earlier. Access 2002 and
2003 will save in Access 2000 format unless you specify differently.
Once saved as a later version, that's what it is unless you explicitly
change it. My recommendation is to save in the default Access 2000
format.

HTH
 
J

jwb

Larry, you've been a great help, I am almost there...

Let me fill in a few details and then I think you will see exactly where I
am still in the fog..

My partner and I started with one Access file that had all the tables and
reports etc. Together we had created this, while working on this project
last year. We used the Access files with great efficiency for our auction
project.

This year however we have seperated the task, I am working on the "table" by
being responsible for populating it with the data, that is entering all the
information into it. (We took last years end file and deleted all the
records so that we could just update with this year's data and still have the
reports etc. we had created.)

My partner will concentrate on updating the reports, forms etc.

So. I copied the Access .mbf file and brought it home to my computer and
have been putting in data. She is at her place working on her duplicate copy
of the .mbf file and updating and eventually printing all the forms, reports
etc.

At your direction, I was easily able to go throught the splitting wizard and
Access automatically created a seperate file in my folder with the same name
as my original file, but ACCESS added the "be" to the name. And as you know,
this folder can be opened in Access but only has the table.

This makes great sense to me so far. ..... BUT....

I have this concept in my mind that I should be looking for a way for Access
to automatically create a file with a "fe" in the name?????

OR.... does the orignianal file now become known for practical purposes as
the "FE" and my partner and I just swap this file back and forth, overwriting
the old, and share the BE file seperately?

If this is all true up to here, then how do I LINK the FE with the newly
created BE file?

I wish I could buy you a cup of coffee for your trouble.

JWB
 
L

Larry Daugherty

Are your "mbf" files really MDB?

I confess that I've never used the splitter. I began splitting
applications before it came along and have continued to do it
manually. I can't answer your questions about the effects of using
the spliter. I can tell you that you want your BE to consist only of
the tables and the Front End should have everything else. I imagine
that the splitter generated Front End shows all of the original
tables as Linked tables have an arrow symbol to the left of the table
symbol.

I thought I'd read that your partner was re-doing the design of the
forms and reports. Review shows me that the operative word was "work"
What other "work" is there on the forms, queries and reports? If the
clocking of another year requires that your forms and reports be
redone then you folks should learn a whole bunch more about
generalizing your forms, queries and reports. This may come to you as
either good news or bad news but a properly designed application can
hold many years of data and will not require annual overhaul of the
forms and reports.

If you look into the original MDB file you were using and see the
tables with arrows then that copy should be marked_FE. If all that is
true, you can continue to enter data using the forms there and it will
go into the tables in *_BE. Continue to enter data until you and your
partner are ready to swap files for test. You send her a copy of
*_BE. She should go through the same splitting process and send you a
copy of *_FE. I suggested in an earlier post how to manage your files
properly. If you both are usingsimilarly named folder structures then
moving or renaming the older files will keep them out of the way and
putting the new files in the same directory should require no further
action on your parts. If you do it differently then you need to start
learning how to use Access help a bit more. There is help on "linking
tables".

Good luck.
 
J

jwb

Larry,

thanks, I think I am there for what I am doing. You have been a great help.

a few last comments / questions.

I did in fact mean that my access files are .dbf.

Some clarification. When you use the database splitter, Access creates a
file with the same name but adds the extension name_BE. This file only
contains the tables, nothing else.

The original Access .dbf file remains the same, with everything in tact, all
the reports etc., but there is an "arrow" to the left of the table nams to
let you know it is linked, HOWEVER it does not rename the original file to
something like "name_FE".

That threw me for a loop for awhile.

Also, in my ignorance, and yes I use that word correctly, until I was
further enlightened I was working with the "name_BE" file and then file..get
external data..import forms etc to then be able to work with the "name_BE"
file, BUT NOW I KNOW...

I just need to work with the original file and it will keep the "name_BE"
file up to date, that I can then share with my partner by saving on a disk
and transfering to her, or send the "name_BE" file as an attachement and
allow her to over write her old "name_BE" file.

The only expirement I have left is how to get her totally up and running.
Do I have to do a database "split" for her on her machine with the original
"name.dbf" file or can I just copy BOTH of my files, "name.dbf" and
"name_BE.dbf" into the same directory on her machine and then she'll be all
set?

And finally, no we are not re-doing our reports etc from previous year, only
tweaking them and adding new ones for our needs this year. We are not that
"ignorant"

Thanks so much, you've been a great resource,

Jim
 
J

John Vinson

I did in fact mean that my access files are .dbf.
Click to expand...

Then they are not Access files.

..dbf files are dBase "databases". Some other dbms's used the dbf
extension as well, and there are several "flavors" or formats, but
they're all external files as far as Access is concerned.

They can be linked to an Access database, but *they are not Access*.
If your data is stored in .dbf files then they are ALREADY linked; you
gain nothing by using the database splitter wizard (and I'm rather
surprised that the wizard actually works!)

I would suggest that you and your coworker should simply ship the data
(.dbf files) back and forth; or (better) if you are both on a fast and
stable LAN, put them on a shared directory and each of you link your
Access database (which, since you have no data stored in Access, is
*already* functionally a frontend) to the .dbf files.

John W. Vinson[MVP]
 
Top