Joining Access Replication ID to SQL Server GUID

[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)

 

[David W. Fenton]

(e-mail address removed) wrote in

We've got a large vendor supplied SQL Server database in which
almost all of the tables have GUIDs as primary keys.

Uh oh -- you've got major problems ahead:

Replication and GUIDs, the Good, the Bad, and the Ugly
http://trigeminal.com/usenet/usenet011.asp?1033

Basically, Access and VBA just don't deal well with GUIDs. I'm not
sure whether the conflict resolver works correctly with SQL Server
(the same conflict resolver is used in A2K forward as in SQL Server,
if I'm not mistaken), but if you're doing heterogenous replication,
you'd still have the conflict resolver problem on the Jet end.

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;

Convert the JOIN to a WHERE clause -- that often works when I'm
working with the Jet replication system tables, so I'd expect the
same here. It's probably a GUID/string conversion issue that's
causing the problem, but I've found that WHERE clauses somehow
coerce the values to the correct typtes (or don't have the problem
in the first place).

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)

See if converting JOINS to WHERE clauses works. The Jet query
optimizer treats them identically in terms of optimization. I don't
know what it would do with a heterogenous join, though.

 

[aaron.kempf]

dude.. it's reallly really really simple

keep everything in ADP format-- sql server-- and _FUCK_MDB_IN_THE_NOSE_

MDB is obsolete and it has been for 10 years

-Aaron

 

[robbie]

Hi David and Aaron,

Thank you for the insights. Aaron, I share your sentiments about most
Microsoft products. But, sometimes we have to work with what we are
given. David, that link to the Good Bad and Ugly of GUIDs was really
informative!

Aaron, by ADP format, do you mean not keeping any tables in the .mdb
file and keeping them all out on SQL Server? I've tried that, and it
works only sporadically. I've moved the Person_List table from the .mdb
to a database on the SQL Server. David, I've also tried replaceing the
JOIN clauses WHERE clauses. That doesn't appear to make a difference,
though in all my queries I try it both ways.

It's very weird. In some situations the queries on GUIDs work, but on
others they don't. I can use them to join two table in the vendor
supplied database. But I can't join the table I created (called
Person_List) in a separate database (on the same server) to any of the
vendor tables. I've populated the GUID field in Person_List by
inserting values into it from a table in the vendor supplied database.

What am I doing wrong?!

Thank you,
Robbie

 

[David W. Fenton]

(e-mail address removed) wrote in

What am I doing wrong?!

Heterogenous joins in a context in which Jet doesn't support them?

 

[Jonathan Roberts]

Aaron, by ADP format, do you mean not keeping any tables in the .mdb
file and keeping them all out on SQL Server? I've tried that, and it
works only sporadically. I've moved the Person_List table from the .mdb

What doesn't work about your use of ADPs? They worked great for me in
the past.

 

[aaron.kempf]

fabulous JR

keep up the good work; speak the good word.

these folk microsoft.public.access need more ADP preachers

-Aaron

 

[Bill Schanks]

fabulous JR

keep up the good work; speak the good word.

these folk microsoft.public.access need more ADP preachers

-Aaron

ADP are great, but in this scenario you wouldn't be able to save any
views to the ADP. Unless the vendor has given you access to do that (I
would guess not). I use a vendor supplied SQL Server and there is no
way we can change anything in the database.

Have you thought about using SQL Pass Thru queries, rather than linking
tables? Create your query in Enterprise Mgr/Query Analyser and then
copy that into a SQL PassThru on the MDB Side. You can even make them
DSN-Less so you don't have a DSN to potentially maintain on multiple
machines.

 

[robbie]

Here's what I'm doing:

This query in Transact SQL from Query Analyzer works:
select * from [tmi-misc].dbo.person_list PL, [ngprod].dbo.person
PE
where PL.person_id = PE.person_id

(note that one table is in one database, and the other is in another,
though both on the same server.)

The same query from within Access doesn't:
select * from person_list PL, person PE
where PL.person_id = PE.person_id

Person_ID is defined as UniqueIdentifier in both tables.

From within Access these two tables, person_list and person, are linked

