Sending data from Table A in Database A to Table B in Database B

[breezett93]

I have a user that uses two databases. To save time, the user would like to have data sent from Table A in Database A to Table B in Database B automatically via button on a form. I've never done something like this before because it's usually more efficient to keep the work in the same database.

However, I need to provide an answer if this short-term solution is do-able.

Do the two tables in the two databases have to have identical columns? That seems like a pass/fail questions for this.


Thanks.

 

[Andrzejek]

Do the two tables in the two databases have to have identical columns?"
No, but... You cannot cram 120 characters from a field in one table into 60 characters field in the other. The data type and size of the fields should be the same, but they don't have to be in the same order. You also can have different number of fields in both tables, but you cannot fit 20 fields into 15 field table.

Your user may just have a need to 'see' the data from other data base, no need to 'copy' the data. You can use link tables. Or just simple query from other data base to see the data.

I guess to give you more precise answer, you need to provide more information about your requirements.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[MajP]

I have a user that uses two databases

.
Since the user has access to both databases there is almost no scenario where copying the data makes any sense or should be considered. This should be a linked table, or a query. In fact if these databases are shared on a network then no tables should be in the front end database. All tables should be in separate databases from the forms, reports, queries, and code. Running shared databases across a network without splitting it with the front end on the user desktop is one of the fastest ways to corrupt a database.

 

[breezett93]

Based on the responses, it looks like a linked table is the way to go.

Is it better to initiate the link from the database where the data is being entered or from the database that is viewing the data.

 

[MajP]

Can only be done from the viewing database. FYI you can link to other things like excel worksheets, csv files. The link table can only be designed from the parent DB, but you can work in it just like any other table.

 

[breezett93]

The table that I want to link to does not show up after I specify the source database. Picture attached below.

Is there a property of the table that I need to modify?

 

[MajP]

No, that is strange. Can you do a screen capture of the original db showing the tables? That looks like you are pointing to a blank db.

 

[breezett93]

Absolutely.

TR Number Table is the one that I'd like to link to receive the data from the other database.

 

[MajP]

That was what I thought. The tables in the source database are all linked tables. See the blue arrows. So the source database may actually already be set up as a split database which is good. You need to point to where the tables are actually located. Right click on any of the tables and select "linked table manager". You will be able to see the path to the actual tables.
Or you can import the linked table from this location. That is why it does not show up. You cannot link to a linked table, but you can import the linked table which basically just copies the link.