Macros to copy an Access DB?

[John Thow]

Hi,

Whilst I fundamentally dislike having more than one copy of a DB, I'm in a
situation where I need to. Currently, I'm doing it by having 2 copies of
Access running and copying and pasting the relevant bits from one to the
other. This makes me rather nervous, in case I end up copying from the
'slave' DB to the 'master', so I'd really like to find a way to automate the
process. Unfortunately, I know almost zilch about Access VBA - although I'm
fairly comfortable with Excel / Word VBA.

Q1: Can This process be automated at all? (In Access 97)

Q2: If it can, these are the things that need to be done:-
In the 'master' DB,
a) copy 3 tables, lets just call them A, B & C;
b) Run a query that selects whole records from table D where
these are 'Current'.
In the 'slave' DB,
a) remove all joins;
b) empty all tables;
c) paste the copies from the 'master' to A, B, C, & D:
d) rebuild the joins.

[Fairly obviously, processes (a) and (b) on the 'slave' have to be done first.
Then the 4 copy and pastes done one at a time, followed by process (d) on the
slave. ]

Some guidance would be much appreciated.

TIA
--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!

 

[Ken Snell]

When you say you need to have more than one copy of a database, are you
referring to the data? A normal setup of ACCESS is to split the database
into two parts: a "front end", which contains all the forms, queries,
reports, and code; and a "back end", which contains all the tables with
data. The front end contains links to the back end's tables.

One copy of the back end is placed in a location where everyone can access
it. Everyone uses his/her own copy of the front end to access the data. That
way, all data changes happen in the same place.

Alternatively, if it's not possible to have the back end where everyone can
access it, then you might consider the replication method. I have not worked
with this feature, and there is a newsgroup devoted to it where you can get
help.

 

[John Thow]

When you say you need to have more than one copy of a database, are you
referring to the data? A normal setup of ACCESS is to split the database
into two parts: a "front end", which contains all the forms, queries,
reports, and code; and a "back end", which contains all the tables with
data. The front end contains links to the back end's tables.

One copy of the back end is placed in a location where everyone can access
it. Everyone uses his/her own copy of the front end to access the data. That
way, all data changes happen in the same place.

Alternatively, if it's not possible to have the back end where everyone can
access it, then you might consider the replication method. I have not worked
with this feature, and there is a newsgroup devoted to it where you can get
help.

--
Ken Snell
<MS ACCESS MVP>

the process.

[Snip]

Ken,

Thanks for the response. What was needed in this case was an extract of most
- but not all - of the data and around a third of the 'front end'. I do not
share a network (other than the internet) with my co-volunteer who required
this stuff to maintain our local business association's website. Hence,
separation of the 'front end' & 'back end' was not relevant to the problem.
Complete replication wasn't appropriate either: There's rather more in the DB
than our webmaster needs to do her bit and I wanted to keep her task as
straightforward as possible by removing anything that wasn't relevant to that
task. Doing that also simplifies the queries needed for her to get the
necessary data for the website.

Not having got an earlier response via this ng (or the access.macros one) I
took the problem to experts exchange and now have a solution.

Thanks again for looking at this.
--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!