linking two databases and changing the data type on one

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

Guest

Hey,

I've linked two databases together, the fields that i used for a
relationship while building a query are different data types (ones a text and
ones a number). So, how can i create a new table that basically does what
table 3 would do below, or am i best to build a query to do this and if so
how would i do that?

Table 1 (linked to db 1)
Table 2 (linked to db 2)
Table 3 i want linked to table 2 so it updates when table 2 is updated, but
i want to be able to change the data type in design view so i don't get a
type mismatch error.

Thanks,

Ben
 
Hi Ben,

to the best of my knowledge, you can't change the datatype of a linked
table, so your best route would be to import the table you want to alter and
base your query on that and the other linked table.
 
Ben,

If you are linking fields in a relationship or in a query the two fields
need to be the same data type or you will continue to get the error. I would
change them so they are either both text or both number. In the relationship
you can specify if the relationship is either one to one, or one to many,
although this is really defined by the way the keys are setup... so if both
of the fields are the PK in their respective tables you would create a
one-to-one relationship, where as if the field in tbl1 was the pk, and in the
seciond tbl it is not the ph you will establish a one to many relationship.
Better practice would be to use an autonumber for PK's in the one table, and
a number field in the second table... see example below:

Tbl1

pk: CustomerID
CustomerName
Phone
Etc

tbl2

pk: OrderID
CustomerID
OrderDate
Etc

In this case you would link CustomerId from Tbl1 to CustomerID in tbl2
creating a one-to-many relationship using key fields. Hope this helps!

Also, if the information in both DB's is the same you may want to
consolidate or link tables... no sense in having the same data everywhere.
 
Ben,

Susan is right... except you can change the data type... but first you must
remove the relationship... you can do this in the relationship manager...
after the fields are the same datatype use the manager to re-establish the
relationship.
 
Back
Top