duplicating database on 2nd dataset

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi!

I've created a relatively complex desktop application in Access and now need
to create a 2nd identical application on a new dataset of virtually the exact
same structure and format as the first.

(i.e. same reports, forms, queries - different tables)

I've naturally given the process some thought, but I'd like some
confirmation and/or fresh ideas from the pros here on some possible best
practices and red flags :)

Always thankful,

Marika :)
 
Marika

Are you saying that you have "different" tables (i.e., different structure)
or "different data" (but the same structure)?

--
More info, please ...

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
Sorry - different data, same structure (just a few fewer tables - but the
ones that exists are structurally identical)

Thanks Jeff! Looking forward to hearing your thoughts :)

M
 
Maria

Sometimes when folks create a "same structure" copy, they're trying to
handle records they don't want to see in their day-to-day operations (I.a.,
they're "archiving"). It rarely turns out to be necessary to physically
remove/move records to accomplish this.

Other times, folks are making a copy to provide another user a way to use
the system. If the same system/data is needed by more than one user (on a
network), this can be handled with a front-end/back-end design.

I, for one, have sold licenses for systems I've created to multiple sites,
not connected with each other (I.a., separate customers).

But in all three of these examples, the structure remains the same. Could
you describe a bit more what you are hoping to accomplish by doing what you
posted? The recommendations will differ...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
Dear Jeff,

The desktop application I've created (consisting of roughly a dozen reports
and a dozen forms) runs off tables of a certain set of companies. I'd like
to create an exact replica database using tables of the same structure but
with data on different companies. As such, I'd like to preserve the
functionality and structure of the existing database (via the same forms,
reports and queries), but insert new underlying tables - where the data is of
the exact same structure, but based on a new set of companies. Thus we would
have two identically functioning applications, but to access to different
sets of data.

Presently, I presume I'm going to make a copy of the existing database,
import the new company tables and relink them as the new data sources for all
the reports and forms and then, once there are no remaining dependencies on
the old tables, delete them.

Instinct is telling me that there may be an easier, less error-prone method
here, though. . . Your thoughts would be greatly appreciated !!

Marika :)
 
Marika

Is there a business need to segregate the information about your second
"batch" of companies from the information about your "original" batch of
companies? Have you already considered importing the second batch of
information into your existing database?

I'm not sure I understood a remark in your original post, about leaving out
a few tables. Why would your "second" database not need the same tables as
the first?

Sorry if I'm taking a long time to get to my ideas, but I'd rather not
recommend actions without understanding the context (and need).

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
Dear Jeff,

Forgive me for such incomplete information ! !

I've created a desktop database application on a set of tables with data on
thousands of american companies. My client has also given me similar data on
a slightly smaller set of European companies (which must be a completely
separate application for reasons important to them). For simplicity, let's
say that the underlying data tables are structurally identical - in reality,
a few of the specific tables for the european dataset don't exist, but
naturally I then simply won't use the related forms and reports.

I hope that adds necessary clarity!!

It seems to me, some of the options are : 1) append-querying the european
data to the existing tables, and then deleting the american data. 2) making
a copy of the existing application, cut-and-pasting in the new european
underlying tables and re-linking all the queries/forms/reports to the new
tables and then deleting the old tables; 3) starting a fresh database with
the european tables and then copying in the queries/forms/reports and
one-by-one linking them to the tables.

I'm hoping you might have some guidance as to, perhaps, another method - or
some thoughts on which of the methods above is likely fastest and most
error-proof!

Many, many, many thanks for your help,

Marika :)
 
PMFJI :-)

One approach would be to split your database into a standard "front
end/back end" (FE/BE) structure.

In an FE/BE structure, the FE contains all of the queries, forms,
reports, macros & modules, but none of the tables. Converselt, the BE
contains all of the tables, but none of the queries, forms, reports,
macros or modules. Code within the FE creates links to the actual
tables within the BE.

The normal advantage of this structure, is that you can provide a new
FE (containing new or modified programs), without clobbering the user's
actual data, which is stored in the BE (not the FE). But in your case,
you could have /several/ BEs - one for the American data, another for
the European, another for the Elbonian, and so on. Then the FE could
have some mechanism for letting the user choose which BE they wanted to
link to. Or you could make the FE link /automatically/ to whatever BE
was in the same folder.

This is not necessarily the ideal way to do it "in theory". But it
would involve /no changes/ to your existing code. You'd just add /new/
code to identify & relink to, the appropriate BE database.
 
Marika

The things on my list, were this my project, would be making sure the
"child" table records (if any) were properly linked back to their parent
records.

If I had to "load" the Access database from non-Access sources (e.g.,
Excel), I'd probably start with an "empty" database, load the parent table
records first, then their child table records.

And I'm with TC -- if you haven't done so already, split your db into
Front-End and Back-End.

Good luck on your project!

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
Back
Top