Trying to merge modified copy of database with original.

[blueman]

I'm not a programmer but have built a database for my business. Data
is entered through the original at my office. I'm am still in the
process of refining it so I take a copy back home and work on it
there. Sometimes it's a few days before I get the modifications done
and bring it back to the office to hopefully replace the existing one
I copied. In the meantime, the original is being used to enter data.

I realized there would be issues in trying to merge the two. I
figured that I did not want to use the modified database that I took
home, as there was new data inputed in the tables(in the days I was
not there, more data had to be entered)in the copy left at the office.
I assume that if I tried to copy those tables into my "modified
database, considering that there were relationships and key numbers
that were established there that would get pretty screwed up. To avoid
this problem I decided to copy the new (Changed) forms, macros,
queries, reports - everything but the tables from my Modified Database
into the existing Office Database so that the Data, Key numbers and
relationships should not change. While this appeared to work
initially,for some reason, on a later opening of the database, some of
the changes made seemed to not be there. After that,trying to fix what
wasn't working, regardless of how many times I tried to copy a Form,
for example,from the modified database (which works fine) to the
office database. The form was not being found by a macro. I triple
checked the name and it was there. The form is just one example of
what wasn't working, there were other similar issues elsewhere.

I guess my question is what is the convention for making modifications
to a copy of the database and then trying to get those modifications
back into the original. I would greatly appreciate any guidance. If
it
matters, I do not know any VBA code.

Thanks

Mike

 

[John W. Vinson]

I guess my question is what is the convention for making modifications
to a copy of the database and then trying to get those modifications
back into the original. I would greatly appreciate any guidance. If
it
matters, I do not know any VBA code.

What you REALLY REALLY need to do is use Tools... Database Utilities...
Database Splitter Wizard to split the database into a "backend" containing
only your tables, and a "frontend" containing everything else: forms, reports,
queries, and code.

That way you can take home the frontend (and a backend containing data that
you don't care if you overwrite or mess up in your testing); make all the
design changes you want; and bring it back into the office, relink to the
production backend, and give your users their own copy of the frontend.

As it is, you can use File... Get External Data... Import to import modified
Forms, Reports, Macros or Modules without messing up the data; you may need to
rename some of these after importing them.

If you need to make changes to the *actual structure* of tables - adding
fields, deleting fields, changing datatypes - it's harder; you'll need to make
the same changes in your production copy, if doing so won't cause you to lose
data. If it would... rethink the changes.

 

[blueman]

John,

Thanks for the great tips. After reading up on it, it seems like the
perfect solution. I have few questions though:

1) If I make changes to the front end and go back to the office where
the front end (before my modifications) and back end reside ( I will
have them in the same folder on the hard drive), do I simply remove
the "old front end" and insert my new "modified front end" into that
folder? Assuming I haven't modified any tables or table structure,
should everything work or do I actually have use the link manger (for
which the documentation seems to be lacking)

2) if I do modify tables or relationships or add new tables when
working on the back end I assume I would have to keep track of them
somewhere and then implement those changes to the existing office copy
of the back end. I would then link the tables (again, I'm hoping to
find better documentation on the Link Manager). Can you tell me if
this is the correct procedure to follow?.

3) I have not split the database copy yet that remains in the office
and new data is being entered now. I will not be in the office for a
few days but have a copy of the un-split database that I copied just
before I left and took with me. Can I split the copy I have with me,
work on it, take the modified front back to the office in a few days,
split the existing office copy then and insert my modified front end
that I've been working on?

I hope this is clear and not too long. Any further help would be
appreciated.

Mi
ke

 

[John W. Vinson]

John,

Thanks for the great tips. After reading up on it, it seems like the
perfect solution. I have few questions though:

1) If I make changes to the front end and go back to the office where
the front end (before my modifications) and back end reside ( I will
have them in the same folder on the hard drive), do I simply remove
the "old front end" and insert my new "modified front end" into that
folder? Assuming I haven't modified any tables or table structure,
should everything work or do I actually have use the link manger (for
which the documentation seems to be lacking)

Just to be clear: if you have six users, you need six copies of the frontend.
The backend is in a shared folder; the frontend should NOT be shared, everyone
should have their own copy. You do need to use the linked table manage both
when you take the frontend home (to link to your Development scratchpad
backend), and again when you bring it back in, to link to the Production
backend.

Most Access administrators will keep a copy of the frontend .mdb file and use
Tools... Database Utilities... Make MDE (after relinking), and give each user
a copy of the mde file. The mde file has all the design tools disabled so
users can't mess around with the structures of forms and the like; it also
will be somewhat smaller and perhaps more efficient.

2) if I do modify tables or relationships or add new tables when
working on the back end I assume I would have to keep track of them
somewhere and then implement those changes to the existing office copy
of the back end. I would then link the tables (again, I'm hoping to
find better documentation on the Link Manager). Can you tell me if
this is the correct procedure to follow?.

Not quite. If you change the structure of a table, I've found that it's a lot
safer to open the frontend; DELETE the table (fear not, it just deletes the
link, not the table itself); and use File... Get External Data... Link to
create a fresh new link. The Linked Table Manager just refreshes (or
redirects) links to existing tables and would not let you add new ones.

3) I have not split the database copy yet that remains in the office
and new data is being entered now. I will not be in the office for a
few days but have a copy of the un-split database that I copied just
before I left and took with me. Can I split the copy I have with me,
work on it, take the modified front back to the office in a few days,
split the existing office copy then and insert my modified front end
that I've been working on?

Probably, but not certainly. You might give it a try with the suggestions
above - delete the tables and relink for example - but BACK IT UP first just
in case it doesn't work.