R
robbie
Hello,
We've got a large vendor supplied SQL Server database in which almost
all of the tables have GUIDs as primary keys. We are attempting to do
some processing using our own in house procedures written in Access. In
Access we link to tables in SQL Server via ODBC using .dsn files.
The problem is that we can't reliably join a table with an Access
Replication ID to a table with a SQL Server GUID. Here is a sample of
our data structures:
SQL Server
Person.Person_ID data type = UniqueIdentifier
Person.Last_Name data type = char(50)
Patient_Encounter.Person_ID data type = UniqueIdentifier
Patient_Encounter.Enc_Date data type = DateTime
There is a many-to-one relationship between Person and
Patient_Encounter
Access Table:
Person_List.Person_ID data type = Number/Replication ID
Person_List.Order data type = Number/Integer
In Access if I create a query that joins Person_List with
Patient_Encounter, zero records are returned:
SELECT Person_List.Person_ID, patient_encounter.person_id,
patient_encounter.Enc_Date
FROM Person_List INNER JOIN patient_encounter
ON Person_List.Person_ID=patient_encounter.person_id;
However, if I create a form in Access where the data source is
Person_List, and create a sub-form based on Patient_Encounters, the
main form DOES display the correct related records in the sub-form. So
I know that the data types are (probably?) correct and that a
relationship is possible.
If I join the SQL Server tables Person and Patient_Encounter, there is
no problem:
SELECT *
FROM person INNER JOIN patient_encounter
ON person.person_id = patient_encounter.person_id
The problem only appears to be joining Access to SQL Server. Any help
in how to reliably refer to SQL Server GUIDs from Access would be
greatly helpful.
We are running Access 2003 v.11.5614.5606 and
SQL Server Enterprise Edition v.8.00.760(SP3)
We've got a large vendor supplied SQL Server database in which almost
all of the tables have GUIDs as primary keys. We are attempting to do
some processing using our own in house procedures written in Access. In
Access we link to tables in SQL Server via ODBC using .dsn files.
The problem is that we can't reliably join a table with an Access
Replication ID to a table with a SQL Server GUID. Here is a sample of
our data structures:
SQL Server
Person.Person_ID data type = UniqueIdentifier
Person.Last_Name data type = char(50)
Patient_Encounter.Person_ID data type = UniqueIdentifier
Patient_Encounter.Enc_Date data type = DateTime
There is a many-to-one relationship between Person and
Patient_Encounter
Access Table:
Person_List.Person_ID data type = Number/Replication ID
Person_List.Order data type = Number/Integer
In Access if I create a query that joins Person_List with
Patient_Encounter, zero records are returned:
SELECT Person_List.Person_ID, patient_encounter.person_id,
patient_encounter.Enc_Date
FROM Person_List INNER JOIN patient_encounter
ON Person_List.Person_ID=patient_encounter.person_id;
However, if I create a form in Access where the data source is
Person_List, and create a sub-form based on Patient_Encounters, the
main form DOES display the correct related records in the sub-form. So
I know that the data types are (probably?) correct and that a
relationship is possible.
If I join the SQL Server tables Person and Patient_Encounter, there is
no problem:
SELECT *
FROM person INNER JOIN patient_encounter
ON person.person_id = patient_encounter.person_id
The problem only appears to be joining Access to SQL Server. Any help
in how to reliably refer to SQL Server GUIDs from Access would be
greatly helpful.
We are running Access 2003 v.11.5614.5606 and
SQL Server Enterprise Edition v.8.00.760(SP3)