Importing tables from 1 DB into 2nd DB - tables same - data differ

[LKH]

I have 2 DBs that are exactly the same except for table data. I do an Import
Data, select the table with the data I want, the import definitions and data
option is selected. The tables copy (or seem to) quickly. The problem: the
data has not changed.
What is causing is??

 

[Klatuu]

Reading your post, I wonder if you are using Access. The description of the
import process you provide does not sound like it is in Access.
If you are using Access, the Import does not append data to an existing
table. It creates a new table. Say you have a table named tblFoo, after you
import tblFoo from the other database, look in your database window at the
tables tab and you will see
tblFoo
tblFoo1
Access adds the 1 if the table name being imported already exists in the
current database.

If you are using Access, then a better approach would be to create an Append
query that would append data to your table from another database.

 

[LKH]

Am using Access and the tblFoo and tblFoo1 is what we found on a closer look
at the DB. Any way to get tblFoo1 to overlay tblFoo? There are quite a few
tables in this DB so I am hoping for a rather simple way to have them all
done at one time or if necessary one at a time.

 

[Klatuu]

Yes, reread my previous post. Use an append query to pull the data from one
to the other.

 

[LKH]

But an append query just adds the data to the existing table. I want to
overwrite the existing table.

 

[Klatuu]

Okay, then just delete the existing table first, then do the import. This
could be a problem if you have any relationships set up. If you do have
relationships, then before you create the append query, delete all the data
in your existing table:
Currentdb.Execute("DELETE * FROM MyTableName:")

 

[Noel]

Access 2003: I have the same type of situation but I am trying to import my
new tables, queries, and forms from a "copy database" that we use. I only
want the definition from the "copy db" but want to keep the data from the
current database. The users currently use one database and I just made a copy
of it to make all of the changes. How do I get the definition in the current
table?

Thanks

 

[Klatuu]

Is the database split?
If not, why not?

 

[Noel]

What do you mean by split?

Is the database split?
If not, why not?

 

[Noel]

No, my database is not split. I am the only one in my office that knows a
little about working with access. So I am learning as I go. Is there a way to
get the new definition with the current data I need?

 

[Klatuu]

The correct way to deploy an Access application is to use the database
splitter (Tools, Database Utilities, Database Splitter). It creates two mdb
files. One will retain the name of the mdb you started with. It will
contain all your forms, reports, queries, macros, and modules. It is
commonly referred to as the Front End (fe). The other file will have _be
appended to the file name and will contain all your tables, indexes and
relationships. It is commonly referred to as the Back End (be). It should
contain only data. Do not be tempted to put other things in it.

The be should be on a shared folder where all users have read, write, and
delete permissions.
Each user should have a copy of the fe on his own computer. It also should
be an mde file. It is not a good idea to deploy mdb files to the user.

The fe "links" to the tables in the be. A word about that. Since in many
cases not all users will have the same drive mapping. It is best to use UNC
path names when prompted for the location to put the back end file. A UNC
path name uses the sever name and folder path rather than a drive letter. It
will be something like \\ServerName\SomeFolder\SomSubFolder\MyApp_be.mdb.
That way, it will not matter how a user has their drives mapped.

As to making changes.
If you make any changes to the application (fe), all you need to do is to
deliver a new copy of the fe to the users.
If you make changes to the tables (be), it takes a bit more work. You have
to choose a time when you can have be be opened in exclusive mode so no users
can use it. You can then import any files you created in your test version
and select definition only when you import them. If you have made changes to
existing tables, you can open the table in design mode and make the changes.

Once you have made your changes, it is a good idea to do a compact and repair.

 

[Noel]

These db's were built by someone else and I can be quite clueless with the
db's.
The (fe) db is in a Shared folder that all the users can access. They may
only have a short cut on their desktops linking to the db. The db is a .mdb
file.
Okay, so I can still import the changes from the "copy database" to the
"current database" for forms, queries, reports. But with the tables, I need
to go the current database and make my changes there. Am I understanding that
correctly?

I appreciate all of your help in leading me in the right direction.

THANKS!!

 

[Klatuu]

That is correct. You need all users out of the database to be able to make
any changes.