import data ONLY from anpother Access table

  • Thread starter Thread starter Mark Kubicki
  • Start date Start date
M

Mark Kubicki

I've been working on a copy of the office master database, and have made
some significant changes (added and updated forms, added tables and queries,
relationships added....). ...); and am now ready to put the updated file
"on-line" for the office to use.

problem...
since everyone has been (and still is) using the "old" version, most of the
data in the copy I have been working with is outdated.


I would like to import the data (ONLY) from the currently being used table
into the copy that I've updated (many of the tables have had fields
added...) All of the original table names are still the same, and none of
the field names in those tables have changed (although I've often want to
!@#$%^)


I appears that I first need to strip the updated tables of all data, and
then run append queries for all of the tables in common with the earlier
(still being used) version (individually?)

just wanted to check... is this an appropriate course of action, or an I
totally off base?

thanks in advance,
mark
 
Well, you started off so far off base you are not even in the ball park, but
it can be rescued. Your users will have to be without the database for a
period of time until you fix all your problems.

First, you should never have mutliple users sharing one copy of a database.
The database should be split and each user should have a copy of the front
end database on their own computer. So here is what you need to do:

1. Make a backup copy of your development version
2. Run the database splitter wizard. (Tools, Database Utilities, Database
Splitter)
This will leave you with two mdb files. The file with the original name is
the front end. It will contain all your forms, reports, modules, macros, and
data acces pages. The other mdb will have _be added to to its name. So if
the original was MyApp.mdb, it will be MyApp_be.mdb. It will contain only
tables and relationships.

The be should be deployed to a network folder where all users have
read/write/delete permissions. It is necessary because the ldb file is
created deleted, and updated as users open and close the applicaiton. You use
the Linked Table Manager to point to the tables in the be. This is a key
point. Not all users will have the folder on the network mapped to the save
drive letter. The correct way to manage this is to use UNC paths rather than
drive letter mapping in the Linke Table manager. This way, the users will
link to the be correctly regardless of their drive letter mapping. So,
instead of g:\SomeFolder\SubFolder\MyApp_be.mdb, you should use
\\ServerName\SomeFolder\SubFolder\MyApp_be.mdb

Each user should have their own copy of the fe on their computer.

So, to continue.

3. Once you have the database split, import the data in the existing version
into your new be database.
4. Install the new fe version on each users computer.

In the future, this will make doing changes to the database design much
easier. You make the changes to the fe, then deliver a new version to each
user. I suggest you always deliver an mde fe rather than the mdb.
 
Hi Mark

Have you split your database into backend-end (data tables only) and
front-end (everything else)?

If not, then you really should do this, especially as it sounds like your
database is multi-user.
(Check here for an explanation:
http://www.granite.ab.ca/access/splitapp/overview.htm)

Having split your database, you can continue with the original plan to empty
all the tables and write append queries to repopulate them with the
up-to-date data from the old database.

OR.....

It might be easier just to delete everything except the tables from a copy
of the old database, then add your new fields to the tables and use that as
your back-end.
 
Back
Top