to the SQL Server databases via .dsn files. (Thought: Is it possible
that there's something wrong with one or both .dsns?) I have no problem
in Access joining tables on number, character or other type fields.

Is this what you're calling a 'hetergenous join,' David?

Thanks again for the help. Any further insight is appreciated.

-Robbie

 

[aaron.kempf]

so create another database on the same server.. do all your dev in that
server; using the 4-part naming Server.Owner/Schema.Object in order to
get stuff out of the main database.

it's not rocket science; and it's a hell of a lot simpler than Pass
Thru _THIS_ and Linked Table Manager that

ADP are 10 times simpler

-Aaron

 

[David W. Fenton]

(e-mail address removed) wrote in

to the SQL Server databases via .dsn files. (Thought: Is it
possible that there's something wrong with one or both .dsns?) I
have no problem in Access joining tables on number, character or
other type fields.

Is this what you're calling a 'hetergenous join,' David?

Yes, because you're joining an Access table and a SQL Server table.

Can't you link an Access table into the SQL Server? Then you could
let SQL Server do the join, rather than Jet. I don't do SQL Server,
but I know there's the concept of "linked servers" there, and I
thought I recalled it being possible to link to Jet data.

 

[robbie]

Thank you all for the various suggestions. I followed as many as I
could and still had problems. Kept all my tables in SQL server and only
used Access for a front end.

After perusing our vendor's web site I found that THERE IS A BUG IN
ACCESS that prevents it from working correctly when you refer to a SQL
Server GUID field. Microsoft has a hotfix that fixes the bug.

Here's the hitch: It's not available to the public.(!!!!) We had to get
a special ID from our vendor, call a specific person at Microsoft
(caught her before she left for vacation!) and had her e-mail the
hotfix. So far as I can tell, Microsoft hasn't even announced publicly
that there is a bug, even though this fix has been available since
August or earlier.

To my mind this is a classic example of why we all need to get off the
Microsoft treadmill and go solely to Open Source. If the Open Source
community had been looking at this problem, they'd have found it pretty
fast, and probably have fixed it pretty fast too. Don't hold your
breath waiting for Microsoft...

Thanks again,
Robbie

 

[Tony Toews]

After perusing our vendor's web site I found that THERE IS A BUG IN
ACCESS that prevents it from working correctly when you refer to a SQL
Server GUID field. Microsoft has a hotfix that fixes the bug.

Here's the hitch: It's not available to the public.(!!!!) We had to get
a special ID from our vendor, call a specific person at Microsoft
(caught her before she left for vacation!) and had her e-mail the
hotfix. So far as I can tell, Microsoft hasn't even announced publicly
that there is a bug, even though this fix has been available since
August or earlier.

My experience with hot fixes has been quite different. Find the KB
article mentioning the problem, phone the 800 number and tell them
you're having the problem mentioned, they email you a link and you
download the patch. Takes only a few minutes.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[robbie]

My experience with hot fixes has been quite different. Find the KB
article mentioning the problem, phone the 800 number and tell them
you're having the problem mentioned, they email you a link and you
download the patch. Takes only a few minutes.

So far as I can tell, there was no KB article mentioning the problem.
In this case I had to call a toll phone number, and I was asked for an
ID that my vendor gave me. I wasn't given a link for downloading,
rather she sent it zipped via e-mail. The Microsoft rep wasn't going to
send me the hot fix without this ID number. And the zip file was
password protected. When I googled the the name of the hot fix
(WindowsXP-KB895751-v2-x86-ENU.exe) or any part of it, the search
didn't come up with anything related, as it usually does with MS hot
fixes.

And, of course, my initial assumption is that I'm doing something
wrong, not that there's a bug in the program.

This really isn't something that's been released to the public. If you
can find information on this problem to begin with, please send the
URL.

-Robbie

 

[Jonathan Roberts]

So far as I can tell, there was no KB article mentioning the problem.
In this case I had to call a toll phone number, and I was asked for an
ID that my vendor gave me. I wasn't given a link for downloading,
rather she sent it zipped via e-mail. The Microsoft rep wasn't going to
send me the hot fix without this ID number. And the zip file was
password protected. When I googled the the name of the hot fix
(WindowsXP-KB895751-v2-x86-ENU.exe) or any part of it, the search
didn't come up with anything related, as it usually does with MS hot
fixes.

And, of course, my initial assumption is that I'm doing something
wrong, not that there's a bug in the program.

This really isn't something that's been released to the public. If you
can find information on this problem to begin with, please send the
URL.

-Robbie

It appears to be under the referenced KB# in the patch's filename. Link
is http://ask.support.microsoft.com/kb/895751.

Jonathan

 

[David W. Fenton]

(e-mail address removed) wrote in

When I googled the the name of the hot fix
(WindowsXP-KB895751-v2-x86-ENU.exe) or any part of it, the search
didn't come up with anything related, as it usually does with MS
hot fixes.

What about:

http://support.microsoft.com/kb/895751

 

[Tony Toews]

In this case I had to call a toll phone number, and I was asked for an
ID that my vendor gave me. I wasn't given a link for downloading,
rather she sent it zipped via e-mail. The Microsoft rep wasn't going to
send me the hot fix without this ID number. And the zip file was
password protected. When I googled the the name of the hot fix
(WindowsXP-KB895751-v2-x86-ENU.exe) or any part of it, the search
didn't come up with anything related, as it usually does with MS hot
fixes.

Interesting. My zip file which I downloaded was also password
protected and good for only a few days.

And, of course, my initial assumption is that I'm doing something
wrong, not that there's a bug in the program.

Of course. <smile>

BTW thanks for mentioning this problem and to Jonathon and David for
finding the KB article. That has been forwarded to my fellow MVPs so
we all know about it.

I have been having intermittent problems which those hot fixes might
take care of. I'll have to come up with a repro situation though.
<smile>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm