Linking to exteranal table 2 questions.

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

Guest

I've got a DB that our users are using all day, on some occasions they have
to update another DB, and add records to it. But its the same stuff that
they're entering in the other DB. So its been surgested that the main one be
linked it update thee other one. Great idea, but I'm not sure how. I didnt
design the other one, so its not been degsigned with this in mind or
anything. But its got SOME of the same fields and the same primary key. So
can it be done, and how??

Whilst I'm here I've got a seperate Q. I had to import like 250 records into
a table from another DB, both tables had the exact same records. First I
tried copy and pasting, but it didnt work. Is that at all possible??? After
trying everything I could think, I got it in the same DB and did it as an
apend query. That sorta worked but I all the fields that were lookups didnt
work so I had to do them manualy. So whats the best way to do it?

Thanks guys!
 
If you link the other table into the database you are using, you can use an
append query or an update query depending on what is being done. If it is
just new data all the time and the records do not exist, then you can create
an append query to add the new data matching the fields of one table to the
fields of the other table with the exception of the primary key if it is
AutoNumber. It will not fill in any other fields that exist in the other
table, so it still may require the user to go back in and enter that data.

If the data exist in the other table and the user is updating info and you
need to apply the data to update the other data, you can do an update query.
The problem will arise if their is no match for match on the primary key. In
other words the primary key and data does not match the primary key and data
in the other table.

Having said all of the above, I can think of no reason to have two or more
databases that contain the same information. This is poor database design.

Unless the tables match fieldname for fieldname, you can't do a copy and
paste.

As for the Append query, you need to explain what you mean by "all the
fields that were lookups didn't
 
If you link the other table into the database you are using, you can use an
append query or an update query depending on what is being done. If it is
just new data all the time and the records do not exist, then you can create
an append query to add the new data matching the fields of one table to the
fields of the other table with the exception of the primary key if it is
AutoNumber. It will not fill in any other fields that exist in the other
table, so it still may require the user to go back in and enter that data.
Click to expand...

An append query would work for the data that already exists, but is there a
way to update between the 2 databases live. As it stands users are entering
data in our database and then having to come out of that and enter the same
stuff in another one. Most of the time they are forgetting. I dont blame them
either.
Having said all of the above, I can think of no reason to have two or more
databases that contain the same information. This is poor database design.
Click to expand...

Yes, I know. I've kinda inherited this DB and I've got nothing to do with
the other one. Basicly, our one gets used by department and cant be used by
any other departments, for security reasons. The other databases gets used by
3 other departments.
Unless the tables match fieldname for fieldname, you can't do a copy and
paste.
Click to expand...

They did match.
As for the Append query, you need to explain what you mean by "all the
fields that were lookups didn't
Click to expand...

When I ran the append query, it added the records, but all the fields which
had drop-down lists were blank, even though the stuff I was adding was on the
drop-down. Its not so important now anyway.
 
I dont think I was very clear with what I needed to do. Basicly, is there a
way to add data into a field so that it updates in two tables in seperate
dbs. I know I can do this with an append query but how can I do it so its
happening live? Is it possible???
 
Back
Top